Making a Waterfall Chart in Excel: A Step-by-Step Tutorial

A waterfall chart is one of the lesser-known types of Microsoft Excel charts. However, it is a popular choice in the world of finance.

In this article, we’ve got all you need to know about this special chart type. This includes when to use it, why you might need it, and how to create your own. Read on!

What is a waterfall chart?

A waterfall chart (also referred to as a bridge chart or a cascade chart) is used to visually demonstrate how a starting value of something becomes a final value through a series of intermediate additions and subtractions.

It can be used to show categorical or sequential data, and the additions and subtractions can be category-based or time-based.

Whole columns usually represent the starting and final values, while color-coded floating blocks represent intermediate values.

How to build waterfall charts in earlier versions of Microsoft Excel

There’s no built-in waterfall chart type in Excel 2016 (and later versions), meaning you have to create one. Below are steps to help you build a waterfall chart.

Step 1: Create a data table

The first step is reorganizing the data. Introduce new columns between two original columns, then name them base, rise, and fall.

• Base column: The starting point for the rise and fall series in the graph.
• Up\ Rise column: Positive changes in the value.
• Down\Fall column: Negative changes in the value

You can also create a data table with seven columns: period, cash flow, cumulative, start & end, before, after, and data label positions.

Step 2: Insert formulas

After reorganizing the data, you need to enter formulas in the corresponding columns or first cells and drag them down to the whole column using the fill tool or handle. You can also copy and paste the formula into corresponding adjacent cells.

Step 3: Make a stacked column chart

Select the data range without the amount column and click Insert > Bar Chart or Insert Column > Stacked Column.

Step 4: Transform into a bridge chart

Make the invisible column (base series) have no fill color, and format the ups and downs in your favorite color.

To do this:

• Select the base series, then right-click and select the ‘Format Data Series option. Under ‘Fill,’ select No Fill for the border, and select ‘No line.’
• Format the positive changes series\columns to green and negative changes columns to red.
• Color code the Start and End columns with the same shade.
• Choose one of the columns and right-click to format (CTRL+1).

Step 5: Make the chart informative and attractive

This step of polishing the look and feel of your bridge chart is optional. For instance, you can:

• Add a chart title
• Add data labels
• Change the width of the columns
• Add colors

How to make a waterfall chart in later versions of Microsoft Excel

Microsoft Excel 2016 (and later versions) has a waterfall chart as a built-in feature, so it only takes seconds to build one from your data with the below steps:

• Step 1: Open Excel and click the file you want to graph.
• Step 2: Click the insert menu and select the waterfall chart icon.
• Step 3: Drag the waterfall chart to the desired location and customize it to your liking.

When to use a waterfall graph

A waterfall chart shows how an initial value is affected by a series of negative or positive values. This makes them frequently relied upon in specific industries.

• An organization’s potential gain or substantial loss can be interpreted in a bridge graph.
• Retail businesses often use these charts to visualize sales or inventory over time.
• Managers use this financial model to demonstrate budget change and the critical factors or several factors which influence the value of a product over a period of time.

Waterfall Chart in Excel

We hope by now you know how to make a waterfall chart in Excel and when to use it. Used correctly, a waterfall graph is an impressive way to display data changes. It also cultivates a workplace that values time management, as it is similar to a trading strategy.