Where and how to build a sales funnel? Comparing Excel, CRM and Power BI

Sales department managers frequently have to manually export data from the CRM into an Excel spreadsheet in order to see the sales funnel afterwards. This does not always work out well, though, because some Excel features and strategies are not evident.

We'll show you how to do it right the first time, with the least amount of effort and time. I'll demonstrate the visualisation method with a five-stage funnel as an example. The number of clients reduces at each level, as is normal.

In Excel 2019, creating a funnel chart is very easy, so I won't go into much detail about inserting the chart as it's illustrated in the screenshots below. The instructions are simple: select the table with your data → "Insert" → "Recommended Charts" → "Funnel."

You'll only need to adjust and customize the appearance to your liking. To do this, click on the chart and go to the "Chart Tools" tab in the menu. From there, you can adjust the chart color, background color, and other formatting elements.

In older versions of Excel, the process is a bit more complex but still achievable using stacked bar charts. Essentially, a "Funnel" chart is a stacked bar chart with the bars centered. Achieving this alignment in earlier versions of Excel might not be a single-click task, but with a little effort, you can accomplish it.

Firstly, you need to add another column to your data table called "Padding." The values in this column will help transform the stacked bar chart into a funnel shape. The padding values for each row in the table need to be calculated beforehand.

Select the 3 columns of the resulting data and insert a stacked bar chart from the menu. Don't expect Excel to automatically generate a perfect funnel at this stage – this is just the beginning! In this chart, the padding will act as auxiliary series that will adjust the necessary bars to shape the chart into a funnel.

Next, manipulate the chart by transforming it into a funnel and changing its look.

• Change the series sorting (use "Reverse Order" function).
• Rearrange the series order so that the padding appears first on the left side of the chart.
• Make the padding colorless, meaning remove any suggested fill.
• Reduce the side gap to eliminate spacing between funnel stages.
• Remove unnecessary labels at the bottom of the chart and the legend.
• Enable data labels on the chart to show the number of clients at each stage. If needed, adjust their color and size.

That's it – the resulting funnel visually and functionally won't differ from the one created in the newer version of Excel.

When dealing with simple small datasets, inserting a chart is easy. However, when visualizing data or preparing a presentation, you often have to work with pivot tables. And here is where it gets more complex.

With the correct understanding, you may not only develop a funnel based on a pivot table, but also a whole interactive dashboard. In other words, you may make your information panel respond to actions: for example, clicking on a certain manager's name filters the data on the dashboard to show the metrics for that manager.

Indeed, in Excel, you can create a quite attractive interactive dashboard. However, a BI platform offers more capabilities for this purpose and also eliminates the additional steps that Excel requires. Using the example of a sales funnel, I will demonstrate how a dashboard with it looks and functions in Power BI.

Firstly, data can be easily imported into Power BI from various online sources, including CRM systems. This alone can be considered a significant advantage of the platform over Excel, where data needs to be manually exported.

Secondly, the funnel in Excel only shows the number of deals at each stage. Meanwhile, the funnel in Power BI can also calculate the conversion rate at each stage.

Furthermore, to make a dashboard capable of filtering data with a button click, Excel requires creating an additional chart and adding slicer buttons. Power BI, on the other hand, handles this process automatically.

Absolutely, and these advantages of Power BI over Excel are specific to sales funnels. Considering that the BI platform is designed specifically for data visualization and analytics, it outperforms more "traditional" tools not only in this aspect but in many others as well.

Despite all of this, managing and even developing dashboards in Power BI doesn't require being a programmer or a designer. An average manager who has acquired the necessary skills can handle it. I teach these skills in my online course "Dashboard in Power BI from Scratch to Pro." Take a look, there are reviews from my students who previously thought that BI platforms were something daunting.