Funnel charts are useful visualization that you can create for showing a flow through a process.
At this time there is not a built-in funnel chart option in Google Sheets, however, we still have a few options for creating one.
In this tutorial, I will show you how to create a funnel chart in Google Sheets.
Table of Contents
What is a Funnel Chart?
A funnel chart is a kind of chart that shows how values progress through a process.
They are most commonly used to visualize users through a sales process or other business processes.
The top of the funnel chart is the broadest level, and each step of the process narrows the users down to create a funnel-like shape.
Funnel charts are used when data goes through a series of stages with each stage narrowing the data.
They are useful for visualizing how a variable changes during a process.
Here is an example of a funnel chart:
The example above is a sales funnel for an email newsletter. It shows the progression from the users who received the email, all the way to those who completed purchases.
Creating a Funnel Chart with the Chart Builder
Even though a funnel chart is not a built-in option in Google Sheets, we can still create one using the chart builder by using a stacked bar chart.
The only thing we have to do is format our data properly so that the chart turns into a funnel chart.
Here are the steps to do this.
1. First open your spreadsheet, and enter the data that you will create the chart with. Here is my example data
2. Next we need to insert a helper column to help us create the funnel chart
3. After you have inserted the helper column, you will need to enter this formula into the first cell of the helper column. Here is the formula I have used in this example: =(max($C$2:$C$6)–C2)/2
4. Copy the formula down any additional cells you have in your dataset
5. Next, highlight your entire data range
6. In the top menu select Insert>Chart
7. You should now see the Chart editor on the right side of your screen. Under the Chart type section you will need to select Stacked bar chart
8. Your chart should now look something like this
9. We now need to edit our chart so that it looks more like a funnel chart. In the Chart editor select the Customize tab and navigate to the Series section. Select your Helper column from the drop-down and set the fill color to white
10. This will make your chart look something like the below chart where the helper column is now invisible so it appears your bars are floating, which turns it into a funnel chart
11. There are a few other changes we should make to the chart. Under Legend set Position to None. This will remove the legend from the top of the chart
12. Under the Chart & axis titles settings lets remove the Vertical axis title
13. Then set it to Chart title, and write in a title if you want to have a title for your funnel chart
14. Next we should add data labels so the bars in our chart are numbered. Under the Series settings, select your values and check the Data labels box. I have also changed the font color of my data labels to black, but that is optional.
15.
16. You have now created a funnel chart and it should look something like the below chart
Creating a Funnel Chart Using SPARKLINE
You can also use the SPARKLINE function to create a funnel chart.
In the cell where you want the first bar of your funnel chart to appear, place this formula:
=sparkline(B2,{“charttype”,“bar”;“max”,max($B$2:$B$6); “rtl”,true;“color1”,“#746AB0”})
In my example, my first data point is in cell B2 and my entire range is cells B2:B6. You will have to adjust these cell references according to your own data.
This will create the first bar of data for your chart:
Then you can copy and paste this formula down additional rows and it will create the other bars:
After the left side of your chart is created, we have to change up the formula a little bit for the right side of the chart.
Here is the new formula:
=sparkline(B2,{“charttype”,“bar”;“max”,max($B$2:$B$6); “color1”,“#746AB0”})
From here it will be the same process. Put the formula in the first cell for the right side of your data, and then copy and paste it down any additional rows as needed to complete the chart.
Closing Thoughts
I have now shown two different ways to create a funnel chart in your spreadsheet.
If you want a more detailed chart with customization options and labels, then the stacked bar chart method is the best option.
Sparkline charts can be created more quickly, but you don’t have as much customization options as with any chart created through the Chart builder.
Still, if you just want a quick visual, this may be a good option for you.
More Google Sheets Tutorials:
How to Make a Scorecard Chart
How to Make a Timeline Chart