How to Create a Funnel Chart in Excel? 2 Useful Ways
(Note: This guide on how to create a funnel chart in Excel is suitable for all Excel versions including Office 365)
Representing complex data in Excel is easy as a breeze. With every iteration, Microsoft Excel implements a lot of updates and upgrades to help the user efficiently portray the data.
One such upgrade is the Sales Funnel chart which was introduced in Excel 2016 versions and so on. This has proven to be a very effective way to display data for a variety of multistage processes.
In this article, you will learn how to easily create a Funnel chart in Excel using 2 ways.
You’ll Learn:
Related Reads:
How to Create a Step Chart in Excel?
How to Make a Pie Chart in Excel? A Step by Step Guide
How to Create an Excel Map Chart from Pivot Table Data? 3 Simple Steps
Watch our video on how to create a funnel chart in Excel in 2 easy ways.
What Is a Funnel Chart and Why Use Them?
Funnel Chart in Excel helps portray data over multiple stages. When the data gradually increases or decreases during each stage of a process, there can be no better option than to use the funnel chart.
Imagine you are working in a marketing firm. You have curated the data of the leads and the conversions your firm has made. The data will change in a step-by-step cascade starting from generating leads to making conversions. Presenting the data just by stats might be hard to read and a bit difficult to understand. Here, you can use the funnel chart to effectively portray the data.
Funnel charts in Excel are not limited to marketing and conversion processes. These charts can also be used to depict any multistage industrial processes and workflows. In human resources too, they help depict the processes and conversions a candidate goes through.
Example
Consider an example where a marketing firm wants to sell a product/service. Initially, the firm starts by acquiring leads and prospects by cold call or by emailing 1432 people. About 984 people who show interest in their product have replied or responded to them. Out of the 984, 662 people want to know more about the product and have shown interest. 440 people have asked about the pricing and are negotiating. And finally, 215 people buy their product.
Representing this particular piece of data by any other form of chart might be confusing or might not properly convey the conversion. In these cases, the funnel chart is the perfect go-to.
How to Create a Funnel Chart in Excel
Using Recommended Charts
- To create a funnel chart for the required data, first, select the data.
- Go to Insert, and click on Recommended Charts.
- When you click on Recommended Charts, a dialog box opens which shows you a list of chart options that might be the best chart to depict your presentation. Choose the Funnel chart from the Recommended Charts.
- Once you click on it, you can see the description on the right side of the dialog box.
- Click OK to insert the chart into the Excel sheet.
- In case you couldn’t find the funnel chart in Recommended Charts, click on the dropdown from Insert Waterfall, Funnel, Stock, Surface, or Radar Chart and select Funnel to insert the funnel chart.
- This gives you a dynamic funnel chart that populates in the center of the sheet. If you change any data, the graph depicting the data automatically changes.
If you want to customize the chart, you can use the shortcut customize buttons on the side of the chart.
For additional customization, click on the chart or a section of the chart. Navigate to Chart Design or Format to edit the chart. Double-click on any section of the chart to open Format Data Series for in-depth personalization and customization of the chart.
Note: One advantage of creating a dynamic chart is that you can change the data in the sheet and the chart adjusts automatically. This way, you can create one chart and use this as a template to create other charts.
Also Read:
How to Make a Box Plot Excel Chart? 2 Easy Ways
How to Make a Pareto Chart Excel Dashboard? 4 Easy Steps
How to Make an Excel Bullet Chart – 2 Easy Examples
Using a Bar Chart
The Recommended Chart with the funnel chart option was introduced only from Excel 2019 and higher versions. If you need to create a funnel chart with your data in the previous versions, what would you do?
In such a case, you can use a bar chart to display the data and convert them into a funnel chart.
- For a funnel chart using the bar chart, first, you will need to create a dummy dataset. To create dummy data, insert the formula =(MAX(totalrangeofcells,1)-currentcell)/2, leaving the first row empty.
- The purpose of the dummy data is to create an intermediary space between the original data so the dummy data is plotted first and then the original. You can see that the sample data are all increasing by the stage.
- Now, with the dummy and original data ready, navigate to Insert. Click on the drop-down from the Column chart and select the Stacked Bar chart.
- Now, right-click on the dummy data in the chart and select Format Data Series.
- In the Format Data Series options, click on the Fill&Line tab. Under the Fill dropdown, select No Fill.
- Now, right-click on the Y-axis elements and select Format Axis. In the Format Axis pane, under Axis Options, check the checkbox for Categories in reverse order.
Now, this somewhat looks like a funnel. To make the bar chart into a complete funnel chart, there are some additional changes to be done.
- To change the width between the bars, right-click on any bar and enter the Format Axis pane. In the Series options, set the Gap Width to 0 or a minimum depending on your choice.
Now, we need to remove the gridlines, data, and legends.
- To clear any unnecessary data, right-click on them and select Delete.
- For the data to populate the bars, click on the Chart Elements and check the checkbox for Data Labels.
- Now, double-click on the bars. From the Fill&Line option, under Fill, select the type of fill you want to add to the bar and choose the color.
Now, we have the required Funnel Chart in Excel.
Suggested Reads:
Pivot Charts in Excel Tutorial
Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
How to Save Excel Chart as Image? 4 Simple Ways
Frequently Asked Questions
Where to find the funnel chart in Excel 2016?
You can find the Funnel Chart in Excel by navigating to Insert and clicking on Recommended Charts. You can also find it from the Insert Waterfall, Funnel, Stock, Surface, or Radar Chart and select the Funnel chart.
How do I change the color of each stage in the funnel chart?
Double-click on the section of the chart you want to change the color. This opens a Format Data Series pane on the right of the sheet. Navigate to Fill&Line and select Solid Color.
How do I create a funnel chart in Excel 2013?
If you are using Excel 2013 or older versions, you can use the Stacked Bar chart and convert them into a Funnel chart.
Closing Thoughts
Funnel charts have become an integral part of marketing presentations. Other than helping you exhibit the rate of conversion, they also help exhibit the efficiency of the progress or the personnel or team involved.
In this article, we saw 2 ways to create and insert a funnel chart in Excel. You can select the Funnel Chart from the Recommended Charts for Excel versions 2016 and later. Or, you can use the stacked bar chart and then convert them into a funnel chart following a series of steps. Each of the methods is useful in its way, choose the ones that suit you the best.
Please visit our free resources center for more high-quality guides on Excel and other Microsoft Suite applications.
Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.
Simon Sez IT has been teaching Excel and other business software for over ten years. For a low, monthly fee you can get access to 140+ IT training courses.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!