Waterfall Chart with Helper Columns

Excel Formulas › Charts

All versionsStacked bar

A waterfall shows how a starting value rises and falls to an ending value — profit bridges, cash-flow walks. Excel 2016+ has a built-in type; for older versions, a clever invisible base column floats each bar into place.

Quick formula: the trick is a hidden “base” that lifts each bar:
Base = running total before this step; Bar = the step amount
Stack an invisible base under each step so the visible bar starts where the previous one ended — the floating-brick look.

Functions used (tap for the full reference guide):

IFSUM

The example

Helper layout: base (hidden) + the up/down amounts.

ABC
1StepBaseChange
2Start0100
3+Sales10040
4−Costs110−30
5End0110

The formula

Build the invisible base, then stack:

Base = running total before the step Stack: Base (no fill) + Change (colored) // bars float to the right height

How it works

An invisible base floats each bar:

  1. 2016 and later: select the data and Insert → Waterfall — done. Mark totals with “Set as Total.”
  2. Older versions: add a base column = the running total before each step, and a change column for the step amount.
  3. Make a stacked column of base + change; set the base series to No Fill so only the change shows.
  4. Color increases and decreases differently with separate up/down series via IF helpers.

Split up and down: use two change columns — =IF(step>0, step, 0) for rises (green) and =IF(step<0, -step, 0) for falls (red) — so the bricks are color-coded. The native 2016 chart does this automatically.

Try it: interactive demo

Live demo

Start + steps (e.g. 40,-30,25).

Variations

Native (2016+)

Insert → Waterfall; mark totals.

Up column

Rises only:

=IF(step>0, step, 0)

Down column

Falls only:

=IF(step<0, -step, 0)

Pitfalls & errors

Base must be No Fill. Forgetting to hide the base series shows solid bricks from zero — the float effect disappears.

Base differs for ups vs downs. For a decrease, the base is the ending level (running total after the step); for an increase, the level before it.

Totals reset the base. Start and end (and subtotals) sit on a zero base — mark them as totals (native) or set base = 0 (manual).

Practice workbook

📊
Download the free Waterfall Chart with Helper Columns practice workbook
A waterfall helper layout (base + up/down columns) with a stacked chart, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I make a waterfall chart in Excel?
In Excel 2016+, select the data and Insert → Waterfall, then mark start/end as totals. In older versions, add an invisible 'base' column (the running total before each step) and stack the change on top with the base set to No Fill.
How do I color increases and decreases differently?
Split the change into two columns: =IF(step>0, step, 0) for rises and =IF(step<0, -step, 0) for falls, then color each series.
Why are my bars solid from zero instead of floating?
The base series must be set to No Fill. Otherwise the helper column shows as a solid brick and the floating effect is lost.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Bullet chart · Combo chart · Running cash balance

Function references: IF · SUM