How to Create a Waterfall Chart in Excel: A Complete Guide

Waterfall charts are one of the most useful — and underused — chart types in Excel. They're built to show how a starting value rises or falls through a series of positive and negative changes, landing at a final result. Finance teams use them constantly for profit-and-loss breakdowns, budget variance analysis, and cash flow statements. But they work just as well for anything involving sequential changes to a running total.

Here's exactly how to build one, what to watch out for, and how your specific data setup affects the result.

What a Waterfall Chart Actually Shows

Before building anything, it helps to understand the structure. A waterfall chart displays:

• A starting baseline value (e.g., opening balance, beginning revenue)
• A series of positive increments (shown floating above the baseline)
• A series of negative decrements (shown hanging down from the running total)
• A final total bar anchored to zero

The floating bars are the signature feature — they visually communicate change, not just absolute values. This makes trends and contributing factors far easier to read than a standard bar chart.

How to Insert a Waterfall Chart in Excel (Excel 2016 and Later)

Excel added a native Waterfall chart type in Excel 2016, so if you're running a current version of Microsoft 365 or Excel 2016/2019/2021, you don't need any workarounds.

Step 1: Structure your data correctly

Your data needs two columns:

• Column A: Category labels (e.g., "Starting Balance," "Sales Revenue," "Operating Costs," "Net Income")
• Column B: Values — positive numbers for increases, negative numbers for decreases

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 bar chart with floating segments). Click it, and Excel generates the chart automatically.

Step 4: Set totals correctly 📊

This is where most people get tripped up. Excel doesn't automatically know which bars represent totals versus incremental changes. For your starting and ending values, right-click the bar → Format Data Point → check "Set as total". This anchors those bars to zero instead of floating them.

Customizing Your Waterfall Chart

Once the basic chart is in place, several formatting options matter:

• Connector lines: Excel adds these by default to show the running total flow. You can remove or style them under Format Data Series → Connector Lines.
• Color coding: By default, increases appear in one color (often blue or green) and decreases in another (often red). You can change these under Format Data Series for each category (positive, negative, total).
• Data labels: Right-click → Add Data Labels to display the exact value on each bar — helpful for financial presentations.
• Axis formatting: If your values are large numbers, format the Y-axis to show thousands or millions under Format Axis → Number.

What If You're on an Older Version of Excel?

Excel 2013 and earlier don't include a native waterfall chart type. Users on those versions traditionally built them using stacked bar charts with a hidden base series — a manual workaround that takes more steps but produces the same visual result.

The manual method involves:

1. Creating three data series: Base (invisible), Increase, and Decrease
2. Calculating the running totals manually for the base series
3. Setting the base series fill to No Fill so only the floating portions show
4. Adjusting gap widths to remove space between bars

This method still works but requires more formula setup and is easier to break when data changes. If you're regularly building these charts, that alone is a meaningful reason to consider upgrading to a version with native support.

Variables That Affect How Your Chart Looks and Behaves

Not every waterfall chart behaves the same way, and several factors determine whether yours will be clean and readable or confusing:

Data volume: Waterfall charts get crowded quickly. More than 10–12 bars on a single chart tends to compress labels and make the flow hard to follow. If your dataset is large, consider grouping smaller categories.

Mixed positive/negative sequences: If your data alternates frequently between gains and losses, the visual can become hard to read. Reordering categories or separating the chart into two sections sometimes helps.

Subtotals vs. totals: Some datasets include running subtotals mid-sequence (e.g., "Gross Profit" before expenses). Each subtotal bar needs to be individually set as a total in Excel, or it will float incorrectly.

Excel version and OS: The native waterfall chart renders slightly differently between Windows and Mac versions of Excel, particularly around default colors and font rendering. Functionality is the same, but visual output can vary.

Data connected to external sources: If your chart pulls from a Power Query table or linked workbook, values update automatically — but chart formatting (especially "Set as total" settings) may reset if the data structure changes.

Where the Chart Type Falls Short

Waterfall charts aren't the right tool for every situation. They struggle when:

• You need to compare multiple series side by side (use grouped bar charts instead)
• Your data doesn't represent sequential changes to one value (the floating bar concept breaks down)
• You're presenting to audiences unfamiliar with the format (some readers find the floating bars counterintuitive without a brief explanation)

How much any of this matters depends entirely on what your data represents, who's reading the chart, and what decision or story you're trying to support. The mechanics of building the chart are straightforward — but whether a waterfall chart is the clearest way to present your specific dataset is a question only your data can answer. 📈