How to Create a Waterfall Chart in Excel: Step-by-Step
Waterfall charts visualize how an initial value transforms through a series of positive and negative changes to reach a final result. Financial analysts call them 'bridge charts' because they...
Key Insights
- Excel 2016+ includes a native waterfall chart type that handles most use cases with a few clicks, but understanding the underlying stacked bar technique gives you more control and works in older versions.
- Proper data structure is everything—your source data needs a clear category column and a values column, with running totals calculated separately if you’re using the manual method.
- Always explicitly set your total and subtotal bars; Excel won’t automatically detect which values represent cumulative totals versus incremental changes.
Introduction to Waterfall Charts
Waterfall charts visualize how an initial value transforms through a series of positive and negative changes to reach a final result. Financial analysts call them “bridge charts” because they literally bridge the gap between a starting point and an ending point, showing every contributing factor along the way.
You’ll see waterfall charts everywhere in business contexts: explaining how Q1 revenue became Q2 revenue, breaking down budget variances, showing cost buildup in manufacturing, or decomposing year-over-year profit changes. They answer the question “what happened between point A and point B?” in a way that stacked bars or line charts simply cannot.
The visual logic is straightforward. Each bar floats at the cumulative level of all previous values, so increases push subsequent bars higher while decreases pull them lower. Total bars anchor to the baseline, showing absolute values rather than changes.
Preparing Your Data Structure
Before touching any chart tools, get your data right. A waterfall chart needs two columns at minimum: categories (the labels for each bar) and values (the incremental changes). Here’s a typical financial bridge dataset:
| Category | Value |
|-----------------------|----------|
| Starting Revenue | 500000 |
| New Customers | 75000 |
| Price Increases | 25000 |
| Lost Customers | -45000 |
| Discounts Given | -30000 |
| Product Returns | -15000 |
| Ending Revenue | 510000 |
Notice the structure: a starting value, several positive and negative changes, and an ending total. The ending value (510,000) should equal the starting value plus all intermediate changes (500,000 + 75,000 + 25,000 - 45,000 - 30,000 - 15,000 = 510,000).
For the manual stacked bar method, you’ll need additional calculated columns:
| Category | Value | Base | Fall | Rise |
|-----------------------|----------|----------|----------|----------|
| Starting Revenue | 500000 | 0 | 0 | 500000 |
| New Customers | 75000 | 500000 | 0 | 75000 |
| Price Increases | 25000 | 575000 | 0 | 25000 |
| Lost Customers | -45000 | 555000 | 45000 | 0 |
| Discounts Given | -30000 | 525000 | 30000 | 0 |
| Product Returns | -15000 | 495000 | 15000 | 0 |
| Ending Revenue | 510000 | 0 | 0 | 510000 |
The Base column represents the invisible portion that positions each bar correctly. Fall and Rise columns separate negative and positive values for distinct coloring.
Creating a Basic Waterfall Chart (Excel 2016+)
If you’re running Excel 2016 or later, Microsoft built waterfall charts directly into the chart gallery. Here’s the process:
Step 1: Select your two-column data range (categories and values), including headers.
Step 2: Navigate to Insert → Charts → Waterfall. In the Charts group, click the Waterfall icon (it looks like a series of floating bars).
Step 3: Excel generates a waterfall chart immediately. By default, it treats every value as an incremental change.
Selection: A1:B8 (Category and Value columns with headers)
Insert > Charts > Waterfall (or Insert Waterfall Chart from dropdown)
The native chart handles the floating bar positioning automatically. Positive values display in one color (typically green), negative values in another (typically red), and the chart respects the cumulative running total for bar placement.
Step 4: Right-click any bar you want to designate as a total (like “Starting Revenue” or “Ending Revenue”) and select “Set as Total.” This anchors that bar to the baseline.
The native method works well for standard use cases. However, you lose granular control over formatting, and some organizations still run older Excel versions.
Manual Waterfall Method (Stacked Bar Approach)
The stacked bar technique works in any Excel version and offers complete formatting control. The concept: create a stacked bar chart with three series—an invisible base, a visible increase series, and a visible decrease series.
Step 1: Build your extended data table with Base, Fall, and Rise columns.
Step 2: Calculate the Base column using running totals with conditional logic:
# For cell C3 (first data row after Starting Revenue):
=IF(B3>=0, C2+B2, C2+B2+B3)
# Generalized formula (assuming B is Value, C is Base):
# If current value is positive: previous base + previous value
# If current value is negative: previous base + previous value + current value
Step 3: Calculate Fall and Rise columns:
# Fall column (D):
=IF(B2<0, ABS(B2), 0)
# Rise column (E):
=IF(B2>=0, B2, 0)
Step 4: Select columns A, C, D, E (Category, Base, Fall, Rise) and insert a Stacked Bar chart.
Step 5: Format the Base series to have no fill and no border—this makes it invisible while still positioning the visible bars correctly.
Right-click Base series → Format Data Series
Fill: No fill
Border: No line
The result looks identical to a native waterfall chart but gives you full control over every visual element.
Formatting and Customization
Default waterfall charts work, but polished presentations require customization. Here’s what to adjust:
Color Coding: Establish a consistent scheme. The standard convention uses green for increases, red for decreases, and gray or blue for totals.
Right-click any bar → Format Data Point
Fill: Solid fill
Color: Select appropriate color
# Typical color scheme:
Increases: #2E7D32 (dark green) or #4CAF50 (medium green)
Decreases: #C62828 (dark red) or #EF5350 (medium red)
Totals: #1565C0 (blue) or #757575 (gray)
Data Labels: Add values directly on bars for immediate comprehension.
Click chart → Chart Elements (+) → Data Labels
Position: Inside End (for positive) or Inside Base (for negative)
Format: Number with thousands separator
Connector Lines: Native waterfall charts include subtle connector lines between bars. For manual charts, you’ll need to add these as shapes or skip them entirely—they’re helpful but not essential.
Axis Formatting: Remove unnecessary gridlines, set appropriate number formatting on the value axis, and consider starting the axis at zero unless you have a specific reason not to.
Right-click Value Axis → Format Axis
Number: Currency or Number with 0 decimal places
Display units: Thousands or Millions for large values
Setting Subtotals and Total Bars
Subtotals break long waterfalls into digestible sections. A 15-item bridge chart becomes much clearer with subtotals after each logical grouping.
Native Chart Method:
1. Right-click the bar you want as a subtotal
2. Select "Set as Total"
3. The bar drops to the baseline and shows absolute value
Manual Chart Method:
For subtotals in stacked bar waterfalls, insert a row with the cumulative value at that point, then set its Base to 0 and its Rise value to the running total.
| Category | Value | Base | Fall | Rise |
|-----------------------|----------|----------|----------|----------|
| Starting Revenue | 500000 | 0 | 0 | 500000 |
| New Customers | 75000 | 500000 | 0 | 75000 |
| Price Increases | 25000 | 575000 | 0 | 25000 |
| Subtotal: Gross Adds | 600000 | 0 | 0 | 600000 | ← Subtotal row
| Lost Customers | -45000 | 555000 | 45000 | 0 |
Format subtotal bars with the same color as your totals to visually distinguish them from incremental changes.
Common Use Cases and Best Practices
Financial Reporting: Quarterly earnings bridges, showing how net income changed from prior period through revenue changes, cost changes, and one-time items.
Budget Variance Analysis: Starting with budgeted figures, showing favorable and unfavorable variances by category, ending with actual results.
Pricing Waterfalls: Decomposing list price down to net realized price through discounts, rebates, and allowances.
Project Cost Tracking: Beginning with original estimate, showing scope changes, overruns, and savings to reach final cost.
Best Practices:
Limit bars to 10-12 maximum. Beyond that, consider grouping smaller items into an “Other” category or using subtotals to break the visual into sections.
Order matters. Place items in logical sequence—either by business process flow or by magnitude (largest impacts first).
Always verify that your intermediate values sum correctly to your ending total. A waterfall that doesn’t reconcile destroys credibility.
Use consistent sign conventions. If you’re showing cost reductions as positive values because they help profit, make that explicit in labels.
Label clearly. “Revenue Impact: +$75K” is better than just “$75,000” when the sign and context aren’t obvious from position alone.
Waterfall charts take more setup time than basic bar charts, but they communicate sequential value changes with unmatched clarity. Master both the native and manual methods, and you’ll have the right tool regardless of Excel version or customization requirements.