Make a Waterfall Chart in Excel to Explain a Change

A waterfall chart in Excel explains how one number became another. It starts with a bar for the opening value, adds a floating bar for each increase or decrease, and ends with a bar for the final value. You make one in Excel 2016 or later by listing the steps in order, inserting a Waterfall chart and setting the first and last bars as totals. This guide is for analysts, finance staff and anyone who has to answer "why did this number change?" in a report or a job test.

What is a waterfall chart?#

A waterfall chart (also called a bridge chart) shows a running total. Each bar starts where the previous one ended. Increases go up and are usually green, decreases go down and are usually red, and the totals stand on the axis.

It is the best chart for explaining a change between two numbers, for example:

• Last year's profit to this year's profit.
• Budget to actual cost, line by line.
• Opening cash to closing cash in a month.

For comparing separate items rather than a running total, a normal bar chart is clearer. The Excel chart basics guide helps you choose.

How to set up the data#

A waterfall needs two columns: a label and an amount, in the order of the story. This example explains how profit grew from 120,000 to 126,000:

Three rules matter:

1. Decreases are negative numbers. Type -31,000, not 31,000. The chart uses the sign to decide the direction.
2. Calculate the end value. In B7, type =B2+SUM(B3:B6). It returns 126,000, and it updates if any step changes, so the last bar always matches. The SUM function guide covers the basics, and the SUM lesson lets you practise it.
3. Don't sort the data. The order is the story. Put the biggest drivers first or follow a logical order, like a profit and loss statement.

To show the change as a percentage too, use =(B7-B2)/B2 and format it as a percentage: 5%. The percent change lesson explains this formula.

How to insert a waterfall chart in Excel#

Once the data is ready, the chart takes a few clicks in Excel 2016 or later, on Windows or Mac:

1. Select A1:B7.
2. Go to Insert and click the Insert Waterfall, Funnel, Stock, Surface or Radar Chart button in the Charts group.
3. Choose Waterfall.

You can also use Insert > Recommended Charts > All Charts > Waterfall.

At first, every bar floats, including the two profits. Excel treats 126,000 as one more increase on top of the others, so the last bar shoots far above the rest. The next step fixes that.

How to set the total bars#

Totals must start at zero, so you mark them yourself:

1. Click any bar once. All the bars are selected.
2. Click the This year profit bar again. Now only that bar is selected.
3. Right-click it and choose Set as Total. (Or open Format Data Point and tick Set as total under Series Options.)
4. Do the same for the Last year profit bar.

Now the first and last bars stand on the axis, and the steps float between them. Price and Volume go up, Costs and Exchange rates come down, and the chart ends exactly at 126,000.

How to format a waterfall chart#

A few small changes make the chart easy to read:

• Colors: click a legend entry (Increase, Decrease or Total) to select all bars of that type, then pick a fill color under Format > Shape Fill.
• Title: replace "Chart Title" with the message, for example "Profit up 6,000: price and volume beat higher costs".
• Labels: keep data labels on the bars and remove the gridlines, so readers see the numbers without scanning the axis.
• Gap width: right-click a bar, choose Format Data Series, and lower the gap width to make the bars wider.

How to build the steps from raw data with SUMIF#

Real data rarely arrives as five tidy steps. Usually you have a table of transactions, each with a category. Summarise it first, then chart the summary. If the transactions are on a sheet named Data, with categories in column B and amounts in column C:

=SUMIF(Data!B:B,A3,Data!C:C)

This adds every amount whose category matches the label in A3, such as Price. Fill it down for each step. Each category becomes one bar. The SUMIF function guide explains the arguments, and the pivot-style summary lesson practises turning a long list into a summary like this. A budget vs actual report or a profit and loss statement is a natural source for waterfall steps.

How to make a waterfall chart in Excel 2013 or earlier#

Older versions have no Waterfall type, but a stacked column chart with an invisible base gives the same look. Add helper columns next to the steps. With the running total in column C (C2 is =B2, C3 is =C2+B3, filled down to C6):

For the start and end rows, put the total in its own Total column and leave Base, Up and Down at 0. Then select the labels with the Base, Up, Down and Total columns, insert a Stacked Column chart, and set the Base series to No fill. The bars now float. This method assumes the running total stays above zero.

Common waterfall chart mistakes#

Most problems come from the data, not the chart:

1. Totals left floating. Set the start and end bars as totals.
2. Decreases typed as positive. They go up instead of down. Add the minus sign.
3. A typed end value. If it doesn't equal the start plus the steps, the chart doesn't close. Use SUM.
4. Too many steps. More than about eight bars gets hard to read. Group small items into "Other".

For two measures on different scales, such as revenue and margin %, a waterfall is the wrong tool; use a combo chart with a secondary axis.

Practise it#

A waterfall chart is only as good as the numbers behind it. Try the exercise on this page, then practise SUM in a live spreadsheet, practise percent change and practise turning raw data into a summary. Each formula is checked instantly, so your next chart starts from numbers you trust.