Sales Funnel Charts in Excel – Tutorial

Sales funnel is a very common business chart. Here is a simple bar chart based trick you can use to generate a good funnel chart to be included in that project report.

Download the Sales Funnel Chart – Excel Template and learn it by playing with it.

1. Get your sales data ready

The first step is to get your sales data ready for a funnel chart.

For this we take a normal phase-wise sales data and add a dummy series to it. The dummy values tell excel how far each of the sales figures should be moved away from y-axis to get the funnel effect.

The dummy values are calculated using a simple formula like =(some_large_number – funnel_size_at_that_phase)/2, In our case, I have used 150,000 for the large number.

We will also add a total column.

Tell me again, why are we using dummy series?

In order to create the funnel effect, we must move each of the bars in such way that they are all centered vertically. To do this, we take a large number and subtract funnel value from that and divide this by two. How it works ? well, that you can figure out while sipping coffee 😀

2. Create a simple bar chart with the columns Total and Dummy

Once you are done, the bar chart should look like this.

3. Now adjust bar overlap and gap width settings to create a funnel effect

Select the bars by clicking on them, and right click and go to format data-series. In the resulting dialog, go to options and adjust “series overlap” and “gap width” settings in such way that series are completely overlapped and gap is ZERO.

Once you do this, the chart should look like this.

4. Adjust colors and add labels

Select the dummy series and fill it with white color or make it transparent. Add data labels and you have a sparkling funnel chart ready.

Next time use this in your sales presentation to the boss and see everyone having a discussion.

Download the Sales Funnel Chart – Excel Template and learn it by playing with it.