Excel Waterfall Chart: Add and Format a Subtotal Bar

To add a subtotal to an Excel waterfall chart, include a row containing the cumulative subtotal in the chart’s source data, then right-click that bar and choose Set as Total. Excel will anchor the bar to the chart baseline instead of treating the subtotal as another increase or decrease.

What a subtotal means in a waterfall chart

A waterfall chart shows how a starting amount changes through a sequence of positive and negative values to reach an ending amount. Ordinary bars represent changes: a positive value rises from the previous running balance, while a negative value falls from it. A total bar, by contrast, starts at the baseline and shows the full balance at that point.

This distinction is why adding a subtotal takes two actions. You enter the subtotal value in the worksheet, and you tell Excel to display that specific chart point as a total. Excel does not reliably infer that a row called “Subtotal” is a subtotal just from its label. Without the total setting, it interprets the number as a change, which can make the next bars start from the wrong level.

A chart subtotal is also not the same thing as an Excel worksheet subtotal. The SUBTOTAL function calculates values in a range, often while accounting for filtered rows. A waterfall subtotal is a chart data point displayed as a baseline-anchored balance. You can calculate the number with a worksheet function if appropriate, but you must still mark the corresponding chart point as a total.

Prepare the source data

For a straightforward waterfall, arrange the information in two adjacent columns: category labels and chart values. Put the opening balance first, list the movements in order, add a row for each subtotal you want to show, and finish with the ending balance if the chart needs one. Keep the chart’s intended sequence visible in the worksheet; changing the order changes the story the waterfall tells.

For example, use these values in A2:B8, with labels in column A and amounts in column B:

Opening balance: 100
Product sales: 40
Operating costs: -25
Q1 subtotal: 115
Other income: 20
Tax: -10
Closing balance: 125

The subtotal is 115 because 100 + 40 – 25 = 115. The closing balance is 125 because 115 + 20 – 10 = 125. The subtotal and closing rows contain balances, not new movements. That is the key data-entry rule: use the cumulative amount for a total row, rather than entering zero or repeating only the latest change.

Insert the waterfall chart in Excel

In Excel for Microsoft 365 on Windows, select the category and value columns, including their headers if present. Then choose Insert > Insert Waterfall, Funnel, Stock, Surface or Radar Chart > Waterfall. Excel creates a chart from the selected categories and numbers. If your selection includes a heading row, Excel generally uses it to identify the value series.

To use the example, select A1:B8 if row 1 contains the headings, then insert the chart. The chart may initially treat every row as a change, including Opening balance, Q1 subtotal, and Closing balance. That first rendering is normal: you still need to designate the balance rows as totals.

If you have already made a chart and added a new subtotal row afterward, check whether the chart includes that row. Select the chart and inspect the highlighted source range on the worksheet, or use Chart Design > Select Data to review it. Expand the source range if the new category and value are outside it.

Mark the subtotal bar as a total

Click the chart once, then click the specific subtotal bar so that only that data point is selected. Clicking a second time may be needed to select one point rather than the whole series.
Right-click the selected bar and choose Set as Total. In some Excel builds, the option is available in the point’s formatting controls rather than directly on the context menu.
Check that the subtotal bar now begins at the baseline and reaches the height represented by its full value.
Repeat for the opening and closing balance bars if those should also appear as totals.

For the example, mark Opening balance, Q1 subtotal, and Closing balance as totals. Leave Product sales, Operating costs, Other income, and Tax as ordinary changes. The changes after Q1 should then continue from 115, not from a value produced by adding 115 as if it were another movement.

On a Mac, the same chart logic applies in Excel for Microsoft 365. Use a right-click or Control-click to open the point’s context menu; menu wording and placement can differ slightly by release. Excel for the web may not expose every chart-formatting command in the same way. If you cannot find the total-point command there, open the workbook in the desktop Excel app, set the point as a total, and then save the workbook.

Calculate subtotal values correctly

For a small chart, you can enter subtotal balances directly. Formulas are useful when the movements change regularly, because they keep the displayed balance consistent with the source values. In the example layout, A2:A8 holds the labels and B2:B8 holds the chart values; B2 is 100, B3 is 40, and B4 is -25. Enter this formula in B5 for the Q1 subtotal: =SUM(B2:B4). It returns 115.

In the same layout, where B5 is 115, B6 is 20, and B7 is -10, enter this formula in B8 for the closing balance: =SUM(B5:B7). It returns 125. These formulas calculate the values, but they do not tell the chart to display B5 or B8 as a total. Set those data points as totals separately. If you insert rows or change which movements belong to a subtotal, review the formula ranges as well as the chart settings.

A useful check is to reconcile each balance manually: previous total plus the intervening increases and decreases should equal the next subtotal. If the chart looks mathematically plausible but does not reconcile, inspect the sign of each movement and the subtotal formula before adjusting the formatting.

Format and label subtotal bars

Once the chart structure is correct, improve readability with a descriptive chart title and category names that fit on the horizontal axis. Select the chart and use Chart Design options to add chart elements such as data labels or axis titles. You can also select a single bar and change its fill using the chart formatting controls. Keep subtotal and final-balance bars visually consistent so readers can distinguish balances from movements.

Data labels can help when the exact balance matters, but too many labels can crowd a chart with many categories. Consider labeling just the opening balance, key subtotals, and ending balance, or use a table beside the chart for precise amounts. Avoid relying on color alone to communicate meaning: labels and the bar’s baseline position should make the distinction understandable to readers who cannot distinguish the colors.

Remember that a subtotal bar is a total point, not a special chart category with automatic formatting rules. You can choose its appearance, but the important functional setting is Set as Total. A custom color or a category label containing the word “subtotal” does not replace that setting.

Common problems and how to fix them

The subtotal shoots upward as a large increase. Excel is treating the cumulative balance as a change. Select that individual point and set it as a total.
The next movement starts from the wrong height. Check that the subtotal value is the full running balance and that the subtotal point is marked as a total. Also verify that later increases are positive and decreases are negative.
The bar will not select by itself. Click the chart, then click the target bar again. If the entire series is selected, try right-clicking directly on the bar and confirm the point name in the formatting pane before changing it.
The subtotal row is missing. The chart’s source range may not include the row. Expand the data range through Chart Design > Select Data, or recreate the chart from the complete range.
The total bar appears to have no height. Check that its value is not blank or zero and that it represents the intended cumulative balance. A total equal to zero may sit on the baseline and look like no bar.
The first or last bar behaves like a movement. Mark the opening and ending balances as totals when they represent full balances. Excel does not automatically understand every row’s accounting role.

When troubleshooting, begin with the worksheet values and category order, then check which points are set as totals. Changing colors or labels before confirming those basics can make a chart look polished while leaving its arithmetic misleading.

Subtotal bars versus separate charts or formulas

For a single sequence of changes, a native waterfall chart with marked subtotal points is usually the clearest choice: the reader can see both the movements and the balance at an intermediate checkpoint. A regular column chart is less suitable when the goal is to show how positive and negative changes bridge from one balance to another, because its columns normally share a common baseline.

If you only need a subtotal number in a report and do not need to show the bridge of movements, a worksheet calculation or a small summary table may be simpler than a waterfall chart. If you need several independent scenarios, separate waterfall charts can be easier to read than combining unrelated sequences into one chart. Choose based on the question the graphic must answer, not just on which chart type looks more elaborate.

Frequently Asked Questions

Can Excel add a subtotal automatically to a waterfall chart?
Excel can chart a row that you provide, but it does not infer a subtotal simply because the label says “Subtotal.” Enter the cumulative balance, then set that data point as a total.

Should a subtotal row contain the change or the running balance?
It should contain the cumulative balance at that checkpoint. In the example, the Q1 subtotal is 115, while the separate movements are +40 and -25.

Do I need to set the final bar as a total?
If the last row represents the ending balance, yes, set it as a total so it rises from the baseline. If it represents another increase or decrease, leave it as a movement.

Why does my subtotal still look like an increase?
The point is probably still configured as a change, or the source value is a movement rather than a cumulative balance. Confirm both the cell value and the point’s Set as Total setting.

Does the SUBTOTAL function create a waterfall subtotal bar?
No. SUBTOTAL can calculate a worksheet value, but it does not set chart behavior. Include the calculated result in the chart data and mark its point as a total.

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.