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.
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.
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.
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.