Unsure which chart type to use? Click here to find the right chart for your data and the ideas that you want to convey.
A funnel chart is used to show the progress of connected stages in a specific process. The chart mimics a funnel or inverted pyramid, starting in a broad head and ending in a narrow neck. It is commonly used in the business or sales process where the starting number of users or customers are tracked through each stage. It shows how the starting whole breaks down into progressive parts.
One common example visualizes how potential customers gradually become customers. The funnel chart can help businesses identify their potentials and areas that need improvement.
In this tutorial, we will learn how to create funnel charts in Google Sheets.
Adding your data is very straightforward,
We will use the stacked bar chart we learned from our previous tutorial to create a funnel chart. Here are the steps.
Step 1. Create a helper column on your dataset by adding a column after the first column. To do that, right click on the first column header, then select Insert 1 right.
Step 2. In cell B2, under the helper column, insert the following formula:
=(max($C$2:$C$5)-C2)/2
The above formula determines the maximum value in our data (usually the value in the first row), then calculates the difference between the maximum and the current value. The result is divided by 2 to center the bar. Copy the formula to other cells in the helper column.
Step 3. Select the data and insert a stacked bar chart.
Step 4: We need to remove the helper bars to give a visual of the funnel chart. Under the Series option, on the Customize tab, choose the Helper column and set the fill opacity to 0%.
Step 5: You now have a funnel chart. Hovering the mouse pointer over each bar shows the actual count.
You can also change the color of the bars in your funnel chart based on your preference. To do that, go to the Series option under the Customize tab. Choose your data on the Series selector. Then, select your preferred color on the Fill color options.
You can also add data labels within your funnel chart to easily see the actual number for each process. To do that, choose the Count option on the Series selector. Then, tick on the Data labels. Under data labels, you can also modify label positions, formats, fonts, and colors.
For better presentation of the funnel charts, major gridlines and horizontal axis labels should be removed. However, you cannot delete these aspects in Google Sheets, but you can make them disappear by editing their formats and colors.
Under the Gridlines and ticks option on the Customize tab, choose the Horizontal Axis. Then, change the Gridline color to white (or any color that matches the background color of your funnel chart).
To remove the horizontal axis labels, just change the text color to white under the Horizontal axis option on the Customize tab.
We also discussed some common modifications of charts such as changing the title, axis titles, and legends in one of our previous tutorials.
In case you want to recreate the funnel chart above, you can use this sample sheet.
