How to Build a Waterfall Chart in Excel

A waterfall chart is one of the most powerful ways to visualize how a starting value changes through a series of positive and negative adjustments to reach a final result. Whether you're breaking down monthly cash flow, showing budget variances, or tracking cumulative project costs, a well-built waterfall chart makes the story inside your data immediately visible.

Excel has supported native waterfall charts since Excel 2016. If you're on an older version, the process is more manual — but still achievable.

What a Waterfall Chart Actually Shows

Before building one, it helps to understand the structure. A waterfall chart displays floating bars — each bar starts where the previous one ended, rather than from zero. This lets you see each incremental change in context.

There are three types of bars:

• Starting value — the baseline (e.g., opening balance)
• Positive values — increases shown above the previous bar
• Negative values — decreases shown as drops
• Subtotals or totals — bars that touch the baseline again to summarize accumulated values

The visual effect looks like stepping stones across a gap — hence the name.

Building a Waterfall Chart in Excel 2016 and Later 📊

This is the fastest route if your Excel version supports the native chart type.

Step 1: Prepare your data

Set up two columns: one for labels (categories or time periods) and one for the values representing each change. Include your starting value and ending total as separate rows.

Example layout:

Step 2: Select your data range

Highlight both columns, including headers.

Step 3: Insert the chart

Go to Insert → Charts → Waterfall (it looks like a series of rising and falling columns). Excel will generate a chart automatically.

Step 4: Set totals as totals

By default, Excel treats your closing balance (or any subtotal) as just another incremental value — which will make it float incorrectly. To fix this, double-click the closing balance bar to select only that bar, then check the "Set as Total" box in the Format Data Point panel. This grounds the bar to the baseline.

Repeat for any other subtotals in your chart.

Step 5: Format and label

• Add data labels via Chart Elements (the + icon) for clarity
• Adjust colors — typically green for positive, red for negative, and grey or blue for totals
• Edit axis titles and the chart title to make the chart self-explanatory

Building a Waterfall Chart in Excel 2013 or Earlier

Without native support, you simulate the floating bar effect using a stacked bar chart with an invisible base series.

The approach:

1. Create three additional helper columns alongside your data:

   • Base (the invisible spacer that lifts each bar to the right height)
   • Increase (positive values only)
   • Decrease (negative values as positive numbers)

2. Calculate the base for each row as the running cumulative total, subtracting decreases appropriately.

3. Insert a stacked bar (column) chart using all three series.

4. Select the Base series and set its fill to No Fill and border to No Border — making it invisible while still acting as a spacer.

5. Color the increase and decrease series appropriately.

This method requires more setup and formula accuracy, but produces the same visual result. The formulas in your base column are the most critical part — errors there will misalign all bars.

Common Issues and How to Fix Them 🔧

Bars floating at the wrong height Usually caused by incorrect base calculations or a total not being set as a total. Double-check your running sum logic.

Negative bars going the wrong direction In the manual method, make sure decreases are entered as positive values in the Decrease column and subtracted in the base calculation.

Subtotals not grounding to zero In native Excel charts, each subtotal or total bar must be individually set via "Set as Total." Missing one will leave it floating.

Chart looks cluttered Reduce the number of data points, or group smaller line items together before building the chart. Waterfall charts work best with 5–12 categories — beyond that, they become hard to read.

Factors That Affect Your Approach

The right method depends on several variables specific to your situation:

• Excel version — 2016+ gives you a native chart type; earlier versions require the manual stacked bar workaround
• Data complexity — simple linear series are straightforward; charts with multiple subtotals require more careful setup
• Presentation context — a chart embedded in a dashboard may need different formatting than one in a printed report
• Audience — financial teams familiar with waterfall charts may need less annotation than general audiences
• Update frequency — if the data refreshes regularly, building the chart from a structured table with named ranges makes maintenance far easier

Dynamic data sources (like Power Query outputs or pivot tables) introduce additional considerations around how the chart range updates when new rows are added. Static datasets are simpler to manage but require manual updates when figures change.

The gap between knowing how waterfall charts work and knowing which approach fits your specific workbook, audience, and update cycle is one that only your own setup can close. ✅