2 Different Methods to Create a Funnel Chart in Your Excel

When you need to show the information in a chart according to the values, you can create a funnel chart. Hence, you can use the two methods in this article to create such a chart.

The information in a funnel chart can show the original data in a more intuitive way than the ordinary chart. Funnel chart can also reflect data and information in a clearly way. Therefore, in such a chart, you can know the relationship of different data and information clearly. The image below shows a range of sales volume of our product in the first half year. And now we will use this example to create a funnel chart for this range.

In newer version of Excel, you can create a funnel chart directly. This feature is indeed very convenient. But if you use older version of Excel, you need to make additional settings. That’s because there is no direct options of funnel chart. And now you can follow the two different methods in this article below to create a funnel chart. Here we will create a funnel chart by using Excel 2010. And the steps are very easy to learn.

Method 1: Use a Stacked Pyramid Chart

In this method, you need to first insert a stacked pyramid chart and then modify it.

1. Select the target range in the worksheet.
2. And then click the tab “Insert” in the ribbon.
3. Next click the button “Column” in the toolbar.
4. In the drop-down menu, choose the “Stacked Pyramid”. Thus, the new chart will appear in the worksheet.
5. When the chart appears, you will see the “Chart Tools” tab in the ribbon. And here click the button “Switch Row/Column” in the toolbar.
6. Right click in the chart.
7. In the new menu, choose the option “3-D Rotation”.
8. In the new window, change the rotation of the X and Y into “0”.
9. Next close this window.
10. And then right click the vertical axis.
11. In the pop-up menu, choose “Format Axis”.
12. In the “Format Axis” window, check the “Values in reverse order”.
13. Next close this window. Now come back to the worksheet, you will see the effect.
14. After that, click “Layout” tab in the ribbon.
15. And then click the button “Data Labels” in the toolbar.
16. Next choose the option “Show” to add data labels for the chart.
17. In this step, delete the gridline in this chart.
18. And then click the tab “Layout”
19. Here click the button “Chart Floor”.
20. In the menu, choose the option “None” to delete the chart floor.
21. And then delete the horizontal axis and vertical axis. Thus, your will get this funnel chart like the image below show.

Method 2: Insert Stacked Bar Chart

Except for the above method, you can also use the stacked bar chart in the worksheet. And here you need to filter the data and add another new column for the worksheet.

1. Select a cell in the range.
2. And then click the button “Sort & Filter” in the toolbar.
3. Next click the “Filter” in the drop-down menu.
4. Now click the button in the cell B1.
5. And then click the option “Sort Smallest to Largest” in the menu.
6. In C1, input the header of the new column. Here we input “Add the Additional Number” into it. You can add it according to your need.
7. In cell C2, input the formula into it:

=($B$7-B2)/2

In this formula, we will get the additional number for each number. Thus, in the following steps, we can make sure that all the column of the sales volume series will have the same center line.

8. And then press the button “Enter” on the keyboard.
9. Next click the cell C2 again.
10. In this step, double click the fill handle of this cell. Thus, you have filled this whole column.
11. After that, select the whole range.
12. Next click the tab “Insert” in the ribbon.
13. Then click the button “Bar” in the ribbon.
14. In the drop-down list, click the “Stacked Bar”.
15. And then click the button “Select Data” in the toolbar.
16. In the “Select Data Source” window, change the order of the two legend entries.
17. Next click “OK”.
18. In the chart, right click the series of the column that you have added.
19. And then in the pop-up menu, choose the “Format Data Series”.
20. In the “Format Data Series” window, click the option “Fill” on the right.
21. And then click the option “No fill” on the right.
22. Next close this window.
23. Now you will come back to the worksheet again. Right click another data series in the chart.
24. And then also choose the “Format Data Series” in the menu.
25. In the new window, change the gap width into “0”.
26. Next close this window.
27. Now add the data label for this chart. Be careful that don’t add the data label for the other data series.
28. And then delete the legend.
29. After that, delete the horizontal axis and the gridline in this chart. Thus, you will get this new funnel chart in this worksheet.

You can also make additional settings according to your need to modify this funnel chart. And both of the two methods are very easy to manipulate.

The Comparison between the Two Methods

From the above analysis, you have learned the two different methods to create an intuitive funnel chart. But there still exist differences between these two methods. When you don’t know which method you can use, you can refer to the table below.

From this table, you must have a clear understanding about the two different methods. The next time when you need to create a funnel chart for your worksheet, you will know which methods you can use.

What’s the Importance of Backing up

Actually we all know the importance of backing up your essential files, especially the Excel files that contains data and information. However, the more important those files are, the greater chances there exist that they will be damaged by hackers or malicious malware. A mission of those accidents is that you need to repair your files. When you cannot get access to your files, you can consider a data disaster. In order to repair Excel xls data problem and get back all your data and information, you can use out powerful Excel recovery tool. This tool is equipped with the best technology in the world, and it can fix almost all the errors in Excel. Compared with other recovery tool, our software is indeed your first choice.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word docx corruption and outlook repair software products. For more information visit www.datanumen.com