What Is Funnel Chart In Excel?
A Funnel Chart in Excel graphically displays the progressive reduction of different phases of the activities. The graph starts with a broader horizontal bar, with the following bar sizes reducing phase-wise, representing the shape of a funnel, hence the name. Users can use the Funnel plot to summarize data involved in an activity, such as sales and workflow stages, and locate bottlenecks in the lead conversion funnel.
For example, the below table shows the various stages of a sales product. The stages start from the time of many sales prospects to the one where a small chunk of prospects successfully converts to sales.
We can insert Funnel Chart in Excel to graphically represent the above data. We get the above Funnel Chart in Excel that shows different stages in a product’s sales pipeline in the form of horizontal stacked bars. And the bars vary in size according to the given amount values for the various phases in the pipeline.
Table of contents
Key Takeaways
- The Funnel Chart in Excel is an inbuilt chart from Excel 2016 and above. It helps review sales processes, lead conversions via emails and client interactions, and various stages in a workflow.
- If the Excel version is 2013 or older, use the 2-D Stacked Bar chart and modify it to achieve a Funnel plot. However, we can also use 3-D 100% Stacked Column chart and modify it to get a Funnel graph in the form of a pyramid.
- We can add percentages to the Funnel plot or quantify the stages using values such as the count of customers or deal values in USD.
How To Create Funnel Chart In Excel?
The steps to create a Funnel Chart in Excel are as follows:
- Ensure the source table contains all the stages of the given process and that their associated values are accurate.
- Select the entire table and then Insert – Recommended Charts – Funnel chart. Otherwise, we can select Insert – Insert Waterfall, Funnel, Stock, Surface, or Radar Chart – Funnel chart to insert Funnel Chart in Excel.
- Finally, update the chart and axis titles to achieve the required Funnel plot.
We will create a Funnel Chart using the Excel Version 2013 And Older, and the Excel Version 2016 And Above for the following example.
Consider the below table containing the different stages in an email conversion rate.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
#Excel Version 2013 And Older
The steps to create a Funnel Chart template using Excel Version 2013 And Older are,
Step 1: Insert a column between the Stage and Users columns to include dummy data.
Step 2: Enter the cell B4 value as 0, and in cell B5 enter the formula =(LARGE($C$4:$C$8,1)-C5)/2. The result in cell B5 is 500, as shown below.
Step 3: Drag the formula from cell B5 to B8 using the fill handle.
Step 4: Click anywhere on the table – select the “Insert” tab – go to the “Charts” group – click the “Insert Column or Bar Chart” option drop-down – select the “Stacked Bar” chart type from the “2-D Bar” group, a shown below.
The following chart gets generated.
Step 5: Right-click the vertical axis à select the Format Axis option.
In the “Format Axis” pane, select the “Axis Options” option in the drop-down – click the “Axis Options” tab – check/tick the “Categories in reverse order” checkbox in the “Axis position”, as shown.
Step 6: Right-click the “Dummy Data” data series in the chart area – select the “Format Data Series” option.
In the “Format Data Series” pane, select the “Series Options” option in the drop-down – select the “Fill & Line” tab – select the “No Fill” option in the “Fill” group, as shown below.
Step 7: Right-click the Users data series – select the “Format Data Series” option.
In the “Format Data Series” pane, select the “Series Options” option in the drop-down,
- Select the “Series Options” tab – set the “Gap Width” to 0%.
- Select the “Fill & Line” tab – go to the “Fill” group – choose the “Automatic” option and the desired color from the color option, as shown below.
- Select the “Fill & Line” tab – click the “Border” option – choose the “Solid Line” option, and the desired color from the color option, as shown below.
Step 8: Remove the horizontal axis and the legend by selecting and deleting the respective elements in the chart area.
So, the chart will become:
Step 9: Right-click the data series – select Add Data Labels option.
We will get the chart with the data labels or the cell values, as shown below.
Next, select the added data labels, change the font color and make it bold using the options in the Home tab – Font group – Font styles.
Step 10: Click the Chart Elements icon, ‘+’, to enable the Axis Titles and disable Gridlines options.
Step 11: Finally, delete the horizontal axis title, and update the chart and vertical axis titles by selecting the respective elements in the chart area and performing the required actions.
Finally, we get the required Funnel Chart, as shown above, without using the inbuilt option.
Excel Version 2016 And Above
The steps to create a Funnel Chart in Excel template using Excel Version 2016 And Above are,
Step 1: Click on the data table – select the “Insert” tab – go to the “Charts” group – click the “Recommended Charts” option, as shown below.
And in the Insert chart window – select the “Recommended Charts” tab – select the Funnel chart type – click “OK”, as shown below.
[Alternatively, click the table – select the “Insert” tab – go to the “Charts” group – click the “Insert Waterfall, Funnel, Stock, Surface, or Radar Chart” option drop-down – select the “Funnel” chart type from the “Funnel” group.
We get the required “Funnel Chart”.
Step 2: As explained in the previous section, update the chart and vertical axis titles using the Chart Elements option, change the data label font color, and make them bold.
The final generated Funnel chart is shown below.
The above Funnel Chart graph shows the different steps in the email conversion rate. The process started with 5000 users receiving the emails leading to 1000 of them making successful product purchases.
Examples
We will create the Funnel Chart with different scenarios and with the current Excel version, i.e., 2016 and above.
Example #1
We will create the Funnel Chart for the table below, which shows the different stages of a sales funnel and the deal value at every stage.
The steps to create a Funnel Chart in Excel are as follows:
Step 1: Click the source table – select the “Insert” tab – go to the “Charts” group – click the “Insert Waterfall, Funnel, Stock, Surface, or Radar Chart” option drop-down – select the “Funnel” chart type from the “Funnel” group.
Step 2: Click the data labels, change the font color and make it bold using the options in the Home tab – Font group – Font styles.
Step 3: Finally, click the Chart Elements option (‘+’ icon) to enable the Axis Titles option.
And update the chart and vertical axis titles by clicking the respective elements in the chart area to achieve the required sales Funnel Chart.
Thus, the above sales Funnel plot shows the deal value to be $200k at the time of sales opportunity identification. And finally, the deal value at the time of winning the sales opportunity is $89k.
Example #2
We will create the Funnel Chart to explain how to add percentage.
The table below shows the stages of a pipeline sales funnel, with the expected customer % to cover each stage.
The steps to create a Funnel Chart in Excel are,
Step 1: Click the source table – select the “Insert” tab – go to the “Charts” group – click the “Insert Waterfall, Funnel, Stock, Surface, or Radar Chart” option drop-down – select the “Funnel” chart type from the “Funnel” group.
The following chart gets generated.
Step 2: Change the data label color and make it bold. And then, update the chart and vertical axis titles as explained in the previous example to get the below Funnel plot.
[Alternatively, the steps to add percentage to Funnel Chart in Excel by using 3-D 100% Stacked Column chart are as follows:
Also, we shall see how to use a to create a Funnel graph in the form of a Pyramid chart.
Step 1: First, we will modify the source table as depicted below:
Step 2: Select the cell range A2:B7 and select Insert – Recommended Charts to open the Insert Chart window.
Then, select Insert Chart – All Charts – Column – 3-D 100% Stacked Column chart.
Thus, the chart we obtain is as follows:
Step 3: Right-click the vertical axis and select the Format Axis option from the contextual menu.
Enable the option to reverse the order of the values in the axis.
Step 4: Right-click the data series and choose Format Data Series from the contextual menu.
Set the Column Shape as the Full Pyramid.
5: Insert rows between each stage in the table to introduce spaces between the phases in the graph.
And enter the stage in the newly inserted rows as Space, with Customer % as 10%.
Step 6: Right-click the chart area and choose the Select Data option from the contextual menu to open the Select Data Source window.
The “Select Data Source” window appears.
Update the Chart data range to include the newly inserted rows.
So, the chart becomes:
Step 7: To show space between each stage, we need to make the data series for the newly inserted rows transparent in the graph.
For that, right-click the data series of the first Space row and pick Format Data Series from the contextual menu.
Set the Fill option as No Fill in the tab, Fill & Line.
Repeat the process to make all the Space rows transparent in the chart.
Step 8: Remove the axis by clicking the respective axis and pressing the Delete button.
After removing the horizontal and vertical axis labels, the chart will look as shown below.
Step 9: Click the chart area to access the Chart Elements option (‘+’ icon) and disable the Legend and Gridlines options.
Step 10: Right-click the first data series and choose Add Data Labels from the contextual menu.
Next, drag the data label to the required position and right-click the data label to select Format Data Labels from the contextual menu.
Check and uncheck the Series Name and Show Leader Lines options, respectively.
Repeat step 10 for the other stages to get the below Funnel plot with percentages.
Step 11: Update the chart title as “Funnel Chart: Pipeline Sales Funnel” to achieve the final funnel chart in the form of a pyramid, shown below.
Uses Of Funnel Chart In Excel
The uses of the Funnel Chart are:
- It visually helps to determine the percentage of leads converted to successful deals in a sales funnel.
- We can use the Funnel Chart to analyze a website visitor trend.
- The Funnel graph helps review data such as the count of students in various stages of the education system and employee distribution across the organization structure.
Important Things To Note
- Use the Funnel Chart in Excel when our source data includes sequential features spreading across at least four stages.
- Insert the Funnel plot if the total data points in the first stage exceed the numbers associated with the final stage.
- Use the Funnel graph if we need to visually summarize data and locate bottlenecks in processes such as the sales funnel.
Frequently Asked Questions
The Funnel Chart in Excel is in the Insert tab. Use one of the following options to insert the Funnel plot.
Method 1 – Using the Recommended Charts –
Click Insert – Recommended Charts to open the Insert Chart window, where we must select the Funnel graph.
Method 2 – Using the Funnel Chart –
Click Insert and then choose Insert Waterfall, Funnel, Stock, Surface, or Radar Chart – Funnel chart to insert the Funnel plot.
The broad classification of Funnel chart types in Excel is as follows:
1. Trapezoidal.
2. Stacked Bar.
We can get a Funnel Chart using the REPT() as an in-cell chart. Let us see the steps with an example.
Consider the below table contains the different stages in a sales funnel.
The steps to create a Funnel Chart in Excel using the REPT() function are:
Step 1: Select cells C3:C8 and set the Font in the Home tab as Webdings.
And set the text alignment for the selected cells as Center.
Step 2: Select cell C3, enter the formula =REPT(“g”,(B3/1000)), and press Enter.
Step 3: Drag the fill handle downwards to copy the formula in cells C4:C8.
We get the above result.
[Output Observation: Let us consider the cell C8 formula to understand how the formula works. First, the cell B8 value, $5,000, gets divided by 1000 to return the value 5. And the REPT() repeats the letter “g” five times. However, as the Font chosen is Webdings, we see the five gs’ as a bar.
Thus, we get a chart in the form of a funnel, with each stage represented as a bar and each bar size proportionate to each stage’s value in USD.]
Download Template
This article must help understand the Funnel Chart in Excel, with its formula and examples. We can download the template here to use it instantly.
Recommended Articles
This has been a guide to Funnel Chart in Excel. Here we create chart, use stacked Bar/Column – 2013 & older excel versions, examples & downloadable template. You can learn more from the following articles –
Leave a Reply