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.
2 Responses to “Weighted Sorting in Excel ”
Just add a column calculating the "performance" or whatever is your criteria and sort by it? No?
have no patience to waste 13min. Save your time too.
Just thought I would mention, the "weird" custom sort behavior mentioned at 5:45 where "% return" doesn't appear to be sorting is because the "August Purchases" field has the sort preference and since these are such unique values, no additional sorting is possible on the "% return" field. If there were two entries that had the same "Customer Since" year AND the same "August Purchases" amount, THEN you would see a sorting of the "% return" on these two entries.