In this Article
What is a Sales Funnel Chart?
Funnel charts are usually used to represent a process’s stages and their outputs. A Sales Funnel Chart does this with sales.
Like the name entails, the chart looks like a funnel made up of stacked bars, as shown below.
Other Funnel Charts
There are many other uses for Funnel Charts.
- Number of employees at each job level (entry-level, experienced, manager, executive, etc.)
- Recruitment process (applied, shortlisted, initial interview, etc.)
- Order fulfillment (orders received, orders processed, orders approved, etc.)
Sales Funnel Chart Example
To make the process easier to understand, here’s a scenario:
Your goal is to sell a product that caters to a certain demographic, and the sales process has multiple stages. You can make a Sales Funnel Chart to depict the number of people that make it through each stage of the process, with each bar representing a stage, and the width of each bar representing the number of people that pass that stage.
Here’s an example of a sequence of stages:
- Research: Start off with about 5000 people to research.
- Qualification: About 3400 of these people fit the demographic you are looking for.
- Presentation: You send invites to these 3400 people to come to a sales presentation where you can pitch your product. 2700 people decide to come.
- Interest: Out of these 2700 people, 1500 stay to express interest in buying the product.
- Close: 800 people actually buy the product.
From here you have 6 main steps:
Step 1: Organize Data
To start off, create three columns.
The first column would consist of your stage names. For the sake of simplicity, let’s just stick with Stage 1, Stage 2, Stage 3, Stage 4, and Stage 5.
Label the second column Spacer (this will be used to center the bars). Keep the rest of this column blank for now.
Label the third column with your desired unit of output. In this example, I will use Stage Winners. Now list the number of units for each stage (in this case, the number of people that make it to each stage).
It should look a little something like this:
In the Spacer column, we’ll be using Excel formulas!
Skip Stage 1, and start on Stage 2.
Each row’s equation will be slightly different, and is best shown through example.
In Stage 2’s Spacer box, type:
The =(MAX($C$2:$C$6) part stays consistent throughout the column because it selects the max number in the Stage Winner’s column.
Then you need to subtract the box coordinates of Stage Winner’s at whatever Stage you are on.
Divide the whole thing by 2 to center the bar.
Use the numbers I listed under Stage Winners first as a way to check if you are using the equations correctly. Your output should look like this:
Step 2: Create a Stacked Bar Chart
Now select the data including the labels for the vertical axis
Go to Charts tab à Bar à Stacked Bar Chart.
Click that, and now you should have something that looks a little like this:
Now you must be thinking, “Wait, isn’t this supposed to be upside down?”
No worries, we just have to reverse the order of the categories.
Step 3: Reverse Order of Categories
Right click the vertical axis (the one with the Stage Names) and select Format Axis.
There should be a checkbox that says Categories in reverse order”, so click that.
Now your stages should be in the same order as how they’re listed on your chart.
Do you see how spaced out everything is? We want to get rid of that.
Step 4: Change Gap Width to 0%
To do this, we want to get the gap width to 0% so that it looks more like a connected funnel than a couple bars floating above each other.
Just right click any of the bars and select “Format Data Series”.
From there change the Gap Width to 0%.
Now your chart should look like this:
Now we want to make the spacer bar transparent so that only the data we want analyzed would appear.
Step 5: Set Spacer Bar Color to No Fill
Right click any of the bars in the Spacer series and select “Format Data Series”.
Change the Fill Color in the series options to No Fill.
Your chart should now look like this:
Step 6. Customize your Chart
Now here is the fun part!
You can customize your funnel chart so that it looks presentable for all your presentation needs.
To get to this chart:
I did the following:
- Changed the fill color from red to blue (Right Click Data Series à Format Data Series à Fill)
- Removed the shadowing (Right Click à Format Data Series à De-Select Shadow)
- Changed the outline to a different color from the fill (Right Click Data Series à Format Data Series)
- Removed the horizontal axis units (Right Click Horizontal Axis à Delete)
- Removed graph lines (Right Click Gridlines à Delete)
- Added data labels to the middle of the bars (Right Click Data Series à Add Data Labels)
- Added a chart title (Charts à Chart Layout à Chart Title à Title Above Chart)
- Removed the legend. (Right Click Legend à Delete)