I remember trying to explain a company’s profit and loss statement to a room full of non-finance people using a regular bar chart, and watching their eyes glaze over. Numbers going up, numbers going down, a running total — it just didn’t click visually. Then I discovered waterfall charts, and the same data suddenly told a clear story: starting point, a series of increases and decreases, and a final total. It’s become my default chart for any data that builds up or breaks down from a starting value.
What Is a Waterfall Chart?
A waterfall chart shows how an initial value is affected by a series of intermediate positive or negative changes, ultimately leading to a final value. Each bar “floats” at the level where the previous bar left off, visually showing increases (bars rising) and decreases (bars falling) as you move from left to right. It’s often used for financial statements, where you start with a beginning balance, show various additions and subtractions, and end with a final balance.
When Should You Use a Waterfall Chart?
I use waterfall charts when:
- I’m showing how a starting value changes through a series of positive and negative contributions
- I’m presenting a profit and loss breakdown, budget variance, or cash flow analysis
- I want to show headcount changes (starting headcount, hires, departures, ending headcount)
- I need to visually explain “how did we get from A to B” through a sequence of changes
If your data doesn’t represent a sequence of additions and subtractions leading to a total, a waterfall chart isn’t the right fit — a standard bar or column chart will serve you better.
Required Data Structure
You need two columns: a category/label describing each step, and the corresponding value (positive for increases, negative for decreases). Here’s an example I often use:
| Category | Value |
|---|---|
| Starting Revenue | 100000 |
| New Sales | 25000 |
| Upsells | 8000 |
| Churn | -12000 |
| Refunds | -5000 |
| Ending Revenue | 116000 |
Notice the first and last rows represent totals, while the middle rows represent individual changes — positive or negative.
Step-by-Step Instructions
Here’s my process:
- Enter your data with category labels and their values, making sure decreases are entered as negative numbers.
- Select the data range, including headers.
- Go to the Insert tab.
- In the Charts group, click Insert Waterfall, Funnel, Stock, Surface, or Radar Chart.
- Choose Waterfall.
- Excel builds the chart automatically, with bars floating based on the cumulative effect of prior values.
- Mark totals as “Set as Total”: Right-click the bar representing your starting or ending total (e.g., “Starting Revenue” or “Ending Revenue”), and choose Set as Total. This anchors that bar to the axis (rather than floating) and typically colors it differently to visually distinguish it from the incremental changes.
Formatting and Settings I Adjust
Once the chart is built, here’s what I always clean up:
- Increase/decrease colors: Excel automatically colors increases and decreases differently (usually blue and orange by default) — I often switch these to green for increases and red for decreases, since that’s the convention most audiences expect.
- Total bar color: I make sure “total” bars (marked via Set as Total) stand out with a distinct color, often gray or a solid brand color, since they represent anchor points rather than changes.
- Data labels: I always turn these on so viewers can see the exact value of each increase or decrease, not just the visual bar height.
- Connector lines: Excel shows thin lines connecting the end of one bar to the start of the next by default — I usually leave these on since they help the eye follow the flow left to right.
A Practical Example
Say I’m presenting a company’s annual headcount change. Starting with 150 employees, I show +40 for new hires, -15 for voluntary departures, -8 for layoffs, and land on an ending total of 167. The waterfall chart makes the net change immediately clear while also showing exactly which factors contributed most to it — new hires clearly outweighing departures — something a simple “150 to 167” statement wouldn’t convey nearly as vividly.
Common Mistakes to Avoid
Mistakes I’ve run into:
- Forgetting to mark total bars: If you don’t right-click and select “Set as Total” for your starting and ending values, they’ll float like regular incremental bars instead of anchoring to the baseline, which visually misrepresents them.
- Entering decreases as positive numbers: A decrease must be entered as a negative value, or the chart will show it as an increase, completely reversing the story.
- Too many small categories: If you have 20+ tiny incremental changes, the chart becomes visually noisy. Consider grouping minor items into a single “Other Adjustments” category.
- Missing intermediate subtotals: For long sequences, adding periodic subtotal bars (also marked as “Set as Total”) helps break up a long chain of changes into more digestible segments.
Troubleshooting Tips
If your waterfall chart isn’t behaving:
- “Waterfall” option missing: This chart type requires Excel 2016 or later. If unavailable, you’ll need to update Office.
- Bars floating in the wrong place: Double-check your total/subtotal bars are marked correctly via “Set as Total” — this is the most common cause of a waterfall chart looking wrong.
- All bars are the same color: Excel colors by increase/decrease/total automatically, but if this doesn’t display correctly, check Format Data Series to confirm the correct category assignments.
- Chart looks disconnected or jumps oddly: Ensure your data is in the correct sequential order — waterfall charts read left to right in the order rows appear in your spreadsheet.
Real-World Use Cases
I’ve built waterfall charts for:
- Profit and loss statements showing revenue, costs, and net income
- Budget variance analysis comparing planned vs. actual spending changes
- Headcount bridges showing hires, departures, and net change
- Cash flow statements showing beginning balance, inflows, outflows, and ending balance
Best Practices
Before finalizing a waterfall chart, I always confirm:
- Starting and ending totals are correctly marked using “Set as Total”
- Decreases are entered as negative values, increases as positive
- Colors follow intuitive conventions (green/red for increase/decrease, neutral for totals)
- Data labels are visible so exact values aren’t left to guesswork
- The chart isn’t overloaded with too many small categories — group minor items together if needed
Waterfall charts turn a sequence of gains and losses into an intuitive visual story that almost anyone can follow at a glance. Once you understand the “Set as Total” trick for anchoring your starting and ending bars, building one in Excel becomes fast, and the payoff in clarity is enormous — especially for financial or headcount data that involves a lot of moving pieces.
