Excel Waterfall Chart Subtotals: How to Set Them

To show a subtotal in an Excel waterfall chart, select its bar, right-click, and choose Set as Total. Enter the subtotal as the running balance at that point—not just the change since the previous row—so Excel can draw it from the baseline and start the next change from the right level.

What a subtotal means in a waterfall chart

A waterfall chart illustrates how a starting amount changes through a sequence of increases and decreases. Each ordinary bar represents a change: positive values rise from the current running level, while negative values fall from it. Excel calculates that running level as it moves through the rows in the chart’s source data.

A subtotal is different from an ordinary change. It reports the accumulated result so far, such as a department’s total expenses before tax or a project’s balance at the end of a phase. In the chart, a subtotal should usually extend from the chart’s zero baseline to that accumulated amount. The following change then continues from the subtotal.

Excel does not reliably infer from a label such as Subtotal that a row is a subtotal. By default, it treats the row’s value as another change in the sequence. You need to tell the chart which data points represent totals. This is the key distinction: the worksheet holds the subtotal amount, and the chart’s Set as Total option controls how that amount is drawn.

How to set a subtotal bar in Excel

In Excel for Microsoft 365 on Windows, create a waterfall chart from category labels and values, then mark each subtotal point individually:

Select the cells containing the category names and their corresponding values. Include the column headings if you have them.
Choose Insert > Insert Waterfall, Funnel, Stock, Surface or Radar Chart > Waterfall.
In the chart, click the subtotal bar. If the first click selects every bar in the series, click the same bar again to select just that data point.
Right-click the selected bar and choose Set as Total.
Repeat for any other intermediate subtotals and for a final total if you want it shown as a total bar.

You can also select the point, open Format Data Point, and turn on Set as total in the formatting pane. The context-menu command is often quickest; the pane is useful when you want to verify the point’s setting or adjust other point formatting. On a Mac, use the equivalent Waterfall chart option and Control-click or right-click the point to open its context menu. Ribbon wording and pane layout can vary slightly by Excel release.

To undo the treatment, select the point and clear Set as total in the pane, or use the context-menu setting if it is available. Check the chart after changing it: the point should return to behaving like a change rather than an anchored total.

Arrange the source data for subtotals

Keep one event or balance per row, with a clear label in one column and the corresponding numeric value in another. List events in the order they occur. Use positive numbers for increases and negative numbers for decreases. A subtotal row should contain the cumulative balance at that stage, not a repeat of the changes that produced it.

For example, a sequence might contain an opening balance, sales, returns, expenses, a subtotal, tax, and a closing balance. The subtotal value must include the opening balance and all changes up to that row. After you mark the subtotal as a total, Excel uses it as the new running level for the later tax bar.

A subtotal can be calculated in the worksheet so it updates when underlying values change. With labels in A2:A8, the opening balance and changes in B2:B5, and the subtotal in B6, enter =SUM(B2:B5) in B6. If those four values are 100, 40, -10, and -5, the result is 125. The formula calculates the balance; it does not automatically tell the chart that B6 is a total. Set the corresponding chart point as a total separately.

Keep subtotal rows in the chart’s source range. If you build a chart from a range that skips the subtotal, Excel cannot display that checkpoint. If you add rows later, check whether the chart’s data range includes them; a correctly formatted subtotal outside the plotted range will not appear.

Worked example: a subtotal and closing balance

Suppose a report starts with 100, adds 40 in sales, subtracts 10 in returns, and subtracts 5 in costs. The balance before tax is 125. Tax reduces it by 10, leaving a closing balance of 115. The source values, in order, are 100, 40, -10, -5, 125, -10, and 115.

In the chart, set both the 125 subtotal and the 115 closing balance as totals. The first bar begins at zero and reaches 100; sales, returns, and costs then move the running level to 140, 130, and 125. The subtotal bar is drawn from zero to 125. Tax then drops the level to 115, and the closing-balance bar is drawn from zero to 115. The subtotal and closing bars therefore communicate balances, while the other bars communicate changes.

If you leave the 125 subtotal as an ordinary change, Excel adds 125 to the existing running level of 125, producing 250 before tax. That is why an apparently reasonable subtotal row can make the rest of the waterfall look dramatically wrong. Marking it as a total does not recalculate the worksheet cell; it changes the chart’s interpretation of that point.

This example also shows why a final total may need attention. Do not assume that a row named Closing balance or Final total will automatically be treated as a total. Select it and use Set as Total if the bar should start at the baseline rather than act as one more increase or decrease.

Format and check subtotal bars

After setting totals, use formatting to help readers distinguish balances from movements. Excel waterfall charts commonly use separate colors for increases, decreases, and totals. Choose colors with strong contrast and keep their meaning consistent across charts in the same report. Labels such as Subtotal before tax can help, but color and labeling should support—not replace—the correct total setting.

Data labels can make key amounts easier to read, especially when a chart contains several small changes. Select the chart and use Chart Design > Add Chart Element > Data Labels to add labels, then choose a placement that does not obscure bars or category names. Available placement choices can depend on chart size and Excel version. If labels overlap, enlarge the chart, shorten category text, or show labels only for important points rather than forcing every value into a crowded plot.

Check the chart against the worksheet arithmetic. Starting from the opening amount, add each positive change and subtract each negative change. The running amount immediately before a subtotal should equal the subtotal value. After the subtotal, apply later changes and confirm that the final balance matches the closing total. This quick reconciliation catches incorrect signs, omitted rows, and subtotal values entered at the wrong stage.

Common subtotal problems and fixes

The subtotal bar is much too tall. It may be treated as a change and added to the running balance. Select the point and set it as a total.
The next bar starts from an unexpected level. Check the subtotal’s value, the sign of each change, and whether the subtotal point is marked as a total. Also verify that the chart includes the intended rows in the right order.
The subtotal seems to disappear or sit at zero. Confirm that its source cell contains a numeric value, not text that looks like a number, and that the chart uses the intended value column.
The wrong bar changes when you choose Set as Total. The whole series may be selected instead of one point. Click the bar a second time to select the individual data point, then check the category label before changing it.
The total does not match the worksheet. Reconcile the running total manually and inspect signs, formulas, and hidden or excluded data. A chart’s total setting affects its display; it cannot fix an incorrect source value.

When a chart is copied, edited, or refreshed, inspect its subtotal points again. The data point’s total setting is part of chart formatting, while the numeric subtotal is part of the worksheet data; changing one does not necessarily change the other. Keeping those two responsibilities separate makes troubleshooting much easier.

When a waterfall chart is the right choice

A waterfall is useful when the reader needs to understand how separate movements lead from one balance to another. Common examples include a budget moving from planned amount to actual amount, a project schedule showing additions and deductions, or a sales report bridging one period’s revenue to another. Subtotals divide a long bridge into meaningful stages, such as revenue before costs and the balance after costs.

A regular column chart is usually clearer when the goal is simply to compare independent values, because its bars all use a common baseline. A waterfall is more informative when the sequence and cumulative effect matter. If you have many categories, several subtotal rows, or long labels, consider splitting the explanation into more than one chart; a single dense waterfall can be hard to read even when its arithmetic is correct.

Frequently Asked Questions

Why is my subtotal being added twice in an Excel waterfall chart?
If the subtotal is left as an ordinary data point, Excel treats its value as another change and adds it to the running balance. Set that point as a total so it shows the cumulative amount rather than adding the amount again.

Should a subtotal row contain the subtotal or just the period’s change?
For a bar that represents the subtotal balance, enter the cumulative amount at that point and mark the point as a total. Enter only the period’s change when you want the bar to represent a movement, not a balance.

Do I need to set the final bar as a total too?
Set it as a total when the final bar represents the ending balance and should start at the baseline. If the last row represents a final increase or decrease instead, leave it as a change.

Can a waterfall chart have more than one subtotal?
Yes. Set each intermediate subtotal point as a total. Each one can mark a useful checkpoint, and subsequent changes continue from the balance represented by that point.

Does changing the subtotal setting change the value in my worksheet?
No. It changes how Excel draws and interprets that chart point. The worksheet value and any formula in the cell remain unchanged, so update the source data separately if the amount itself is wrong.

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.