Excel Waterfall Chart Data Layout: How to Set It Up

For an Excel waterfall chart, arrange your source data in two columns: a category label and the amount of the increase or decrease at that step. Put the opening value first, list changes as positive or negative numbers, and put the final value last; then mark the opening and final bars as totals so Excel displays them from the baseline.

The basic data layout

A waterfall chart shows how a sequence of positive and negative changes moves a value from a starting point to an ending point. In Excel, the simplest source range is a single series with one row per step. Use a descriptive heading for each column and keep the category and its value on the same row.

Category: a label such as Opening balance, New customers, Costs, or Closing balance.
Amount: a numeric value for that step. Use positive values for increases and negative values for decreases.

For example, a table could contain Opening cash, 100; Sales, 40; Expenses, -25; Other income, 10; and Closing cash, 125. The opening amount is 100, the changes add up to 25, and the resulting ending value is 125. The rows are ordered in the same sequence in which the changes happen.

The key point is that the middle rows normally contain changes, not the running balance after each change. Excel calculates the cumulative positions of those change bars for the chart. You do not need to add a separate running-total column to create a basic waterfall chart.

A worked example of the numbers

Consider a department that begins a month with 100 units of inventory. It receives 40 units, ships 25, and receives 10 more. The closing amount is 125 units. A suitable source table is:

Category	Amount
Opening inventory	100
Received	40
Shipped	-25
Returned to stock	10
Closing inventory	125

Excel plots the first ordinary value as a column rising from zero, then positions each change relative to the level reached before it. The increase of 40 takes the running level to 140; the decrease of 25 takes it to 115; the increase of 10 takes it to 125. The closing row contains 125 because it reports the final level, not another increase of 125.

This distinction matters when you format the chart. The opening and closing rows are totals, while the middle rows are movements. If the closing value is left as an ordinary change, Excel adds it to the prior level instead of treating it as the final level. That can make the last column appear far too high.

Create the chart from the source range

In Excel for Microsoft 365 on Windows, select the two-column range, including its headings. On the ribbon, choose Insert > Insert Waterfall, Funnel, Stock, Surface or Radar Chart > Waterfall. Excel creates a chart with one category series and a column for each row in the selected range.

Enter the labels and amounts in adjacent columns, without inserting blank rows within the data.
Select the full range, including the column headings.
Choose the Waterfall chart from the Insert tab.
Right-click the opening column and choose Set as Total.
Repeat for the closing column and for any intermediate subtotal that should start from the baseline.

The right-click command applies to the selected data point, not every column. Check each total individually, especially if the chart has more than one subtotal. In Excel for Mac, the chart type and data-point formatting are available too, though ribbon organization and context-menu wording can vary slightly by release. Excel for the web may offer a different set of chart editing controls, so use the desktop app if you cannot find the required total-point setting.

Totals, subtotals, and running balances

Opening and ending values are often called totals or endpoints. Marking a point as a total tells Excel to anchor that column at the chart baseline rather than continue the previous step’s rise or fall. This is a chart setting; it does not change the number in the worksheet cell.

Subtotals work the same way. Suppose a report shows an opening amount, several revenue changes, a Revenue subtotal, several expense changes, and a final Profit total. Mark Revenue as a total if its column should display the subtotal from zero. The later expense changes then continue from the cumulative position represented by that subtotal. Mark Profit as a total if it is a final reported result rather than another movement.

Use the number that describes the actual subtotal in the source cell. Do not enter a subtotal as the sum of all prior movements and also leave it as an ordinary change: that would add the subtotal to the running level a second time. Conversely, entering only the last movement in a row labeled as a closing total makes the reported endpoint incorrect. Decide whether each row is a change or a checkpoint before building the chart.

Choosing and preparing the source data

Keep the source simple and unambiguous. A waterfall chart usually needs one amount column, not separate columns for increases and decreases. Store decreases as negative numbers in that same column. Use text labels that explain the step, and arrange rows chronologically or in the logical order of the explanation.

Use real numeric cell values, not numbers stored as text. Currency symbols can be applied with number formatting rather than typed into the values.
Keep a consistent unit and scale throughout the series, such as dollars throughout or units throughout.
Use clear labels for endpoints and checkpoints, for example Opening balance, Quarter-end subtotal, and Closing balance.
Remove accidental blank rows and verify that formulas in the amount column return numbers.
Keep supporting notes outside the selected chart range so Excel does not mistake them for categories or data.

A running-balance column can still be useful in the worksheet as a check or for a separate report, but it is not the main series for a standard waterfall. If you select both the change and running-balance columns, Excel may create multiple series and a chart that does not communicate the intended step-by-step bridge. Select only the category labels and the amount values for the waterfall itself.

Formatting and reading the chart

After the chart is created, use labels and formatting to make the movement understandable without changing the underlying layout. Select the chart to access its chart design and formatting controls. Data labels can show the amounts on the columns, while a clear chart title and appropriately formatted vertical axis explain what the values represent. For large figures, use a consistent number format such as thousands or millions, and state the unit in the title or axis label.

Excel distinguishes increases, decreases, and totals with different default colors, but the exact appearance can depend on the workbook theme and formatting. If colors are changed, preserve an obvious visual distinction between positive changes, negative changes, and total columns. The category labels should remain readable; shorten long labels or widen the chart rather than allowing text to become crowded.

Connector lines help show how one step leads to the next. If they are available in the chart’s formatting options, they are useful when many changes appear close together. They are not a substitute for correctly marking totals. A chart can have attractive colors and labels yet still be mathematically misleading if an endpoint or subtotal is treated as a change.

Common layout mistakes and fixes

Entering the ending balance as a change: Put the actual ending value in its row and mark that data point as a total. Otherwise Excel continues the cumulative movement from the prior step.
Using positive values for decreases: Enter a decrease as a negative number. The category text alone does not tell Excel to subtract it.
Including a running-balance series: Select only the labels and the change or checkpoint amounts intended for the waterfall.
Forgetting a subtotal setting: A subtotal that should begin at zero must be marked as a total. Right-click that individual column and set it as a total.
Numbers stored as text: A value that looks numeric may not be recognized as a number. Check the cell format and correct the underlying value if the column behaves unexpectedly.
Unclear or repeated labels: Repeated categories are permitted, but readers may not know which occurrence they are seeing. Add context such as First-quarter returns and Second-quarter returns.

If a chart looks wrong, first compare the selected range with the worksheet rows, then inspect the sign of every change and the total setting on each endpoint or subtotal. This check usually identifies whether the issue is in the data layout or in the chart’s point formatting.

When a waterfall chart is the right choice

A waterfall is useful when the story is about how intermediate changes explain the difference between a starting and ending value. Examples include a change in headcount, inventory movement, a project budget bridge, or the steps that explain a change in monthly sales. It emphasizes contribution and sequence rather than simply comparing unrelated categories.

For a straightforward comparison of several independent values, a bar or column chart is usually clearer. For a trend over time with many observations, a line chart may be more suitable. A waterfall is most effective when the reader needs to see the bridge from one meaningful total to another and the number of steps remains manageable.

Frequently Asked Questions

Does Excel need a running total column for a waterfall chart?
No. For a basic waterfall, provide category labels and the individual changes in one value column. Excel calculates the cumulative positions. Add a running-balance column only if it serves another purpose, and do not include it as an extra chart series by accident.

How do I show the starting and ending values as full columns?
Right-click the first data point in the chart and choose Set as Total. Do the same for the ending point. Set intermediate subtotal points this way as well when they should be shown from the baseline.

Should decreases be entered with a minus sign?
Yes. Enter decreases as negative numbers in the amount column. Excel uses the sign of the numeric value to determine whether the step rises or falls; a label such as Expenses does not make a positive value subtract.

Can I include subtotals in an Excel waterfall chart?
Yes. Add a row containing the subtotal value and mark its chart point as a total. That makes the subtotal a checkpoint in the bridge rather than another movement added to the previous cumulative amount.

Why is my final waterfall bar too high?
The final value may be plotted as an ordinary increase instead of a total. Check that its worksheet value is the actual ending amount, then right-click that column and choose Set as Total. Also verify that the endpoint has not been included twice in the selected data.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.