Funnel charts in Google Sheets using the chart tool, formulas and Apps Script

Let’s talk about funnel charts in Google Sheets.

The charts themselves are a bit of a novelty. Yes, they’re aesthetically pleasing because of that resemblance to a real-world, tapering funnel, which reinforces their message, but a plain ole’ bar chart would be equally suitable and actually easier to read data from (because the bars have a common baseline).

However, they throw up some interesting techniques in Google Sheets and for that reason, merit this long article.

We’ll build them using tricks with the chart builder tool, then with two different types of funky formula and finally, and best of all, we’ll build a tool using Apps Script, as shown in this image:

Funnel charts with apps script in Google Sheets

As with the waterfall charts in Google Sheets, they’re not one of the out-the-box charts available to us, so we have to manually create them with a crafty workaround. Thankfully, they’re relatively simple to create, certainly simpler than the waterfall chart.

For all of these examples, we’ll use this fictitious real-estate dataset:

Google Sheets funnel chart data

Here, I’m imagining the real estate agency collects data relating to their sales funnel, and they want to display it in a funnel chart format.

Click here to open up the Google Sheet template and make your own copy (File > Make a copy...).

Funnel charts with Google Sheet’s embedded chart builder

As with the waterfall charts in my earlier post, the trick here is to use a stacked bar chart with transparent base bars, to achieve the floating effects:

Funnel chart in Google Sheets

Step 1: Create a helper column

So the first thing to do is to create a helper column containing the values for the transparent bars.

Move the data from column B into column C, so B1:B7 are now sitting in cell range C1:C7. In the new blank column, add the heading “Helper column” and insert the following formula:

=(max($C$2:$C$7)-C2)/2

This formula determines the max value in our data (in this case 636), calculates the difference between the max and the current value and then divides the result by 2 to center the bar, like so:

Funnel chart with data helper column in Google Sheets

Step 2: Insert a chart

Highlight the new data range, including the helper column, and click Insert > Chart....

Make sure you select Stacked Bar Chart from the Chart Types options.

You should end up with a chart like this (image shows how the data is plotted):

Helper column funnel chart

Step 3: Set the helper column bar color to none

Set the helper column bars to be transparent, thereby “hiding” them to give the appearance of floating bars in a funnel chart.

Set stacked bar to transparent in Google Sheets chart

Step 4: Add data labels and remove x-axis

This is an important step. Take a look at the x-axis as it stands. It’s meaningless really, because our bars are floating in the middle of the chart area.

We can fix this by adding data labels, then removing the x-axis labels altogether:

Add data labels and hide x-axis

That’s it!

Our final chart should look something like this:

Funnel chart in Google Sheets

Funnel charts with sparklines

We can use symmetrical sparkline formulas to create funnel charts:

Sparkline funnel chart in Google Sheets

Assuming we have the same data above, in the range A2:B7, then use the following sparkline formulas:

In column D:

=sparkline(B2,{"charttype","bar";"max",max($B$2:$B$7); "rtl",true;"color1","#FFA500"})

Notice the option "rtl",true, which specifies the direction of the bar within the cell, allowing the symmetrical appearance.

In column E:

=sparkline(B2,{"charttype","bar";"max",max($B$2:$B$7); "color1","#FFA500"})

as follows:

Sparkline funnel chart in Google Sheets

Funnel charts with the REPT() function

Another method, similar to the sparkline approach. We use the REPT() formula and some funky font formatting to create the funnel chart.

Here’s what the final chart looks like:

Funnel chart with REPT formula

Again, with the data in range A1:B7, add the following formulas into column D:

=rept("|",B2/10)

The “|” is the pipe symbol, usually found above the Enter key.

The value 10 is a scale factor to ensure the largest bar fits into the cell, so feel free to adjust this as needed until your largest bar fits. It’s imperative to use the same scale factor value in each formula though.

Next change the font to make the pipe symbols thicker – I’ve found font Modak works pretty well for this – and then ensure all the cells with the REPT formulas are center aligned. :

Using fonts to create funnel chart in Google Sheets

Using Apps Script to build an automatic funnel chart tool

Saving the best to last, let’s write some apps script code to create each of these charts automatically for us!

If you’re new to Apps Script, check out my introductory post on the subject first.

I’ll show you how to use the apps script template first, then run through the code.

Steps to use this template:

1. Click here to open up the Google Sheet Apps Script funnel chart.

2. Create your own copy (File > Make a copy...)

3. Open the Script Editor window (Tools > Script editor...)

Script editor

4. Select and run the onOpen() function to add the custom menu

Run onOpen menu

5. Click Review Permissions in the Authorization required popup:

Apps script review permission

6. Review the permissions, in this case accessing your spreadsheets in Google Drive, and click Allow:

Review spreadsheet permissions apps script

7. Return to your spreadsheet, to the tab called With Apps Script, and you’ll now have a Custom Chart menu, with options for the three different funnel charts. Highlight your data (excluding the heading) and then insert the funnel chart you’re after:

Funnel charts with apps script in Google Sheets

Boom! You have your own working copy of the funnel chart tool.

For those wanting to see under the hood, let’s take a look at the code:

Code for the custom menu

First step is to add a custom menu so that we can simply click a button to insert the funnel chart of our choosing (embedded charts, sparklines or REPT formulas).

This is straightforward boilerplate code from the Apps Script documentation (read more here).

Funnel chart code using the chart builder

This is a two stage process: first, create the helper column so the data is ready, and second, insert a chart using Google Sheets’ chart tool.

In lines 5 – 26, we grab the data range, move the values column one to the right and insert a new value into the helper column, using the formula [(maxVal - d[1]) / 2]

Inserting the chart is relatively straightforward, and most of the code in lines 31 – 44 relates to setting options to make the chart look the way we want.

Here’s a challenge to you (and me): add the data labels to the bars.

Hint: start with this article in the Apps Script documentation.

Funnel chart code for sparkline version

In this example, there’s no need to change the data.

So I just need to create the left and right sparklines in adjacent columns. I do this by creating a left array, formulasLeft, and a right array, formulasRight, to hold all these text formulas. I loop over the original data values and for each one, create sparkline formulas that I add to formulasLeft and formulasRight respectively.

Finally, I identify the output range and paste in these left and right formulas. I use the R1C1 notation to allow me to position formulas relative to where the original data values are.

They show up as sparklines in our Google Sheet!

Funnel chart code for REPT version

This is pretty similar to the sparkline version above and even simpler because I don’t need to create two sets of formulas with the left/right symmetry.

Again I loop over my array of data values and create a REPT formula text string in a new array. Then I identify the output range and insert this array of formulas.

The final step is to format the output to the correct font and color to achieve the desired funnel chart look.

The full code is available here on GitHub.

Check out this GIF showing the apps script in action:

Funnel charts with apps script in Google Sheets

If you’ve enjoyed this post, check out my other recent Google Visualization articles: Waterfall charts, animated temperature charts and Tufte recreations.

One thought on “Funnel charts in Google Sheets using the chart tool, formulas and Apps Script”

Leave a Reply

Your email address will not be published. Required fields are marked *