Breadcrumb

How to create a waterfall chart in Excel

Written by

The GoCardless content team comprises a group of subject-matter experts in multiple fields from across GoCardless. The authors and reviewers work in the sales, marketing, legal, and finance departments. All have in-depth knowledge and experience in various aspects of payment scheme technology and the operating rules applicable to each. The team holds expertise in the well-established payment schemes such as UK Direct Debit, the European SEPA scheme, and the US ACH scheme, as well as in schemes operating in Scandinavia, Australia, and New Zealand. See full bio

Last editedNov 20203 min read

Also known as a bridge graph, waterfall graph, bridge chart, or cascade chart, a waterfall chart offers a visually appealing way to present data. Keep reading to learn how to read a waterfall chart and create your own chart using Excel.

What is a waterfall chart?

The purpose of a waterfall chart is to show positive and negative value changes over a specific time period. Starting and final values are clearly displayed as columns on the chart, with floating steps representing the negative and positive adjustments along the way. You can opt to connect these lines between columns for a bridge effect, or leave the columns floating.

By following the steps in the waterfall, you can see how the value changes over time to achieve a final result. A stacked waterfall chart takes the viewer through a user-friendly journey, with a simple, clean layout.

What is a waterfall chart used for?

From comparing product earnings to visualising financial statements, there are many scenarios where you might use a waterfall chart. It can be used to highlight budget changes throughout the duration of a project, analyse sales over time, or show the fluctuations in operating costs throughout the accounting period. Businesses use stacked waterfall charts to evaluate profits, track inventory, and show employee performance.

Usually, waterfall charts track some form of profit-driven performance over time, making them popular with finance departments. Yet they can be used in a variety of industries, from education to law.

How to read a waterfall chart

To learn how to read a waterfall chart, it’s helpful to break down its various components. Each chart will look slightly different depending on the type of data presented. Here are the main components:

Columns: A waterfall chart’s defining feature is its presentation of floating columns, which give a visual aid to the value’s positive and negative changes over time. Read the chart from left to right to track the journey.
Bridging lines: Some waterfall charts will include connecting lines called datum, which track the journey from one column to the next.
Crossover values: Values might move around the X-axis of your waterfall chart depending on what you’re charting. Profit and loss charts would be one example, showing both positive and negative values with floating columns above and below the axis.
Colour coding: Colour coding different columns makes the waterfall chart easier to read.

How to create a waterfall chart in Excel

Microsoft added a waterfall chart option to Excel in 2016, automatically creating this layout for users. If you’re using an earlier version of Excel, you can use the following steps to figure out how to create a waterfall chart in Excel.

Step 1: Create a data table

Gathering data is an important first step when creating a waterfall chart. For the purpose of this example, we’ll use net sales figures for the current year. Create a data table showing sales amounts from month to month. Insert additional columns into your Excel table representing column movement. The base will represent the starting point, the negative figures will go into the fall column, and the positive numbers will go into a rise column.

Insert formulas into the first cells in each column, copying them down through the chart with Excel’s fill tool.

Step 2: Build a column chart

With all of your data filled into the table, you can then use it to create a waterfall chart in Excel. Select the data you want to highlight, including row and column headers. Go to the ‘Insert’ tab, click on ‘Column Charts’, and then select the ‘Stacked Chart’ option.

Step 3: Convert the stacked chart to a waterfall chart

Now you can convert the stacked chart to a waterfall chart format. To do this, you’ll need to hide the ‘Base’ series from view. Click on the ‘Base’ series to select, before right-clicking and choosing ‘Format Data Series.’ You’ll see the ‘Fill & Line’ icon pop up. Select ‘No fill’ and ‘No line’ from the options presented.

Step 4: Format the chart

Finally, you can personalise your waterfall chart by colour-coding the columns using the ‘Fill & Line’ tool once more. Click on the colour dropdown option to select a colour. You can use a separate colour for the rise column and the fall column to make positive figures stand out from negative.

Right-click on each column to select ‘Add Data Labels.’ This allows you to label each column with the information you want to present.

Of course, there are more ways to customise and create a waterfall chart in Excel, so feel free to play around with this basic template. For users of Excel 2016 and above, simply choose the waterfall chart option and customise it from there.

We can help

GoCardless helps you automate payment collection, cutting down on the amount of admin your team needs to deal with when chasing invoices. Find out how GoCardless can help you with ad hoc payments or recurring payments.