When you want to illustrate your sales funnel data, what could be better than a funnel chart? In fact, you can use them to illustrate any multi-stage process where the numbers decrease with each stage. In addition to the sales funnel, these charts are often used to represent the recruitment funnel, order fulfillment, and website traffic engagement.
In this guide, you will learn what a funnel chart is and what it’s used for, as well as how to create one in Google Sheets. While Microsoft Excel has a built-in funnel chart type, you'll need a few extra steps if you want one in Google Sheets. Fortunately, they’re very easy steps. You have detailed instructions on how to set up the data and customize the standard stacked bar chart into a funnel chart. You also have examples showing how to create a sales funnel chart and a recruitment funnel chart.
What is a Funnel Chart?
Funnel charts provide a high-level view of a multi-stage process, where the measured value decreases with each stage. Each stage is represented by a bar, starting with the initial total value at the top. Each bar is smaller than the previous one, making the chart look like a funnel.
While the length or width of these horizontal bars changes according to the value, their height is equal. The values used are usually the totals for each stage, so the ‘steps’ between stages are quite large. If you have very detailed data for each stage, you can create a funnel chart that uses trapezoids instead of bars so that the bottom of a stage matches the top of the next stage. However, be careful how you divide the stages and substages, as you may get a distorted view of your data.
What is a Funnel Chart Used for?
The funnel chart type best illustrates multi-stage processes where values decrease with each stage. Ideally, these processes should have more than three stages; otherwise, a pie or bar chart is more suitable. For example, funnel charts are an excellent way to illustrate the sales funnel or recruitment funnel stages.
How to Create a Funnel Chart in Google Sheets?
Since Google Sheets does not offer a built-in option for funnel charts, you will need to make some adjustments to a standard stacked bar chart. This will require creating a helper column with data, which you will later hide on the chart itself. Follow the steps below to set up your data, insert the stacked bar chart, and finally, customize it to look like a funnel chart.
1. Set Up the Data
First, you must set up your data and add a helper column.
- 1. Open Google Sheets and list your stages and the corresponding values.
- 2. Insert a helper column to the left of the column with the values.
- 3. Open parenthesis and select the highest value using absolute referencing. You could also use the MAX function, but if the data is appropriate for a funnel chart, the highest value should be the first.
- 4. Add a minus sign and select the value for stage one - in this case, it’s the highest value - but using relative instead of absolute referencing. Close the parenthesis.
- 5. Divide the result by two and press ‘Enter’.
- 6. Grab the fill handle and drag it down to the last stage.
2. Insert Bar Chart
Since Google Sheets has no built-in funnel chart type, you’ll need to build on a standard stacked bar chart.
- 1. Select all three columns and go to Insert > Chart.
- 2. In the ‘Chart editor’, check that the chart type is ‘Stacked bar chart’ with ‘Standard’ stacking.
When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.
READ MORE3. Customize Funnel Chart
As mentioned, you’ll need to make some adjustments to your chart to turn it into a funnel chart.
- 1. Under ‘Customize’ in the ‘Chart editor’, go to ‘Series’ and select the data from the help column: ‘Bar 1’. Change the bar’s color to match the background color; in this case, I have chosen white.
- 2. Select ‘Bar 2’ from the drop-down list and check the box that says ‘Data labels’. Under ‘Position’, select ‘Center’.
- 3. Click on ‘Legend’ to expand the options and select ‘None’ under ‘Position’.
- 4. Under ‘Gridlines and ticks’, uncheck the box that says ‘Major gridlines’.
- 5. To hide the horizontal axis, change the ‘Text color’ to match the background color.
- 6. Finally, you can add a title for your chart under ‘Chart & axis titles’.
Funnel Chart Examples
Below are two typical examples of funnel charts and the type of data with which you can use them: the sales funnel and the recruitment funnel.
If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet
READ MORESales Funnel
Follow the steps below to create a funnel chart based on your sales conversion data.
- 1. Set up your data and add the helper column, as shown in the previous section.
- 2. Select the data and go to Insert > Chart.
- 3. Ensure that the standard ‘Stacked bar chart’ is selected.
- 4. Follow the instructions in the previous section to customize the chart and turn it into a funnel chart.
Recruitment Funnel
Follow the steps below to create a funnel chart based on your recruitment process data.
- 1. Add the helper column to your recruitment funnel data, as shown in an earlier section.
- 2. Select the three columns of data and go to Insert > Chart.
- 3. Select the ‘Stacked bar chart’ and choose ‘Standard’ stacking.
- 4. Customize your sales funnel chart, as shown in an earlier section.
Conclusion
Funnel charts provide a simple but effective illustration of how values decrease as a multi-stage process advances. You can use them to show your sales conversion data, your order fulfillment data, or your recruitment data. The more detailed and structured your data is, the more clearly you can identify potential bottlenecks in your processes.
You now know what a funnel chart is and what types of data it’s usually used to illustrate. You also know that, unlike Microsoft Excel, Google Sheets doesn’t have a built-in chart type, but you have step-by-step instructions on how to set up the data and add a helper column, insert a stacked bar chart, and customize it to turn it into a funnel chart.
Check out the guides below to learn more about creating different types of charts and graphs in Google Sheets.