Part of the free Module 9: Advanced Charts and Infographics · Lesson 7 of 8 · Full Excel course
Updated on 5 September 2026 · The built-in waterfall chart needs Excel 2016 or later; the stacked column method works in every version.
A waterfall chart in Excel shows how a starting value becomes an ending value through a series of increases and decreases. Each floating column starts where the previous one ended, so the reader sees the running total move step by step. It is also called a bridge chart and is the standard way to explain a profit bridge, a cash flow movement or a headcount change.
What a waterfall chart shows and when to use it
Use a waterfall when the question is “how did we get from A to B?”. A column chart would show the opening and closing figures as two bars and hide the story in between; the waterfall breaks the difference into named steps. Typical uses are revenue to net profit, budget to actual variance by cost line, opening to closing cash, opening to closing inventory, and last year to this year sales by region.
Excel offers two ways to build one. The built-in Waterfall chart type, added in Excel 2016, takes seconds but gives limited control. The classic stacked column method uses helper columns and works in Excel 2013, 2010 and in any tool that can draw a stacked column, including Google Sheets and PowerPoint. This lesson builds both from the same data.
The sample data
Type this small revenue bridge into a blank sheet. Column A holds the labels and column B the change at each step. The opening and closing rows hold totals, not movements.
| Cell | Step | Value |
|---|---|---|
| A2:B2 | Opening revenue | 500 |
| A3:B3 | Product sales | 120 |
| A4:B4 | Services | 80 |
| A5:B5 | Discounts | -45 |
| A6:B6 | Returns | -30 |
| A7:B7 | Closing revenue | 625 |
Put the closing figure in B7 as a formula, =SUM(B2:B6), so it stays correct when a movement changes.
Method 1: the built-in waterfall chart (Excel 2016 and later)
- Select A1:B7 including the headers.
- Go to Insert > Charts > Insert Waterfall, Funnel, Stock, Surface or Radar Chart > Waterfall. Excel draws every row as a floating movement, including the opening and closing rows, so the last two columns look wrong for a moment.
- Click the Opening revenue column once to select the series, then click it again to select only that point. Right-click and choose Format Data Point, then tick Set as total. The column drops to the axis.
- Repeat for Closing revenue. It now rests on the axis at 625.
- Select the chart and use Chart Design > Add Chart Element > Data Labels > Outside End.
- Right-click any column, choose Format Data Series and set Gap Width to about 50 percent. Tick Show connector lines if you want the thin lines that join each step.
- Click a legend entry (Increase, Decrease or Total) and use Format > Shape Fill to recolour all columns of that kind at once: green for increases, red for decreases, grey or blue for totals.
That is the whole build. The limitation is that the built-in type is a special chart: it cannot be combined with a line series, its axis cannot be given a custom minimum, and it does not exist in Excel 2013 or older, where the file opens with a blank placeholder.
Method 2: the stacked column waterfall (every version)
The classic method stacks an invisible Base column under a visible Up or Down column. Four helper columns do the arithmetic. Add these headers in C1:G1: Running, Base, Up, Down, Total.
| Cell | Formula | Purpose |
|---|---|---|
| C2 | =B2 |
Running total starts at the opening value |
| C3 | =C2+B3 (fill down to C7 as =C6 for the closing row) |
Running total after each movement |
| D3 | =MIN(C2,C3) (fill down to D6) |
Invisible base: the lower of the previous and current running totals |
| E3 | =MAX(B3,0) (fill down to E6) |
Height of an increase, otherwise 0 |
| F3 | =MAX(-B3,0) (fill down to F6) |
Height of a decrease, otherwise 0 |
| G2 | =C2 |
Opening total drawn from the axis |
| G7 | =C7 |
Closing total drawn from the axis |
Leave D2, D7, E2, E7, F2, F7 and G3:G6 as 0. The Base column must never be charted with a fill; it only lifts the visible column to the right height.
Build the chart
- Select A1:A7, hold Ctrl and select D1:G7. Do not include the Change or Running columns.
- Go to Insert > Charts > Insert Column or Bar Chart > 2-D Column > Stacked Column.
- Click the Base series, then Format > Shape Fill > No Fill and Shape Outline > No Outline. The floating effect appears immediately.
- Fill Up green, Down red and Total dark blue or grey.
- Right-click any column, Format Data Series, set Gap Width to 50 percent.
- Select the Up series and add Data Labels > Inside End; repeat for Down and Total. Give the labels the number format
0;-0;;(Format Data Labels > Number > Custom) so the zero values on rows where the series is empty stay hidden. - Delete the Base entry from the legend by clicking it twice and pressing Delete, or remove the legend altogether.
- Add a title such as Revenue bridge, Q1 and remove the gridlines if the labels carry the numbers.
Built-in versus stacked column: which to choose
| Feature | Built-in Waterfall | Stacked column method |
|---|---|---|
| Excel versions | 2016, 2019, 2021, 365 | All versions, Google Sheets, PowerPoint |
| Build time | Under a minute | Five minutes with helper columns |
| Totals | Tick Set as total per point | Total helper column |
| Running total that goes negative | Handled automatically | Needs an extra crossing calculation |
| Connector lines | One tick box | Manual lines or an extra line series |
| Combine with a line series | Not possible | Yes, via Change Chart Type |
| Custom axis minimum | Not available | Full axis control |
| Copy to PowerPoint 2013 or older | Shows as a blank box | Works |
Worked example
With the sample data the helper table evaluates as follows. The Running column climbs 500, 620, 700, 655, 625; the Base column for the movement rows is 500, 620, 655, 625, which is always the lower end of each step.
| Step | Change | Running | Base | Up | Down | Total |
|---|---|---|---|---|---|---|
| Opening revenue | 500 | 500 | 0 | 0 | 0 | 500 |
| Product sales | 120 | 620 | 500 | 120 | 0 | 0 |
| Services | 80 | 700 | 620 | 80 | 0 | 0 |
| Discounts | -45 | 655 | 655 | 0 | 45 | 0 |
| Returns | -30 | 625 | 625 | 0 | 30 | 0 |
| Closing revenue | 625 | 625 | 0 | 0 | 0 | 625 |
Check one row by hand. Discounts: the running total before the step is 700 and after it is 655, so the base is MIN(700,655) = 655 and the red Down column of 45 sits on top of it, reaching 700. The chart therefore shows a column hanging from 700 down to 655, which is exactly what a decrease should look like.
Tips and common mistakes
- Enter decreases as negative numbers. The MAX formulas rely on the sign; typing 45 instead of -45 draws Discounts as an increase.
- Keep the opening and closing rows as totals. In the built-in chart tick Set as total; in the stacked method give them a Total value and a zero Base.
- Do not put the Change or Running columns in the chart. Only Base, Up, Down and Total belong in the stacked column.
- Use the label format
0;-0;;so hidden zeros do not clutter the chart. - Order the steps logically, usually largest increase to largest decrease or in the order of the income statement; do not sort by value in the chart.
- Handle a running total that crosses zero with the built-in type, or add two more helper columns for the stacked method; the simple MIN and MAX pattern assumes the running total stays positive.
- Name the series in row 1. Legend entries and Change Chart Type dialogs read the header cells.
Errors and how to fix them
| Symptom | Cause | Fix |
|---|---|---|
| Closing column floats in the air | Set as total not ticked (built-in) or Total column empty (stacked) | Tick Set as total, or enter =C7 in G7 |
| A decrease is drawn as an increase | Positive number typed for a reduction | Enter the change as a negative value |
| Grey blocks appear under the columns | Base series still has a fill | Select Base and apply No Fill and No Outline |
| Labels show 0 on every column | Labels on a series with zero values | Apply the custom number format 0;-0;; |
| Columns overlap or look mis-stacked | Chart inserted as Clustered instead of Stacked Column | Change Chart Type to Stacked Column |
| Chart is blank on a colleague’s PC | Built-in waterfall opened in Excel 2013 or older | Rebuild with the stacked column method |
Practice exercise
- Type the six-row revenue bridge and build the built-in waterfall, then set both ends as totals and colour the increases green.
- Build the same bridge with the stacked column method and confirm every column matches the first chart.
- Add a subtotal after Services: insert a row Gross sales with
=C4in the Total column and 0 in Base, Up and Down. - Extend the stacked chart with a line series showing the running total (add column C as a line via Change Chart Type).
- Replace the numbers with your own cost lines, budget to actual, and write a one-sentence conclusion the chart supports.
Key takeaways
- A waterfall chart explains how a starting figure becomes an ending figure through named increases and decreases.
- Excel 2016 and later have a built-in Waterfall type; mark the opening and closing points with Set as total.
- The stacked column method uses Base, Up, Down and Total helper columns with MIN and MAX formulas and works everywhere.
- The Base series is always No Fill; only Up, Down and Total are visible.
- Negative movements must be typed as negative numbers for the formulas to place them correctly.
Related lessons
- Advanced Charts and Infographics course hub
- Dynamic chart with checkboxes (previous lesson)
- Funnel chart in Excel (next lesson)
- Stacked column chart from the Basic Charts module
- IF function for building alternative helper columns
- Microsoft Support: Create a waterfall chart
Frequently asked questions
Why does my waterfall chart show the closing total as a floating column?
Excel treats every point in a built-in waterfall as a movement until you tell it otherwise. Select the closing point on its own, open Format Data Point and tick Set as total. In the stacked column method the same problem means the Total helper column is empty for that row; enter the running total there and set its Base to zero.
Can I make a waterfall chart in Excel 2013 or 2010?
Yes, with the stacked column method in this lesson. Older versions do not have the Waterfall chart type, but a stacked column with an invisible Base series produces the same picture and gives you more control over colours, labels and the axis. The workbook also opens correctly in every later version.
How do I add a subtotal in the middle of a waterfall?
Insert a row where the subtotal belongs. In the built-in chart tick Set as total for that point. In the stacked method put the running total from the previous row in the Total column and zeros in Base, Up and Down, so the column starts from the axis like the opening and closing bars.
What is the difference between a waterfall chart and a bridge chart?
They are the same chart. Finance teams often say bridge, as in profit bridge or EBITDA bridge, while analysts and Excel menus say waterfall. Both describe floating columns that carry a running total from a starting value to an ending value.
Can a waterfall chart handle a running total that goes below zero?
The built-in Waterfall type handles it automatically. The simple stacked column method does not, because a single Base column cannot span both sides of the axis. If your running total crosses zero, use the built-in chart or extend the helper table with separate positive and negative base columns.
Want the finished version? Ready-made Excel dashboards, trackers and chart templates are available at NextGenTemplates.com.