- Get link
- X
- Other Apps
This article explains how to build a fully dynamic Excel waterfall chart where all categories and series (base, increases, decreases, and totals) are generated from formulas only, so that the chart updates automatically whenever your underlying data changes.
1. Why build a waterfall chart from formulas only
A waterfall chart is a powerful way to show how a starting value moves step by step to an ending value through a series of positive and negative contributions. It is widely used for profit bridge analysis, budget vs actual reconciliation, cash movement analysis, and variance analysis.
Excel has a built-in waterfall chart type, but in many real-world workbooks you need more control than the default engine offers. For example, you may want custom subtotal logic, special “total” bars, or robust formulas that work across different reporting periods. Building a waterfall chart from formulas only gives you several advantages.
- Full control over how each step is categorized as increase, decrease, or total.
- Ability to add new rows to the data and have the chart extend automatically.
- Transparent logic: reviewers can audit the helper formulas instead of reverse-engineering chart behavior.
- Compatibility with older versions of Excel that do not have the native waterfall chart type.
Note : When waterfall logic is driven by formulas, the chart becomes a reliable reporting layer that survives copy, filter, and aggregation operations without manual fixing of bar heights.
2. Designing the source data for a formula-driven waterfall
To create a waterfall chart from formulas, you need a consistent data structure. The core idea is simple: start with a clean list of steps (stages in the bridge) and then build formula-based helper columns that translate those steps into chart-ready series.
2.1 Basic data table structure
Assume you have a list of steps in a worksheet named Bridge. For clarity, use the following columns.
- Column A – Step: Order of the step (1, 2, 3, ...).
- Column B – Label: Name shown on the axis (for example, Opening Balance, Sales, Costs, Closing Balance).
- Column C – Amount: Signed amount; positive values add to the bridge and negative values subtract.
A minimal example might look like this.
| Step (A) | Label (B) | Amount (C) |
|---|---|---|
| 1 | Opening balance | 1000 |
| 2 | Sales | 600 |
| 3 | Costs | -400 |
| 4 | Other income | 150 |
| 5 | Closing balance | 1350 |
The first and last rows are totals; the rows in between are incremental changes. The goal is to translate this table into four series for the chart.
- Base.
- Increase.
- Decrease.
- Total.
2.2 Turning the range into an Excel Table
Before adding formulas, convert the range into an Excel Table so that formulas and chart ranges expand automatically.
- Select the range A1:C5.
- Press Ctrl + T and confirm that “My table has headers” is checked.
- Name the table, for example
Bridge, via Table Design > Table Name.
After this step, the columns are Bridge[Step], Bridge[Label], and Bridge[Amount].
Note : Using an Excel Table is the easiest way to keep the waterfall chart dynamic. Any new step appended at the bottom of the table will automatically propagate through the helper formulas and into the chart.
3. Creating categories from formulas: base, increase, decrease, total
Instead of manually tagging each row as an increase, decrease, or total, you can let formulas derive these categories from the step number and amount. Add four new columns to the right of the table.
- Column D – Base.
- Column E – Increase.
- Column F – Decrease.
- Column G – Total.
Extend the Excel Table so that these columns become part of Bridge. You now have the following headers.
Bridge[Step],Bridge[Label],Bridge[Amount],Bridge[Base],Bridge[Increase],Bridge[Decrease],Bridge[Total].
3.1 Helper column: running total
For many waterfall patterns, it is convenient to compute a running total first, then derive the base value of each bar from that. Add another helper column H – RunningTotal to the table.
In the first data row (for example, H2 in the worksheet, corresponding to the first row of the table), enter the running total formula.
=SUM(INDEX(Bridge[Amount],1):[@Amount])This structured reference formula calculates the sum from the first amount in the table down to the current row. Because it is defined inside the table, it spills correctly down all rows as you add new records.
3.2 Formula for the Base column
The Base category is the height from which a bar starts. For the first row (Opening balance) the base is zero. For subsequent rows, the base is the previous running total, excluding the current row.
In Bridge[Base], use the following formula.
=IF([@Step]=1, 0, INDEX(Bridge[RunningTotal],[@Step]-1) )This logic means the following.
- If the step is 1 (the first bar), start from zero.
- Otherwise, look up the running total of the previous step and use that as the base of the current bar.
Note : This formula assumes that
Bridge[Step] is a continuous sequence 1, 2, 3, ... without gaps. If steps can be missing, use a different key such as row index or a MATCH-based lookup.3.3 Formulas for Increase and Decrease columns
The Increase and Decrease series determine the size of the positive and negative bars. Only intermediate steps (not totals) should be treated as increases or decreases. In this example, the first and last rows are totals and all rows between them are changes.
Define Increase as the positive amount at intermediate steps and zero otherwise.
=IF( OR([@Step]=1, [@Step]=MAX(Bridge[Step])), 0, MAX([@Amount],0) )Define Decrease as the absolute value of negative amounts at intermediate steps and zero otherwise.
=IF( OR([@Step]=1, [@Step]=MAX(Bridge[Step])), 0, -MIN([@Amount],0) )This categorization is formula-driven, based only on the step index and sign of the amount. When you insert a new row with a negative amount between first and last step, it automatically becomes a Decrease bar in the waterfall chart.
3.4 Formula for the Total column
The Total series is used for the first and last bars (and optionally for any manually defined subtotals). In this pattern, only the first and last rows are totals.
=IF( OR([@Step]=1, [@Step]=MAX(Bridge[Step])), [@Amount], 0 )Now the categories are completely defined by formulas.
- The Base column provides starting points.
- The Increase column holds positive contributions.
- The Decrease column holds the size of negative contributions.
- The Total column holds the values of start and end bars.
4. Building the waterfall chart from the helper columns
With the helper columns in place, you can build the stacked column chart that behaves like a waterfall chart. The Base series will be hidden while the other series remain visible.
4.1 Selecting data for the chart
To build the chart.
- Select the Label column and the four series columns:
Bridge[Label],Bridge[Base],Bridge[Increase],Bridge[Decrease],Bridge[Total]. - On the Ribbon, choose Insert > Column or Bar Chart > Stacked Column.
Excel creates a stacked column chart with five series.
- Base.
- Increase.
- Decrease.
- Total.
4.2 Formatting the waterfall behavior
Apply the following formatting steps to turn the stacked column into a waterfall chart.
- Click the Base series in the chart, then set Fill to No fill and Border to No line. The Base series remains in the stack but becomes invisible.
- Format the Increase series with a consistent color (for example, green) to represent positive changes.
- Format the Decrease series with another color (for example, red) to represent negative changes.
- Format the Total series with a third color (for example, dark blue) and consider adding a thicker border or data labels.
- Adjust gap width in the series options to control spacing between bars.
Because all series refer to columns in the Excel Table, any new row automatically appears in the chart with correct categorization.
4.3 Optional: Subtotals as formula-based categories
In many reporting scenarios, you need subtotals inside the waterfall (for example, gross profit, operating profit). You can incorporate them with a simple additional rule: treat any row with a specific label as a total.
Extend the Total formula as follows.
=IF( OR( [@Step]=1, [@Step]=MAX(Bridge[Step]), ISNUMBER(SEARCH("total",[@Label])) ), [@Amount], 0 )This version automatically treats any row whose label contains the word “total” (for example, “Gross total”, “Operating total”) as a Total bar.
Note : If you use language-specific labels or different naming conventions, adjust the search term or replace it with an explicit list of labels using OR conditions or a lookup table.
5. Making the waterfall chart robust for real-world workbooks
Once the basic structure is working, you can harden the logic so that it behaves well with filters, missing rows, and changed periods.
5.1 Handling blank or zero rows
In multi-period models, some steps may be present but have zero values in certain months. You may not want them to show as bars in the chart. Wrap your helper formulas in a check for blank labels or amounts.
=IF( OR([@Label]="",[@Amount]=""), 0, <existing logic> )Apply this pattern to Base, Increase, Decrease, and Total. This ensures that placeholder rows with no data do not create unwanted zero-height bars.
5.2 Using dynamic arrays for more complex scenarios
In current versions of Excel supporting dynamic arrays and the SCAN and LET functions, you can derive categories and running totals with more compact but powerful formulas. For example, you can compute a running total directly from a dynamic list of amounts without adding a dedicated column.
=SCAN(0, Bridge[Amount], LAMBDA(acc, x, acc + x))This formula returns an array of running totals that can be spilled into a helper range and referenced by the Base series. With LET, you can reuse this array inside other formulas to avoid multiple recalculations.
5.3 Integrating with variance analysis models
The same formula-based waterfall structure can be reused for scenario analysis and variance analysis. For example, you can build a bridge from budget to actual using steps such as volume variance, mix variance, and price variance. In that case.
- The Label column identifies variance components.
- The Amount column holds the magnitude of each variance.
- The step order defines the narrative of the bridge.
Because the categories are derived entirely from formulas, you can change the underlying amounts by referencing different data sources (for example, different scenarios) and the chart will respond instantly.
6. Quality checks for a formula-based waterfall
Before publishing a formula-driven waterfall chart, always implement basic quality checks. A waterfall is a reconciliation chart, so the numbers must tie out.
6.1 Check that the bridge closes properly
Use a simple control formula to verify that the total of the movements matches the difference between end and start.
=SUMIFS(Bridge[Amount],Bridge[Step],"<>1",Bridge[Step],"<>" & MAX(Bridge[Step])) - (INDEX(Bridge[Amount],MAX(Bridge[Step])) - INDEX(Bridge[Amount],1))If this returns zero, the bridge is consistent. If not, some movements are missing or mis-signed.
6.2 Verify that helper columns sum correctly
Because the chart relies on Base, Increase, Decrease, and Total, ensure that their combined effect reproduces the running total curve. For each step, the following relation should hold.
Base + Increase - Decrease + TotalContribution = RunningTotalWhere TotalContribution is either zero (for intermediate steps) or the total value at that step (for starting and ending bars). You can create a diagnostic column that checks this equality row by row and flags any deviations.
Note : These control formulas are not strictly required for the chart to work, but they are extremely useful in audited workbooks, regulatory reports, and internal review processes.
7. Practical tips for deploying waterfall charts in workbooks
Once the formulas are stable, you can integrate the waterfall chart into dashboards and reports used by non-technical stakeholders.
- Place the data table on a dedicated calculation sheet and the chart on a presentation sheet.
- Lock cells containing formulas and protect the sheet so that users cannot accidentally overwrite the helper logic.
- Use consistent colors for increases, decreases, and totals across all waterfall charts in the workbook.
- Add clear chart titles and axis labels that explain the reconciliation view (for example, “Profit bridge: last year to this year”).
- Consider adding slicers or filters that switch between scenarios while reusing the same formula structure.
FAQ
Can I use this formula-based waterfall approach with older versions of Excel?
Yes, the approach is based on stacked column charts and standard worksheet formulas. As long as your Excel version supports structured references for Tables, you can use the exact formulas shown here. If Tables are not available, you can rewrite the formulas using fixed ranges and row numbers, although you will lose some of the automatic expansion behavior.
What if my first and last bars are not simple opening and closing balances?
You can generalize the total logic to any rows you want to appear as totals. Instead of checking whether the step is 1 or the maximum step, you can test the label for specific keywords or reference a separate flag column where you mark any row as a total. The Total series formula then uses that flag to decide whether to show the amount in the Total column.
How do I handle multiple subtotals in the same waterfall chart?
Introduce a helper column, such as Type, that marks each row as Start, Change, Subtotal, or End. Then derive the Increase, Decrease, and Total formulas based on this Type column. Rows marked as Change use the Increase or Decrease series, and rows marked as Start, Subtotal, or End use the Total series. This gives you a flexible way to insert intermediate totals without changing chart structure.
Can this structure work with dynamic arrays and spilled ranges?
Yes, you can construct the entire bridge as a dynamic spilled range where labels, amounts, and helper columns are generated by formulas like FILTER, SORT, and SCAN. In that case, the chart source data points to the spilled range rather than a Table. The core logic for Base, Increase, Decrease, and Total remains the same; only the referencing style changes.
Is there any advantage to using the built-in Excel waterfall chart instead?
The built-in waterfall chart offers quick creation and some automatic subtotal handling, which can be suitable for simple use cases. However, when you need rigorous control, traceable formulas, and compatibility with complex reporting models, a formula-based stacked column waterfall often provides better transparency and flexibility. Many advanced workbooks combine both approaches depending on the audience.