Don’t forget to share this post

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.

How to Create a Waterfall Chart in Google Sheets Revenue Waterfall Chart
How to Create a Waterfall Chart in Google Sheets - Revenue Waterfall Chart

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

How to Create a Waterfall Chart in Google Sheets Open Google Sheets
How to Create a Waterfall Chart in Google Sheets - 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.

How to Create a Waterfall Chart in Google Sheets Set Up Table
How to Create a Waterfall Chart in Google Sheets - Set Up Table

3. Insert Chart

Select the table created in the previous step and go to Insert > Chart.

How to Create a Waterfall Chart in Google Sheets Insert Chart
How to Create a Waterfall Chart in Google Sheets - Insert Chart

If it isn’t selected automatically, change the chart type to waterfall chart using the dropdown menu in the editor.

How to Create a Waterfall Chart in Google Sheets Waterfall Chart Type
How to Create a Waterfall Chart in Google Sheets - Waterfall Chart Type
Top Free Google Sheets Templates and Financial Models
Top Free Google Sheets Templates for 2023

Top Free Google Sheets Templates and Financial Statements to help you manage your business financials, monitor performance, and make informed decisions.

READ MORE

4. 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.

How to Create a Waterfall Chart in Google Sheets Initial Subtotal
How to Create a Waterfall Chart in Google Sheets - Initial Subtotal

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.

How to Create a Waterfall Chart in Google Sheets Q1 Subtotal
How to Create a Waterfall Chart in Google Sheets - Q1 Subtotal

Repeat the previous step until all quarterly subtotals are shown.

How to Create a Waterfall Chart in Google Sheets All Quarterly Subtotals
How to Create a Waterfall Chart in Google Sheets - All Quarterly Subtotals

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.

How to Create a Waterfall Chart in Google Sheets Connector Lines
How to Create a Waterfall Chart in Google Sheets - Connector Lines

You can also add and customize data labels under ‘Series’, though that works best with charts with fewer bars and components.

How to Create a Waterfall Chart in Google Sheets Data Labels
How to Create a Waterfall Chart in Google Sheets - Data Labels

To delete the y-axis title, click on it and press ‘Delete’ on your keyboard. Double-click on the chart title to rename it.

How to Create a Waterfall Chart in Google Sheets Edit Titles
How to Create a Waterfall Chart in Google Sheets - Edit Titles
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

How 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. 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.
How to Create a Waterfall Chart in Google Sheets Open Google Sheets 2
How to Create a Waterfall Chart in Google Sheets - Open Google Sheets
  1. 2. Select your table and go to Insert > Chart.
How to Create a Waterfall Chart in Google Sheets Insert Chart 2
How to Create a Waterfall Chart in Google Sheets - Insert Chart
  1. 3. Check the chart editor to ensure that the chart type is ‘Waterfall chart’ and ‘Stacked’ is selected below it.
How to Create a Waterfall Chart in Google Sheets Stacked Waterfall Chart
How to Create a Waterfall Chart in Google Sheets - Stacked Waterfall Chart
  1. 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.
How to Create a Waterfall Chart in Google Sheets First Value Subtotal
How to Create a Waterfall Chart in Google Sheets - First Value Subtotal
  1. 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.
How to Create a Waterfall Chart in Google Sheets Move Legend
How to Create a Waterfall Chart in Google Sheets - Move Legend
  1. 6. Finally, click on the chart title to edit it.
How to Create a Waterfall Chart in Google Sheets Edit Title
How to Create a Waterfall Chart in Google Sheets - Edit Title

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:

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jan 30 2023, Updated Jun 26 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets