A waterfall graph, also called a cascade or bridge chart, is an important visualization tool. You can use it to create visuals that show how negative or positive trends affect totals. In Microsoft Excel, you can be able to create and customize your waterfall chart with much ease. Here are some of the ways that can help you to create better Excel waterfall charts.
What is a waterfall chart?
A waterfall chart is a visual graph that indicates how a value has been affected by negative and positive changes over time. It shows the beginning and end points, which are connected by a bridge of bars known as bridges or floating bars.
These floating bars visualize how the value rises or falls until its endpoint. Additionally, these floating bars are colored for users to easily identify a positive or negative change.
It’s known as a waterfall chart due to the floating bars, which give it a waterfall look. You can also create a stacked waterfall chart by subdividing the floating bars.
Uses of a waterfall chart
The main purpose of a waterfall chart is to showcase how a value has changed over time. This can be beneficial for the following applications.
- Gap analysis
- Personal cash flow visualization
- Headcount change visualization
- To analyze sales and profits
- Change analysis
To get started
Once you have your data that would fit perfectly into a waterfall chart, the first step is setting up the data. This example will illustrate product earnings in one week. It will show daily losses and gains of the product in the market.
1. The first task is to enter the starting balance at the top of the table of weekly earnings.
2. Select cell A2 and insert the starting balance in the corresponding cell B2.
3. Below the table, insert the Final Balance. Enter the Final balance in cell A10 and the corresponding amount in cell B10. In cell B10, use the formula =SUM (B2:B9).
Note: it is optional to use the SUM function to create the Final Balance. You can just add the starting balance to the earnings and get the Final Balance.
Creating the waterfall graph
Once you have set up your data, you can now create the waterfall graph.
1. Select the data that you want to create a waterfall graph in excel in an open workbook. In this example, select cells A2:B10.
2. Click on the Insert tab of the main menu ribbon.
3. In the Charts group, click on the Insert Waterfall, Funnell, Stock, surface, or Radar chart option.
4. On the pop-up screen Choose Waterfall.
5. There you go; you have your Waterfall graph in excel.
Editing the waterfall graph
Once you create your waterfall graph, you will realize that the waterfall graph has no title besides its default color. However, you can be able to customize these colors to fit your needs.
Editing the title
1. Select the Chart Title
2. Select the delete key and delete the existing Chart Title
3. You can now type the new text for the chart title.
Editing the Final balance amount
You can be able to edit the Final balance amount in your chart. This will be able to represent the total amount clearly and make it distinct. Follow these steps to make it distinct:
1. First, you will need to select the Final balance Column, which will select the entire data series.
2. Click the Final balance column again to select just that column.
3. Then select Set as Total
4. The column will change the fill color, thus showing that it is the total amount.
Changing the Chart Style
You can be able to change the default chart style, which is the numbers that are shown on each column. You can change them to a built-in style.
1. First, click the chart.
2. Click on the Paintbrush button (Chart Style) on the right side of the chart.
3. You can now click on the chart style that you want and apply it to your chart.
How to create a waterfall chart on the Excel Mobile App (Android & iOS)
The excel mobile app offers a simple way to create and edit charts on the go. You can install it on your Android or iOS and Sync it with your Office 365 account. If you don’t have the time to open your computer, you can just open the app, enter some data, and the changes will reflect in your online Microsoft 365 Excel.
1. Download the Excel app from the Play Store or Appstore.
2. Create a new spreadsheet by clicking the + at the top corner.
3. Insert the data. Let’s use the table below as an example
4. Highlight all the data, including the labels
5. Click on the up arrow in the bottom right corner, then click on home
6. Click on insert >>Chart, then scroll down to the waterfall chart and select it. This action will populate a waterfall chart automatically. For smaller screens for Android and iOS, you can rotate the screen for wide viewing.
That’s how to create a Waterfall chart using Android or iOS devices. You can follow the same procedure when creating any other type of chart; instead of picking a waterfall chart, you can pick you can choose your preferred chart.
How to create a waterfall chart using Google Sheets
Google sheets are the best Microsoft Excel alternative. If you are having trouble utilizing Excel, then you can create a waterfall using Google Sheets. Follow these easy steps.
1. Launch Google sheets using the following link https://docs.google.com/spreadsheets/u/0/
2. Enter your data as follows
3. Highlight all the data, including the labels on the first column
4. Click on the three dots located at the top right corner of Google Sheets.
5. Select the insert Chart Icon
6. Select the Waterfall chart using the chart editor
That’s how to create a waterfall graph in Excel. Google Sheets features an AI that automatically preselects the type of chart based on your data structure. But you can change the type of chart on the chart editor anytime if it doesn’t match your criteria.
Google Sheets automatically calculates the subtotal of your values and displays it on the far right using a gray bar. In addition, it displays the positive values as blue floating bars and the negative values as red floating bars.
How to change the waterfall chart Style
The default aesthetics may not match your brand or personal preferences. For this reason, you may want to customize the chart Style. Google Sheets makes it easy to customize graphs using the chart editor.
Click on the chart editor, then navigate to the customize section.
Here you can change the background color, line dash type, line color, fill color, line opacity, and line thickness.
How to hide or show connector lines
Sometimes you just need a graph without the connector lines. The good news is that you can remove them on Google Sheets. Look at the image below to see what a connector line looks like.
To hide the connector lines, navigate to the chart editor>>Customize, then untick the option written with major gridlines.
The chart will appear as shown in the screenshot below.
How to create a stacked Waterfall Chart in Google Sheets
A stacked waterfall chart is a data visualization graph that showcases how one entity’s performance has improved or degraded from one state to another. Each stack in the waterfall chart represents the value changes. The height of a stacked column is used to indicate the cumulative value.
Stacked charts are popularly used in businesses to indicate how values transform from one state to another through intermediate changes. The firsts and last bars show the beginning and the ending values, respectively. The beginning and the ending values are then connected by a series of floating bars that indicate the rise and fall of the values.
In this section, I will guide you on how to create a stacked waterfall chart in Google Sheets.
Let’s say you have two branches of your company and wish to compare their changes in revenue over time, as shown in the image below. A stacked waterfall chart will be the best visualization option.
1. Highlight all the values
2. Click on the three dots in the top right corner
3. Select the graph Icon to insert a chart
4. Choose a waterfall chart if not pre-determined by google automatically
5. Next, select stacked under setup>>Stacking
6. The Stacked waterfall chart will populate automatically
When you view the graph, you will see both branches of the companies staked in the floating columns. Use the customize feature to change the aesthetics of your waterfall graph.
Final Thoughts
We have seen how to create a waterfall chart on Excel, Android, iOS, and Google Sheets. We have also seen how to create a stacked Waterfall chart and how to customize it.