Create a Funnel Chart in Excel
Funnel chart showing values across multiple stages in a process, which is often used in sales and marketing departments. Normally, it is composed of a bar chart, but all bars are centered to make a funnel shape in order to give a visual picture of the stages in the process to the readers. The below Funnel chart shows the sales pipeline of a product. As we can see from the funnel chart, the product begins with a large number of sales prospects, after several stages, only a small percentage of purchases are completed.
This tutorial provides a step by step guide to show you how to create a Funnel Chart in Excel.
Create a Funnel chart in Excel 2016 or the earlier versions
Create a Funnel chart in Excel 2019 and later versions
Download the sample file
Video: Create funnel chart in Excel
Create a Funnel chart in Excel 2016 or the earlier versions
If you are using Excel 2016 or the earlier versions, to create a Funnel chart, please follow the below steps to achieve it.
Firstly, prepare your data
Supposing, you want to create a funnel chart based on the data as the below screenshot shown, you need to create a helper column.
1. Insert a new column between the Stage and the Amount columns. Give a header to this helper column and then enter the number 0 to the cell below the header.
2. Select the cell (in this case it is B4) below the zero value cell, enter the below formula into it and press the Enter key. Select the result cell and then drag the Fill Handle down to get other results.
=(LARGE($C$3:$C$8,1)-C4)/2
Note: The formula will help to center each bar in the chart.
Then create the Funnel Chart based on the table data
1. Select the table data (include the column headers), and then click Insert > Insert Column or Bar Chart > Stacked Bar. See screenshot:
Then you will get a chart as the below screenshot shown.
2. Select the blue bar, right click and select Fill > No Fill from the right-clicking menu.
3. Right click the vertical axis, and then select Format Axis from the context menu.
4. In the Format Axis pane, check the Categories in reverse order box to reverse the order of the categories.
5. Right click any bar and then select Format Data Series.
6. In the Format Data Series pane, change the Gap Width to 0%.
Now the chart looks as shown below.
7. Keep the bars selecting, click Design > Add Chart Element > Data Labels > Center. See screenshot:
8. Format the Funnel Chart as you need.
Now the Funnel chart is created as the below screenshot shown.
Create a Funnel chart in Excel 2019 and later versions
If you are using Excel 2019 and later versions, you can create a Funnel chart directly with it’s build-in Funnel chart feature.
1. Select the table data (include the column headers), and then click Insert > Insert Waterfall, Funnel, Stock, Surface, or Radar Chart > Funnel. See screenshot:
Now the Funnel chart is created immediately as the below screenshot shown.
Note: You can format the Funnel Chart based on your needs.
Download the sample file
Video: Create funnel chart in Excel
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
