What a waterfall chart does and when to use one
A waterfall chart is a column chart that shows how a starting number changes through a series of steps to reach an ending number. Each column represents a change — an increase or decrease — and the chart stacks them visually so you can see the cumulative effect. The columns appear to cascade downward (or upward), which is where the name comes from.
Waterfall charts work best when you want to explain how a total breaks down or how it changed over time. Common uses include showing how revenue becomes profit after subtracting costs, how a bank balance changed through deposits and withdrawals, or how a budget was allocated across departments. The format makes it obvious which steps added value and which ones subtracted it.
Excel has a built-in waterfall chart type starting with Excel 2016 (Windows) and Excel 2016 (Mac). If you have an older version, you can build one manually using stacked column charts, but the built-in version is faster and cleaner.
Key Takeaways
- Excel's waterfall chart type (available in 2016 and later) automatically handles the stacking and positioning of columns so increases and decreases flow visually from left to right.
- Your data needs three columns: category names, values for each step, and a total or connector row at the end.
- Mark intermediate totals and the final total as "Total" in the chart settings so Excel treats them differently from regular changes.
- Waterfall charts work best with five to twelve steps; too many steps make the chart hard to read.
Setting up your data in the right structure
Before you insert a chart, organize your data in two columns. The first column holds category names (the labels for each step), and the second column holds the values. Put your starting balance or starting revenue in the first row. Then list each change — positive or negative — in the rows below. End with a total row.
Here is a concrete example. Say you want to show how a $10,000 budget was spent. Your data might look like this:
| Category | Amount |
|---|---|
| Starting Budget | 10000 |
| Payroll | -6000 |
| Equipment | -2000 |
| Software | -1200 |
| Remaining | 800 |
Use negative numbers for expenses or decreases. The final row should show what is left over or the ending total. Do not calculate it yourself — Excel will do that once you mark it as a total in the chart settings.
Inserting the waterfall chart
Select your data range, including both the category names and the values. On the Insert tab, click the Charts button. In the chart type menu, look for Waterfall (it may be under "Stock" or listed separately depending on your Excel version). Click it and choose the standard waterfall layout.
Excel will insert a basic waterfall chart into your sheet. At this point, the columns may not look right because Excel does not yet know which rows are totals and which are regular changes. The chart will show all columns the same way, which is not what you want.
Marking totals so the chart displays correctly
Right-click on the chart and select Edit Data or double-click the chart to enter edit mode. Then right-click on the data series (the columns themselves) and choose Format Data Series. In the Format pane that opens, look for the option to set data point properties.
Click on the first data point (your starting balance row) and set it to "Total". This tells Excel to treat it as a baseline, not a change. Then click on your final row (Remaining or Ending Balance) and set it to "Total" as well. If you have intermediate subtotals in the middle of your chart, mark those as "Total" too.
Once you mark the totals, the chart will redraw. The starting and ending columns will now sit on the baseline, and the intermediate changes will stack between them. Expenses will drop downward (shown in one color) and income or additions will rise upward (shown in another color).
Customizing colors, labels, and layout
By default, Excel colors increases one way and decreases another. You can change these colors by right-clicking on a column and selecting Format Data Point. Choose a fill color that matches your preference or your organization's style.
Add a chart title by clicking the chart and using the Chart Title button on the Design tab. Add axis labels if the values are not obvious — for example, label the vertical axis as "Dollars" or "Amount". You can also add data labels to each column so readers see the exact value without guessing from the grid.
To add data labels, right-click the data series and choose Add Data Labels. Then right-click the labels themselves and choose Format Data Labels. Select which information to show: the value, the category name, or both. Position the labels inside or outside the columns depending on how much space you have.
Common mistakes and how to fix them
The most common mistake is forgetting to mark the starting row and ending row as totals. If your chart shows all columns stacked in a line instead of cascading, this is the problem. Go back into Format Data Series and mark those rows as "Total".
Another mistake is using too many steps. A waterfall chart with twenty categories becomes cluttered and hard to read. If you have that many line items, consider grouping smaller ones into an "Other" category or breaking the chart into two separate charts.
If your values are very different in size — for example, one expense is $50,000 and another is $100 — the smaller columns become hard to see. In that case, consider whether a waterfall chart is the right choice. A straightforward table or a bar chart might communicate the information more clearly.
When to use alternatives instead
A waterfall chart is not always the best choice. If you want to compare values across categories without showing how they connect to a total, a regular column chart or bar chart works better. If you want to show change over time (like monthly revenue), a line chart or area chart is clearer.
A waterfall chart also assumes your reader understands how the columns connect. If your audience is unfamiliar with this chart type, a straightforward table with a running total column may communicate faster. You can always add a waterfall chart as a visual summary after the table.
Frequently Asked Questions
Can I use a waterfall chart if some of my values are zero?
Yes. A zero value will show as no column at all, which is fine. Excel will skip over it and move to the next step. This is useful when you want to show that a particular category had no impact on the total.
What if I want to show multiple waterfall charts side by side?
You can insert multiple charts on the same sheet, but they will not share a baseline. Each chart will have its own starting and ending points. If you need to compare two different scenarios or time periods, consider using separate sheets or placing the charts far enough apart that they do not crowd each other.
How do I change the order of the categories in my waterfall chart?
Rearrange the rows in your data table. The chart will update automatically to match the new order. You can also right-click the chart and edit the data range to reorder categories without moving your original data.
Can I add a target line or benchmark to my waterfall chart?
Not directly within the waterfall chart itself. However, you can add a line chart on top of the waterfall by using a combo chart. This is more advanced and may make the chart harder to read, so test it first to make sure it clarifies rather than confuses.