How to Create a Waterfall Chart in Google Sheets

Waterfall charts are often used in financing, but inGoogle Sheetsis less well-known among the various chart types in .Although it is similar to a column chart, a waterfall chart is used for a completely different purpose.

Charts make sense when used properly.You can't use any chart for any presentation, and waterfall charts are no exception here.So, before you can go any further with it, you must first know what a waterfall chart is and where it is used.Read on to learn how and when to use waterfall charts in Google Sheets.

What is a Waterfall Chart in Google Sheets?

A waterfall chart is a chart that represents data as rectangles on a grid.The size of the rectangles is proportional to the value they represent, so longer rectangles indicate larger values.So far, it's all similar to column charts in Google Sheets, but there are major differences between the two chart types.

The waterfall chart starts with an initial value, positioned at the beginning of the grid.The changes (addition and subtraction) that the initial value undergoes are represented by more rectangles.By default, red and blue rectangles represent negative and positive values, respectively.

Once the initial value has gone through all the amount changes, the final value is displayed at the end of the grid.The final value is also known as the subtotal.

Waterfall charts allow you to see how much a particular change affects an initial value and better visualize the development of value across multiple events.

How to Create a Waterfall Chart in Google Sheets

Like Excel, waterfall charts are built into the defaultGoogle Sheetsmiddle.What's more, you need to do less than creating a waterfall chart in Excel.Google Sheets automatically changes the chart type to waterfall by looking at your data.

In the following example, we have the approximate cost and profit experienced by a startup project in its first year.Our goal is to create a waterfall chart that visualizes this data table.

1. Select your data table.In our case cells A1 to B7.

2. Go to the Insert menu and select Chart.Google Sheets will most likely create a waterfall chart for your data.If your chart is not a waterfall chart, you can change the chart type yourself.

a. Double-click the chart.This will bring up the graph editor on the right.

b. On the Settings tab, change Chart Type to Waterfall Chart.

Now you have a waterfall chart, but the chart is not ready.If you take a closer look, you will see that the subtotal and final balance are set to two different values.This way, the final balance behaves as an addend rather than a final value.Let's solve this problem.

1. Double-click the graph to bring up the graph editor.

2. Go to the "Custom" tab in the diagram editor.

3. Collapse the "Series" category.

4. Scroll down and uncheck Add subtotal after last value of series.This will remove the subtotal series.It's time to make the final balance a subtotal.

5. Click Add New Subtotal.

6. Under Column Index, select the series you want to subtotal.In this example, we want to choose the final balance.

7. Change the Subtotal Type to Replace.This way, the new subtotal will replace the previous one, and you won't have two subtotals on your chart.

Now you have a proper waterfall chart.You can look at the red and blue rectangles to see what happened to the initial values.The upper side of the blue rectangle, and the lower side of the red rectangle represent the value of that point on the x-axis.

You can also choose to mark the first value as another subtotal.This way it will be represented as the first value in grey, not the addition of zeros in blue.

Visualize your data with waterfall charts

Some charts like waterfall charts are rarely used, not because we don't need them, but because we don't know when we need them.Waterfall charts are a great tool for illustrating the changes a value has undergone, and the resulting subtotals resulting from those changes.

While waterfall charts are often used in finance, that doesn't mean you can't get creative and use them for other scenarios.By learning about the different chart types, you can further improve your use of charts in Google Sheets.