A detailed guide to creating funnel charts in Google Sheets
Do you struggle with choosing the right type of chart? Look no further! Click here to find the perfect chart for your data and ideas.
What is a Funnel Chart?
A funnel chart is used to illustrate the progression of steps in a specific process. It mimics the shape of a funnel or an inverted pyramid, starting with a wide head and ending with a narrow neck. Funnel charts are commonly used in business or sales to track the number of users or customers at each stage. They visualize how the initial number breaks down into progressive parts.
A common example is visualizing how potential customers gradually become actual customers. Funnel charts 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.
How to Format Data for a Funnel Chart
Adding your data is straightforward:
- First column: Enter the description or process.
- Second column: Enter the corresponding number or values for each description or process.
How to Add Funnel Charts
We will use the stacked bar chart that we learned in our previous tutorial to create a funnel chart. Here are the steps:
Step 1. Create an auxiliary column in your data set by adding a column after the first column. Right-click on the header of the first column, then select “Insert 1 right.”
Step 2. In cell B2, under the auxiliary column, insert the following formula:
=(max($C$2:$C$5)-C2)/2The formula above determines the maximum value in our data (usually the value in the first row), then calculates the difference between the maximum value and the current value. The result is divided by 2 to center the bar. Copy the formula to the other cells in the auxiliary column.
Step 3. Select the data and insert a stacked bar chart.
Step 4. We need to remove the auxiliary bars to get a funnel chart. Under the Series option, in the Customize tab, choose the auxiliary column and set the fill opacity to 0%.
Step 5. Now you have a funnel chart! By hovering over each bar with the mouse cursor, you can see the actual number.
How to Change the Color of Bars in a Funnel Chart
You can also change the color of the bars in your funnel chart according to your preferences. To do this, go to the Series option under the Customize tab. Choose your data in the Series selector, then select your preferred color in the Fill Color options.
How to Add Data Labels in the Funnel Chart
You can also add data labels inside your funnel chart to easily display the actual number for each process. To do this, choose the Count option in the Series selector. Then, check the data labels. Under the data labels, you can also modify the positions, formats, fonts, and colors of the labels.
How to Format Certain Aesthetic Aspects of the Funnel Chart
For better presentation of funnel charts, the major gridlines and labels of the horizontal axis should be removed. However, you cannot remove these elements in Google Sheets, but you can make them disappear by modifying their formats and colors.
Under the Gridlines and ticks option in the Customize tab, choose the Horizontal Axis. Then, change the color of the gridlines to white (or any other color that matches the background color of your funnel chart).
To remove the labels of the horizontal axis, simply change the text color to white under the Horizontal Axis option in the Customize tab.
We have also discussed some common modifications of charts, such as changing the title, axis titles, and legends, in one of our previous tutorials.
Example Sheet
If you want to recreate the above funnel chart, you can use this example sheet.
Now that you know how to create funnel charts in Google Sheets, you can dive right in and make your data even more visually appealing. Have fun!
