We use cookies to provide the best site experience.

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

Most marketers work with the sales funnel in a CRM system. However, these built-in dashboards and analytics are not appropriate for every organisation. Some find them uninformative and fail to answer many critical questions. How is the sales strategy being carried out? Which managers excel at which stages of the funnel? How much more effectively does one product sell than another? The CRM funnel cannot always provide insights into these and other issues.
Пример воронки продаж в amoCRM
Example of sales funnel in amoCRM
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.

How to build a sales funnel in Excel 2019

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.

How to make a funnel
in Excel 2016, 2013, 2010

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.
How to calculate padding for a funnel in Excel?

In each row of the new column, enter the formula: =($C$19-C20)/2, where C is the column indicating the number of clients at each stage of the funnel, and 19 and 20 are the row numbers with the relevant values. The formula calculates the difference between the maximum number of clients and the value in the active cell, then divides the result by two.
Формула для вычисления подушки
Formula for calculating the cushion
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.
Так будет выглядеть будущая воронка в старых версиях Excel
This is how the future funnel will look like in older versions of Excel
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.

Funnel in the summary table

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.
Excel does not build a funnel based on a summary table
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.

Why do you need BI platforms then?

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.
Воронка в Power BI позволяет вывести конверсию
The funnel in Power BI allows you to take out conversions
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.
Так может выглядеть ваш отчет по воронке продаж в Power BI
This is what your sales funnel report in Power BI might look like
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.
Professional mastery of Power BI
Interactive reports in 3 hours
Quick and easy data analysis

Beautiful and clear analytical reports in 1 week

Power BI for Business Users
Did you like the article?
Read also
Sign up for our newsletter and receive a gift "How to choose charts", bestseller by Alex Kolokolov!

Want to receive up-to-date articles about data visualization?