Google Sheets features a variety of built-in chart types, including classics such as the line chart, the bar chart, or the pie chart. However, some types may be less familiar to you. One such chart type is probably the waterfall chart. The reason for this is that it has a very specific use: showing how positive and negative components account for the net change in a variable.
In this guide, you will learn about waterfall charts and what they’re used for, as well as how to read them. You will also learn how to create a sequential waterfall chart in Google Sheets, including how to set up the data, how to add subtotals, and how to customize it. Finally, you also have step-by-step instructions on how to create a stacked waterfall chart.
What is a Waterfall Chart Used for?
A waterfall chart is useful when you need to show the additions and subtractions to a variable that led from the initial value to the final one. While this means that this type of chart has a very specific use, you can find that use in a variety of contexts.
Among other things, you can use waterfall charts to illustrate stock price fluctuations, revenue or profit flows, budget spending, and changes to inventory or staff. In other words, while not as well-known as older chart types, like the line graph or the histogram, it is very effective and popular in specific sectors.
Before they were available as built-in chart types in programs like Microsoft Excel and Google Sheets, creating waterfall charts was somewhat challenging, requiring a lot of data setup and adjustments to existing chart types. Now, you can create waterfall charts in just a few steps. However, before you learn how to create one, let’s review how to read them.
How Do You Read a Waterfall Chart?
In a typical waterfall chart, the bar on the left represents the initial value, while the bar on the right represents the final one. Between these, component bars represent the changes that led from the initial value to the final one.
The connecting lines between components indicate whether the change represents an increase or decrease, but this takes some getting used to. To make the relationships clearer, you can use different colors for increases and decreases. In the next section, you will learn how to create the chart shown above.
How to Create a Waterfall Chart in Google Sheets?
Follow the steps below to create a revenue waterfall chart in Google Sheets.
1. Open Google Sheets
2. Set Up Data
Create a table like the one shown below, with labels in the left column and values in the right. On your table, the top row represents the value at the start of the year. After that, list each month’s revenue flows.
3. Insert Chart
Select the table created in the previous step and go to Insert > Chart.
If it isn’t selected automatically, change the chart type to waterfall chart using the dropdown menu in the editor.
Top Free Google Sheets Templates and Financial Statements to help you manage your business financials, monitor performance, and make informed decisions.
READ MORE4. Add Subtotals
Fortunately, Google Sheets makes it easy to add subtotals to your waterfall chart. For example, imagine you want to add bars with the quarterly subtotals to provide more context. First, let’s turn the first value into a subtotal.
From the ‘Customize’ tab in the chart editor, click ‘Series’ to expand and scroll down to the bottom of the section. Tick the checkbox ‘Use first value as subtotal’. As you can see below, the initial value is now the same color as the final one.
To add more subtotals, click on the button shown in the previous screenshot. You can add a custom label and select where you want the subtotal to appear: in this case, I want the value for Q1 to appear after March.
Repeat the previous step until all quarterly subtotals are shown.
5. Customize Chart
Now that you have your waterfall chart, you can customize it to your liking. You can do this by right-clicking on the chart or any of its elements, as well as directly from the ‘Customize’ tab.
For example, you can choose whether to show the connector lines and how to format them.
You can also add and customize data labels under ‘Series’, though that works best with charts with fewer bars and components.
To delete the y-axis title, click on it and press ‘Delete’ on your keyboard. Double-click on the chart title to rename it.
When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.
READ MOREHow to Create a Stacked Waterfall Chart in Google Sheets?
In this example, you will learn to create a stacked waterfall chart. In order to use stacking, you’ll need at least two categories. For example, revenue flows for two different teams.
- 1. In Google Sheets, set up your table like in the previous example. However, instead of two columns, there will be three in total. The first column contains the labels, while the second and third contain the values for two different teams: northteam and southteam.
- 2. Select your table and go to Insert > Chart.
- 3. Check the chart editor to ensure that the chart type is ‘Waterfall chart’ and ‘Stacked’ is selected below it.
- 4. In the chart editor, click on the ‘Customize’ tab and then click ‘Series’ to expand. Scroll down the options until you see the checkbox labeled “Use first value as subtotal”. Check the box.
- 5. Click on the legend to access the options in the chart editor. You can remove it or change its position. Let’s move the legend to the top, so the divisions in the x-axis are clearer.
- 6. Finally, click on the chart title to edit it.
Conclusion
As you have seen, waterfall charts can be very useful in the right circumstances. If you want to illustrate how increases and decreases affect the net change in a variable, the waterfall chart does it very effectively. These types of charts are popular within finance and HR, but not everybody is familiar with them. This is partly because interpreting these charts takes some getting used to: they have similarities to bar charts, but they also have some clear differences.
You now know what a waterfall chart is, what you can use it for, and how to read and interpret them. You have step-by-step instructions on creating a sequential waterfall chart, including how to set up the data, add subtotals, and customize its appearance. Additionally, you also know how to create a stacked waterfall chart so that you can visualize and compare contributions to net change from two different sources.
To learn more about different types of charts and visualizations in Google Sheets, check out these guides on: