How to Create a Sales Funnel Chart in Excel

I am currently working on creating a sales pipeline management dashboard. I have all the data in place and now I am looking for some visualization to create the dashboard. One of the charts that I absolutely want in there is a sales funnel chart.

What is a Sales Funnel?

In any sales process, there are stages. A typical sales stage could look something as shown below:

Opportunity Identified –> Validated –> Qualified –> Proposal –> Win/Loss

If you think about the numbers, you would realize that this forms a sales funnel.

Many opportunities are ‘identified’, but only a part of it is in the ‘Validated’ category, and even lesser ends up as a potential lead.

In the end, there is only a handful of deals that are either won or lost.

If you try and visualize it, it would look something as shown below:Sales Funnel Chart in Excel template- Example Picture

Now let’s recreate this in Sales Funnel Chart Template in Excel.

Download the Sales Funnel Chart Template to follow along

Watch Video – Creating a Sales Funnel Chart in Excel

Sales Funnel Chart Template in Excel

Lets first have a look at the data:

Sales Funnel Chart in Excel template - Dataset

Here are the steps to create the sales funnel chart in Excel:

  1. Arrange the data. I use the same dataset, as shown above, but have inserted an additional column between sales stage and deal value columns.Sales Funnel Chart in Excel template - Dataset with dummy data
  2. In the dummy data column, enter 0 in B2, and use the following formula in the remaining cells (B3:B6)
    =(LARGE($C$2:$C$6,1)-C3)/2

Sales Funnel Chart in Excel template- Dataset with dummy data formula

  1. Select the data (A2:C6), and go to Insert –> Charts –> Insert Bar Chart –> Stacked Bar. Sales Funnel Chart in Excel template - Insert Chart
  2. The above steps creates a stacked bar chart as shown below:Sales Funnel Chart in Excel template - Inserted chart
  3. Select the vertical axis and press Control +1 (or right click and select Format Axis). In the Format Axis pane, within Axis Options, select categories in reverse order. This would reverse the order of the chart so that you have the ‘Identified’ stage bar at the top.Sales Funnel Chart in Excel template - categories in reverse order
  4. Select the blue bar (which is of dummy data) and make its color transparentSales Funnel Chart in Excel template - dummy data bars hidden
  5. Select the red bars, and press Control +1 (or right click and select Format Axis). In the Format Data series pane, select Series Options and change Gap Width to 0%. This would remove the gap between these barsSales Funnel Chart in Excel template - No gap between bars
  6. That’s it. Now remove the chart junk and format your chart to look awesome. Here are some steps I took to format it:
    • Removed the legend and horizontal axis
    • Changed Chart Title to ‘Sales Funnel’
    • Changed bar colors to blue
    • Added a border to the bars
    • Added data labels
    • Removed gridlinesSales Funnel Chart in Excel template - Final

Download the Sales Funnel Template
Download File Pic

That’s all right Sumit. But where is the Dashboard?

I am working on it, and soon as I am done with it, I will share it with you all. For now, here is a glimpse of how I plan to use this sales funnel chart in the sales pipeline management dashboard.

Sales Funnel Chart in Excel template - Dashboard Glimpse

I am really excited as it is the first time I will share a full-fledged dashboard on my blog. If you would like to see more of Excel dashboard tutorials on this blog, give me a shout-out in the comments section.

Also, if you think there are any visualizations that would look cool in the sales pipeline dashboard, just drop me a note or leave your thoughts in the comments section.

You May Also Like the Following Excel Tutorials:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

16 thoughts on “How to Create a Sales Funnel Chart in Excel”

Leave a Comment