This post explains how to make a funnel chart in Excel. This chart can be created in 5 simple steps using a stacked bar chart.
The Excel file used to create this chart can be downloaded below for free.
What Is A Funnel Chart?
A funnel chart is similar to a bar chart, but the bars are centered to create a funnel shape. In this example, the funnel chart displays how many customers are in each stage of our sales pipeline at a specific period in time.
Why Use A Funnel Chart?
Because it's FUNnel! 🙂
Ok, besides that great reason, the funnel chart can help give the reader a visual picture of the stages in the process. In the case of the sales pipeline, we start out with a lot of customers in Stage 1 (Initial Contact), but only a small percentage of those customers make it to Stage 5 (Close) where the purchase is made.
Centering the bars on the plot area gives the appearance of a funnel shape. Your data may not always produce an exact funnel shape like this one, but centering the bars makes the funnel process easier to understand and explain.
5 Steps to Creating the Funnel Chart
The funnel chart can be created in 5 simple steps using a stacked bar chart.
The stacked bar chart contains a “spacer” series that centers the bars. The data for this spacer series is created using a simple formula, and then plotted as the first series on the stacked bar. We then change the fill color of the series to be transparent, so the viewer only sees the funnel data.
You can download the file below to follow along. Or just copy the ‘Funnel Chart Example' sheet into your file, then change the data to make it your own.
Sales Pipeline Funnel Chart.xlsx (118.1 KB)
Step 1: Setup Data & Create Stacked Bar Chart
The first step is to add a column of formulas to the left of your data column. This formula creates a spacer that will center each bar based on the largest number in your data set.
Spacer Formula: The formula finds the maximum (MAX) number in the data series, subtracts the amount in the current data row, then divides it by 2.
Once we have these two columns of data, then select the data and create a Stacked Bar Chart.
Step 2: Reverse the Order of the Categories
When the chart is initially created the categories will be listed in the opposite order from how they are listed on the sheet.
- To reverse this, right-click the vertical axis (the one with the category names), and select Format Axis…
- In the Axis Options click the checkbox that says “Categories in reverse order”.
The categories will now be listed in the same order as they are listed on the sheet.
Step 3: Change the Gap Width to 0%
Next we will change the width of the bars so the borders touch. This gives the chart more of a funnel look.
- Right click on any of the bars and select “Format Data Series…”
- In the series options change the Gap Width to 0%.
Step 4: Change the Spacer Bar Color to No Fill
The fill color of the spacer bar needs to be transparent. This will make it invisible to the reader, and only the actual funnel data will appear.
- Right click on any of the bars in the Spacer series (Series 1) and select “Format Data Series…”
- Change the Fill Color in the series options to No Fill.
Step 5: Format the Chart for Presentation
You can now format the chart and remove all the extra junk. Excel adds a lot of elements (gridlines, legends, etc) that are not needed for this visualization.
Here are the steps I took to format the chart:
- Remove the legend.
- Remove the vertical gridlines.
- Remove the vertical axis border (no line).
- Change the fill color and border color of the funnel bars.
- Change the chart title (include the date or time period of the data).
- Add the data labels to the funnel bars and center them.
The file can be downloaded below. It contains these instructions to create the chart, or you can copy the ‘Funnel Chart Example' sheet right into your workbook and change the data.
Sales Pipeline Funnel Chart.xlsx (118.1 KB)
Other Uses for a Funnel Chart
The example above used customer count data for different stages of a sales pipeline. The funnel chart could also be used to display:
- Number of students in each stage of education (undergrad, grad, phd, etc.)
- Number of employees in each job level (subordinate, manager, executive, etc.)
- Amount of receivables in aging (30, 60, 90 days past due)
- Age distribution of customers/population (20s, 30s, 40s, etc.)
What are some other uses for the funnel chart? Please leave a comment below with any questions or suggestions. Thank you!