In this article, we are going to see how we can create a funnel chart in Excel. The funnel chart is a very good visual aid that helps us visualize the data stream, especially where the data is going to be reduced in successive steps.
Below, we have shown an example where we presented a sample funnel chart that was created with the help of a previously created stacked chart.
How to Create a Funnel Chart in Excel
Below we will see three separate types of funnel chart creation. Those methods are pretty simple and require very little Prior excel related experience. In order to avoid any kind of incompatibility issue, try to use the Excel 365 edition.
1. Funnel Chart From Recommended Chart
The easiest way to add the funnel chart to the worksheet is to use the recommended option.
- For this, the user needs to first select the range of cell B4:C8, and then go to Insert > Recommended Charts.
- After clicking on the recommended charts, we will see that there is a window. As we can see, there is a tab where we can see the recommended charts option.
- In that chart, we can see the funnel-shaped charts are now present.
- Select that chart and then click OK.
- After clicking OK, we can see that the chart is now present in the worksheet.
2. Funnel Chart from the Stacked Chart
Instead of using the recommended chart option, we can use the stacked chart option to create a funnel chart in the Excel worksheet.
- But first and foremost, we need to add some formulas and determine some helper values.
- For this, enter the following formula in the cell.
=(MAX($C$5:$C$8)-C5)/2
- After entering that formula and pressing Enter, we can drag the Fill Handle to cell D8.
- These are the values that are going to help us convert a simple stacked chart to a funnel chart.
- Then go to the Insert tab > Select chart type > 2d- Stacked Bar chart.
- After selecting the chart type, we can see that a chart is now present in the worksheet.
- Then you can right-click on the axis and select the Format Axis.
- After selecting Format Axis, we will notice that there is a right-sided window. In that window, go to the rightmost tab and then select Categories in the reverse order check box.
- After checking the box, we will see that the axis values are now reversed.
- Then we will add another column of values to the existing table by selecting D4:D8 in the saved data ranges. Click OK after this.
- After adding the data to the worksheet, we will see that there is new data added on top of the existing dataset present in the plot.
- Then again select the new dataset and then right-click on it.
- After that, from the context menu select Format Data Series.
- In the Format Data Series right-sided window, select the Solid fill option in the Fill
- And then white as the color of the fill-in just below it.
- For better clarity, we can change the color of the text inside the chart by right-clicking on it and then selecting Add Data Labels.
- And then choose Add Data Labels
- Now we can see that the chart has a funnel-shaped phase.
3. 3 Dimensional Excel Funnel Chart
So far, we have seen 2-dimensional charts in Excel. In the following demonstration, we are going to provide another demonstration of the funnel chart but now in 3 dimensions
- For this, we need to add a stacked chart to the worksheet.
- Select B4:C8, and then go to the Insert tab > Insert Column or Bar Chart.
- Then choose 3D-Stacked Chart.
- After adding the chart to the Excel worksheet, we can change the data row and column serial in the Select Data Source by clicking on the Switch Row/Column.
- Click OK after this.
- After that, we will notice that all of the data is now shown in a singular column.
- Now that we have the data in the worksheet, we can click on the data series and, from the context menu, select Format Data Point.
- In the format data point window, select Full Pyramid in the Column Shape
- The column shape is now shifted to the pyramid shape.
- Then again, right-click on the Y-Axis and then select Format Axis.
- Then, in the Format Axis window, check the Values in the reverse order
- The pyramid-shaped chart will be reversed.
- After some modification, the 3D funnel chart will take the final form shown below.
Things to Remember
Data Setup: Organize your data in a table format with clear headings. The first column should represent the stages or steps, and the subsequent columns should contain the values corresponding to each stage.
Data Order: Make sure your data is sorted in descending order depending on the values. The highest value should be at the top (the first stage), and the values should decrease progressively as you move down the stages.
Select Data: When inserting the funnel chart, ensure that you have selected the correct data range, including both the stage names and the corresponding values.
Chart Type Selection: Choose the right funnel chart type based on your data. Excel offers different funnel chart variations, such as the basic funnel chart or the stacked funnel chart. Select the one that best suits your data representation.
Data Labels: Include data labels on the chart to display the exact values for each stage. This helps readers understand the numbers accurately.
Title and Axis Labels: Provide a clear and descriptive chart title, and label the horizontal axis with appropriate stage names. This makes the chart easy to interpret.
Colors: Choose a visually appealing color scheme for your funnel chart. Use consistent colors or shades to distinguish between different stages.
Formatting: Format the chart elements to make them visually appealing. You can adjust the font size, colors, borders, and other chart properties to enhance its appearance.
Tooltip: Add a tooltip to the chart labels if there is low space for displaying all the data labels. This way, users can hover over the chart to see the values.
Axis Scaling: Ensure that the axis scaling is appropriate and does not distort the representation of the data. The width of each stage should accurately reflect the proportion of values between stages.
Data Validation: Double-check your data for accuracy before creating the chart. Any mistakes in the data can lead to incorrect visualizations.
Chart Placement: Choose the right location to place your funnel chart within the worksheet or the dashboard to make it more effective and visually integrated.
Conclusion
In this article, we will see how we can create various types of funnels in Excel using various methods. Those funnel types included 3-dimensional forms, stacked forms, and, most importantly, the recommended option. The user needs to understand their needs and then use the form according to them. If you need any more assistance, you can contact our Exceldemy team.
Frequently Asked Questions
Q1. What is a funnel chart for?
A funnel chart in Excel visualizes data that undergoes a progressive reduction through different stages. It represents a series of steps, illustrating how the values decrease as you move from one stage to the next. Funnel charts are commonly employed in sales, marketing, website conversions, lead generation, and event registrations to analyze and optimize processes. They help identify drop-offs, conversion rates, and bottlenecks in various workflows, aiding in data-driven decision-making. With clear visuals, funnel charts offer insights into the efficiency and effectiveness of processes, making them valuable tools for data analysis and performance tracking in Excel.
Q2. Can you create a stacked funnel chart in Excel?
Excel does not have a built-in stacked funnel chart option. However, you can create a stacked funnel chart using a workaround by combining a stacked bar chart and a funnel chart. This involves adjusting data and formatting to achieve the desired visual representation.
Q3. How many data points do funnel charts require in Excel?
Funnel charts in Excel typically require at least two data points, representing the starting and ending values of the funnel. However, for a more informative visualization, it is recommended to have multiple data points representing each stage’s values along the funnel’s progression. The more data points you have, the more detailed and accurate the funnel chart will be in showcasing the progressive reduction at each stage of the process.
<< Go Back to Excel Charts | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!