Waterfall charts, also known as McKinsey Charts, are becoming an increasingly popular visualization tool. These charts have several names — sometimes they are referred to as Cascade charts or Bridge charts. Some lesser common names include Flying brick charts and Mario charts.
Waterfall charts provide a great way to visualize changes in a quantity over time. They are a fairly new addition to the Google Sheets charts collection. Before that, Google Sheets users had to create them manually by customizing stacked column charts. But users can now make waterfall charts on Google Sheets quickly and easily with just a few clicks.
In this tutorial, we will show you how to create a McKinsey waterfall chart in Google Sheets, as well as how to format and customize it.
Table of Contents
What is a Waterfall Chart?
A Waterfall chart is a visualization tool that helps demonstrate how a value is affected by a series of positive and negative changes. The chart consists of bars that show the beginning and ending values of a quantity, connected to each other using floating bars (or bridges).
The bridges show how the starting value rises and falls until it reaches the ending value. The bars in a waterfall chart are color-coded so that you can quickly see when a change is positive or negative.
Following is an example of a waterfall chart, showing the quarterly change in revenue over a year:
Notice that the starting and ending value columns start on the horizontal axis (or x-axis), while the intermediate columns are floating.
These floating bars are what give the chart its ‘waterfall look’. You can also include additional subtotal columns in the chart or divide the columns to create a Stacked Waterfall Chart.
What Are Waterfall Charts Used For?
McKinsey Waterfall charts are mostly used to visualize how a value changes over time. As such, these are commonly used in the following applications:
- To visualize personal cash flow
- To visualize change in employee headcounts
- To analyze sales and profit
- Gap analysis
- Change analysis
How to Make a Waterfall Chart in Google Sheets
Let us now see how to create a waterfall chart in Google Sheets. To demonstrate this, we are going to use hypothetical data on a company’s quarterly revenue:
In the above dataset, the positive values indicate money earned (or revenue gained), while negative values indicate money lost.
Notice that we put the quarter numbers (or the text) in the first column, and the values in the second. Moreover, we included a row for the starting cash value, but we did not need to add a row for the ending value.
This is because Google Sheets calculates the ending value on its own based on the increments and decrements presented in our data (just like I discussed in my guide to compound interest).
We can create a waterfall chart to summarize the above data, as follows:
- Select the range containing the data you want to visualize in your Waterfall Chart. In our case, it is the range A1:B6.
- From the Insert menu, select Chart. Alternatively, you can click on the Chart icon from the toolbar.
- This will display a chart on your sheet, and you should also see a Chart editor sidebar to the right of the browser window.
- Typically, Google Sheets tries to understand your data and creates a chart that it finds most suitable to your data. Ideally, it should display a Waterfall chart, in which case you can stop at this step. However, if you see some other chart, then you can change it, as shown in step 5.
- To convert your chart to a waterfall chart, select the Setup tab from the Chart editor sidebar and click on the dropdown menu under Chart type.
- From the chart options displayed, select the “Waterfall chart”. It should be visible under either the “Suggested” or “Other” category.
You should now have a waterfall chart displayed in your sheet, as shown below:
Notice that Google Sheets has calculated the subtotal (ending value) for you and plotted it as the gray-colored column in the end. The positive values (rising values) are shown as blue columns, while the negative values (falling values) are shown as red columns.
If you hover over any of the columns, you will see the values corresponding to each.
All that is left to do now is customize the chart to suit your requirements.
Customizing the Google Sheets Waterfall Chart
If you want to spruce up your chart, Google Sheets offers a range of customization options. Let us look at some of these.
Changing the Chart Title
Your chart title is the first thing that people see in your chart. It tells at a glance what the chart is all about. If you want to change the chart title of your Google Sheets Waterfall Chart, double-click on the chart to open the Chart editor sidebar. Next, select the Customize tab and click on the ‘Chart & Axis titles’ category.
You can now use the options shown in this category to remove, change or format your chart title. In our example, here are the changes we made to the chart title:
- We changed the chart title text to “Changes in Quarterly Revenue over the Past Year”.
- We changed the title font to Tahoma
- We set the title font size to 18
- We set the title text color to “Dark Yellow 2”
Changing the Chart Style
If you want to change the chart aesthetics and style it according to your requirement, you will find all your chart styling options under the ‘Chart style’ category of the Customize tab.
This category lets you set your chart’s background color, overall font, and chart border. It also includes options to hide / show / adjust your connector lines.
Hiding / Showing Connector Lines
The connector lines are the lines that connect or form bridges between the individual columns of your waterfall chart.
By default, Google Sheets displays the connector lines in the form of gray dashed lines. However, you can choose to adjust them as per your liking.
For example, if you want to hide all connectors, uncheck the box next to “Show connector lines”, as shown below.
To make them reappear, you can check this box again.
You can also set the color, thickness, and line dash type of the connectors if you need to.
Adding Data Labels / Setting Label Positions
Data labels in your chart provide added information. For example, if you want to display the values that each of the columns in your waterfall chart represent, you can add data labels to them.
You can find options for data labels under the Series category of the Customize tab. To add data labels to your waterfall chart columns, select the Series menu, scroll down and check the box next to ‘Data labels’.
You should now see more options to format your data labels. For example, you can set the font style, size, and color. You can also set the format of the number displayed in the data labels and set how you want to position your data labels for each column.
In our example, let us set the data labels to be displayed at the inside base of each column as shown below:
Here’s what our chart looks like at this point:
Setting the Column Colors
You will also find options to change the colors of the columns according to your liking. For example, let’s say we want the positive value columns to be green and the negative value columns to be orange. You will find options for this under the Series category of the Customize tab.
To change the color of positive columns, you can double-click on any one of the positive columns.
This opens the Series menu. Click on the Fill color dropdown (you will find it under the input box for the ‘Positive label’) and select the color you want all the positive columns of your chart to have.
Similarly, to change the color of the negative columns, do the same for the Fill color dropdown under the ‘Negative label’ input box.
Under the ‘Subtotal label’ input box, you will find the Fill color option to change the color of the Subtotal bar.
In all three categories, you also have options to change the opacity, line color, line thickness, and other settings for each type of chart column.
Showing / Hiding the Subtotal as the Last Column
Google Sheets displays the final Subtotal as the last column by default in the waterfall chart. However, if you prefer not to display it, you can remove it by simply unchecking the box next to ‘Add subtotal after last value in series’ in the Series category.
Other Customization Options
There are a number of other customization options that you will find under the Customize tab of the Chart editor. Here’s a quick description of some of these.
The Legend Category
This category lets you provide the settings and formatting for your waterfall chart’s legend. You can also remove the legend by clicking on the dropdown under Position (in the Legend category) and selecting ‘None’.
The Horizontal and Vertical Axes Category
This category lets you change axis-related settings, like the font type, color, size, and type for your axis labels, how much you want the axis labels scaled, whether you want the axis line hidden or displayed, etc.
The Gridlines and Ticks Category
This category lets you set up the major and minor gridlines of your waterfall chart. You can choose to either show or hide major and/or minor gridlines, change their colors, spacing, and more.
Including Additional Subtotals
Google Sheets also lets you include additional subtotals in your waterfall chart. For example, say you also want to display the total revenue in the middle of the year (at the end of Quarter 2). The great thing is, you don’t need to calculate it or include it in your data for it to show on your chart.
The waterfall chart automatically calculates the subtotal after you specify the position you want the new subtotal to appear.
So, to include the subtotal after Quarter 2, follows the steps below:
- Click on the ‘Add new subtotal’ button under the Series category (of the Customize tab).
- Type the label you want to give to this new subtotal. In our case, let us label it “Mid-year total Revenue”.
- Let the Subtotal type remain as ‘After’
- Select Q2 from the Column index dropdown list (since we want to insert the new subtotal after the Q2 column).
You should now see a new subtotal column after the Q2 column, that displays the total revenue computed after the second quarter.
Note: In step 3, you could also select the ‘Replacing’ subtotal type if you want to replace a column with a subtotal.
FAQs
Can you do a waterfall chart in Google Sheets?
Yes, since December 2017, Google Sheets has introduced the waterfall chart as part of its charts collection.
How do I remove subtotals from a waterfall chart in Google Sheets?
To remove subtotals from the waterfall chart, follow the steps shown below:
- Select the chart
- Click on the ellipsis button (the three dots) on the top right corner of the chart
- Select Edit chart from the menu that appears
- This opens the Chart editor sidebar on the right side of your browser window.
- Select the Customize tab
- Click on the ‘Series’ menu
- Scroll down and uncheck the box next to ‘Add subtotal after last value in series’.
Your subtotal should now be removed from your waterfall chart.
How do you add data labels to a waterfall chart?
To add data labels to the waterfall chart, follow the steps shown below:
- Select the chart
- Click on the ellipsis button (the three dots) on the top right corner of the chart
- Select Edit chart from the menu that appears
- This opens the Chart editor sidebar on the right side of your browser window.
- Select the Customize tab
- Click on the ‘Series’ menu
- Scroll down and check the box next to ‘Data labels’.
- You will now see more options to format your data labels. You can use these to further format the data labels according to your requirement
How do I create a stacked waterfall chart in Google Sheets?
Google Sheets also lets you create a stacked waterfall chart. Stacked waterfall charts let you show contributions of multiple values at each category by stacking them in each of the floating columns.
For example, say you have the revenue changes over a year for two branches of a company – Branch A and Branch B, as shown below:
In such cases, you can use a Stacked Waterfall Chart to display how the total revenue changed over the year.
Creating a stacked waterfall chart in Google Sheets is as easy as creating a regular (or Sequential) waterfall chart. Here are the steps:
- Select your data range (A1:C6 in this case)
- Navigate to Insert->Chart from the main menu
- This should display a waterfall chart in your worksheet. If not, simply select the waterfall chart from the dropdown under Chart type (in the Chart editor sidebar)
- In the Setup tab, click on the dropdown list under ‘Stacking’ and select ‘Stacked’.
- You should now see your waterfall chart converted to a Stacked Waterfall Chart, displaying the values for both Branch A and B in each floating columns, including the starting column.
- You can now go ahead and customize the chart according to your liking by playing with the different options under the Customize tab of the Chart editor.
Conclusion
This tutorial was about Waterfall Charts in Google Sheets. We discussed what they are, how they work, and how they can be created to provide excellent visualizations for your data. We hope our content was helpful and easy to follow.
