Charts make sense when used properly. You can’t use any chart for any demonstration, and the waterfall chart is no exception here. So, before you move on to using it, you must first know what exactly a waterfall chart is and where it’s used. Read on to find out 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 rectangles are proportional to the values they represent in size, so a longer rectangle indicates a greater value. So far, this is all similar to the column chart in Google Sheets, but there are major differences between the two chart types.
Waterfall charts start with an initial value positioned at the start of the grid. The changes that the initial value undergoes (addition and subtractions) are represented with more rectangles. By default, red and blue rectangles represent negative and positive values, respectively.
Once the initial value has undergone all the changes in its amount, the final value is shown at the end of the grid. The final value is also known as the subtotal.
Waterfall charts allow you to see how much a specific change has affected the initial value, and better visualize the development of the value throughout multiple events.
How to Create a Waterfall Chart in Google Sheets
Like Excel, waterfall charts are built into Google Sheets by default. What’s more, you need to do even less compared to creating a waterfall chart in Excel. Google Sheets automatically changes the chart type to a waterfall chart by taking a look at your data.
In this example, we have the rough costs and profits a startup project has undergone in its first year. The goal is to create a waterfall chart to visualize this data table.
Select your data table. That’s cells A1 to B7 in our example. Go to the Insert menu and then 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. Double-click the chart. This will bring up the Chart editor on the right. In the Setup tab, change the Chart type to Waterfall chart.
Now you have a waterfall chart, but this chart isn’t ready yet. If you take a closer look, you’ll realize that the subtotal and the final balance are set as two different values. This way, the final balance is represented as an addition, rather than the final value. Let’s fix this.
Double-click the chart. This will bring up the Chart editor on the right. In the Setup tab, change the Chart type to Waterfall chart.
Double-click the chart. This will bring up the Chart editor on the right. In the Setup tab, change the Chart type to Waterfall chart.
Double-click the chart to bring up the Chart editor. Go to the Customize tab in the Chart editor. Collapse the Series category. Scroll down and uncheck Add subtotal after last value in series. This will remove the subtotal series. Now it’s time to make the final balance the subtotal. Click on Add new subtotal. Under Column index select the series that you want to represent as the subtotal. In this example, we’re going to select Final Balance. Change the Subtotal type to Replacing. 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 has happened to the initial value. The top side of the blue rectangles, and the bottom side of the red rectangles represent the value at 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 gray, and not an addition to zero in blue.
Visualize Your Data With a Waterfall Chart
Some charts like the waterfall chart are rarely used, not because we don’t need them, but because we don’t know when we need them. The waterfall chart is an excellent tool to illustrate the changes a value has undergone, and the final subtotal that the changes have led to.
Though the waterfall chart is often utilized in finance, that doesn’t mean you can’t get creative and use it in other scenarios. You can further improve your usage of charts in Google Sheets by learning about the different chart types.
