You probably have seen this type of Funnel Chart in Business Intelligence (BI) software packages and wondered how-to make this type of chart in Excel. In this post, I will show you how to make a Funnel Chart Template in Excel to show a graphical view of a Sales Pipeline for use in an Executive Dashboard.
Problems with Funnel Charts
Now before I show you how to make a Sales Funnel Chart in Excel, I feel that we need to discuss issues with this type of chart. Many of the data visualization experts in the industry dislike, sorry, I should have said HATE funnel charts.
Why is that you may ask?
Well specifically, data visualization experts do not like a Funnel Graph or Funnel Chart because the chart may have the potential to mislead readers. The area of each section may not represent the data proportionally to other sections. For instance, if your values for each section/category are the same, then the size of the top section is not proportionally equal to the bottom section even though they are the same value. In this graphic, it can be deceiving to readers that the Sales in Prospecting phase are much larger than those in the Negotiation phase, when in reality, they are exactly equal. The graphic is basing the height on the category value, not the width or volume of the section.
So be careful when using this as a representation of your data.
That being said, many, many organizations, companies, departments and divisions use the graphic as a standard to convey a pipeline or funnel affect for the data. That being that it is started at the top category and it moves to the bottom category, but not all makes it to the bottom. Even though this may be a bad graphic from all design and data visualization perspectives, you may be asked to create this type of graphic for your Executive Dashboard. Your first step should be to talk with the powers that be about the issues with this type of chart. If your client or executives or managers still want this as pare of the Company Dashboard, then give the people what they want. That is why you will see this type of chart in many well-known software packages like Salesforce.com.
Now lets say you are the Dashboard Designer for your sales organization and this is your data in Excel:
Lets say that your sample data has categories from your sales organization on how the sales pipeline looks for the company. Presently there are 652 sales opportunities in the Prospecting stage worth $33.6M. This is the start of the sales cycle for your company and each sales opportunity may be at a different level in the sales cycle. Typically there are less and less at each progressive stage of the sales cycle because deals are not always a done deal. Some people in the prospecting stage may change their mind and not purchase anything. Another potential sale that is in the Presentation stage may not get closed because the buyer chose another company to handle the business. However, not all sales fall out of the pipeline and your organization may be able to calculate the likely percentage that any given opportunity may close from each stage of the sales cycle. This is why organizations like to see this data in a pipeline or funnel format because each stage fills the next stage in the cycle. And if you don’t have enough sales in each part of the pipeline, your company will have uneven cash flows as each sale will most likely have an average closing time from start to finish.
I don’t see a Sales Funnel or Sales Pipeline Chart or Graph in Excel
Okay, that is enough about why organizations and people love Funnel Charts and the dangers about what they are showing you. Lets get to the nuts and bolts of how to make a Sales Funnel chart in Excel. Using the data points above for our pipeline, lets build the chart. However, I don’t see a sales funnel chart or pipeline chart as an option in Excel. That is the good and bad about Excel. Not everything is there and built for you, but with the standard tools, you can make a funnel or pipeline chart on your own.
So looking at the standard set of charts that are available in Excel, the closest representation of what we are looking for is a Stacked Pyramid chart. More specifically, a 100% Stacked Pyramid chart since our data represents 100% of the sales pipeline data and we are not trying to compare the data from one pyramid to another. You will find the 100% Stacked Pyramid chart under the Column Chart menu in the Insert Ribbon. This is what we are going to base or Sales Funnel or Sales Pipeline and here is how.
The Breakdown
1) Create a 100% Stacked Pyramid Chart
2) Flatten it to 2D
3) Flip it upside down
4) Label and Clean it up
Step-by-Step Tutorial on How-To make a Sales Funnel or Sales Pipeline Chart or Graph in Excel
1) Highlight the Data from A2:B6 (From “Prospecting” to Negotiation Value of “45”) and from the Insert Ribbon, choose the Column Menu from the Charts Group and then select 100% Stacked Pyramid.
The resulting chart will look like this:
2) In the chart, select the data series for Series one by clicking on the pyramids. Then from the “Design Ribbon”, select “Switch Row/Column” from the Data Group.
The resulting chart will look like this:
The funnel chart is starting to take shape.
3) Right click on the chart and select “3D Rotation…” from the menu or you can also see this as a choice from the Layout Ribbon in the Background Group.
4) Change the Rotation Settings as follows:
X=0
Y=0
Now we are getting really close to the Final Sales Funnel Chart in Excel.
5) Right click on the chart’s Vertical Axis and select “Format Axis…” from the menu or you can also see this as the Primary Vertical Axis choice from the Layout Ribbon in the Axis Group.
and choose “Values in reverse order” from the Axis Options Dialog Box.
Looking like we want, now we just need to clean up the Chart and give it some Data Labels.
6) Clean up:
a) Remove the Vertical Axis – Click on the Vertical Axis and press your delete key.
b) Remove the Legend – Click on the Legend and press your delete key.
c) Remove the Horizontal Gridlines – Click on the horizontal Gridlines and press your delete key.
d) Remove the Category Label – Click on the #1 at the top of the Sales Funnel and press your delete key.
e) Add Data Labels – Click anywhere in the chart and change Data Labels to “Show” from the Layout Ribbon in the Labels Group.
7) Admire your handy work:
Your Sales Funnel for Excel should now be completed and should look like this:
Sample File
You can get a FREE sample download template for the sales pipeline with instructions here:
Video Tutorial
Please remember to sign up for my RSS Feed and also leave me a comment on the other types of charts you would like to see.
Steve=True
Very nice guide. I use it to see a funnel conversion in our company.
Thanks. Glad it was helpful.
Why didn’t you also use the dollar amount when you selected the information for your sales funnel? I would like to make one that includes the dollar amounts, but every time I do it makes two separate funnels.
Hi Jason,
Yes, you will get to separate charts when selecting the dollar amount as well. I am confused however on what your final chart will look like if you choose both Units and Dollars in the same sales pipeline in Excel. How tall should each section of the pipeline be? Would it be the height of the Units or the $’s? If you chose units, would you like to see how to add a dollar amount to the salespipeline labels?
Excellent example for the funnel chart. Easy to follow and generates the exact model I was seeking.
Found this on youtube today looking for how to make a funnel chart in excel. I like your site. This is great. Thanks!
Thanks
I am using this in an exc PPT to convey a pipeline or funnel affect for the data. What type of report would you use for the dashboard? I was think of using a waterfall love to get other ideas thoug.
A standard column chart may fit the bill.
Great example. Easy to replicate.
Thanks.
Hi Carlos,
Thank you so much for the awesome comment. So happy to help.
Steve=True
Excellent! It is just perfect (exactly what I required) and great template. Thanks
Really appreciated this guide, thank you very much
Best Regards
David
David, Thanks so much for the awesome comment!!
Thanks David,
I am using this…. However, I would like to make one that shows different business unit/sales stage in one chart. Also I would like to add value. Do you know how this can be done?
Regards,
AE
Hi Anthony,
That is an interesting question. I have a solution, but will have to post that video and step-by-step. Come back soon for that solution! Thanks for the great idea, but who is David? 🙂
Steve=True
really love the work thank you so much. gotta job interview and I’m pretty sure they will love the fact that I know how to do the sales chart I love it this will be asset to my skill set and ill pass it down to my fellow. oworksrs thanx again
Thanks Stoney,
What a great comment. Glad it will help you and good luck in the interview. Also, don’t forget to share my site so they can learn other techniques as well. 🙂
Steve=True
Thank You for posting this. I am in job search mode, and beefing up my skills and really like this format. I also liked your caveat on using this.
So glad it was helpful for you!
Great guide – is there a way to be abke to partition the sections so divide Prospects into North / South / East / West and so on for each of the different parts of the sales cycle
Phil
Hi Phil, the best way I think would be to create several Prospects series. Prospects – North, Prospects – South, Prospects – East, Prospects – West. Then change the colors to match the normal prospect color so they look similar. It may get kind of busy but would do the trick. Hope this helps. Steve=True
Is there an easy way to copy the chart to use with different data sets? I wanted to make about five funnel charts on one page and each chart has its own set of data to build from.
Thank you for your help with these!
Hi Matthew,
Yes, you can copy it and then change the references for each one of the series from the Chart Design Ribbon > Select Data button. I will create a tutorial on this in the near future.
Also, this post may help you as well:
how-to-copy-charts-and-change-references-to-new-worksheet
Steve=True
Can this also be made somehow so you can se conversion-rate in percent between each step?
Hi Cooper, yes, that can be done. If your numbers are large and your rate is a percentage, I would add the values as additional data series in between. Alternately, you can just change your labels to include those values.
Nice……….
I Think you are a Chart Man………..
Thanks Anand. But I am just a student 🙂 I appreciate the nice comment.