Excel Waterfall Chart Final Column: Set It as a Total

In Excel for Microsoft 365, select the chart’s final data point, right-click it, and choose Set as Total. This makes the last column show the ending balance from the chart’s baseline instead of treating that balance as one more change to add to the running total.

What the final column means

A waterfall chart shows how a starting value changes as positive and negative amounts accumulate. Its columns therefore have two different jobs: some represent changes, while others represent totals. A change column floats between the running value before and after that change; a total column begins at the chart’s baseline and represents a complete value at that point in the sequence.

Excel does not reliably know from a label such as Ending balance or Final total that a row should be treated as a total. If the final row contains the ending balance but its point is still a change, Excel adds that number to the previous running value. The last bar can then appear far too tall. Marking the point as a total tells Excel to display the value itself, rather than add it again.

This setting changes how that data point is drawn; it does not edit the number in the worksheet. It also does not calculate the ending balance for you. Your source data should already contain the correct final value.

Set the final column as a total

Use these steps in Excel for Microsoft 365 on Windows:

Click the waterfall chart. If you have not created it yet, select the source data and choose Insert > Insert Waterfall, Funnel, Stock, Surface or Radar Chart > Waterfall.
Click the last column once to select the chart series, then click that same column again to select just that data point. Avoid double-clicking quickly, which may open a formatting pane instead of selecting the point.
Right-click the selected column and choose Set as Total.
Check that the column now starts at the baseline and shows the ending value. If it still floats, select the individual point again and verify the setting.

You can also open the Format Data Point pane for the selected column and use the Set as total option under series options. The right-click command is usually the quickest route. In Excel for Mac, the same point-level setting is available in supported current versions; use a right-click or Control-click to open the context menu. Ribbon placement and wording can vary slightly between builds.

How the running total works

Suppose a report begins with cash of 100, gains 40 from operating activity, loses 25 to expenses, and ends with cash of 115. The arithmetic is 100 + 40 − 25 = 115. In a waterfall chart, the first value establishes the opening level, the next two columns show changes, and the closing value confirms the final balance.

If the worksheet’s four values are 100, 40, -25, and 115, Excel can interpret the last 115 as another change unless you mark it as a total. In that case, the running sum would become 230, which is not the intended closing balance. Set the point for 115 as a total, and the ending bar displays 115 from the baseline. The intermediate steps remain unchanged.

This distinction matters because a waterfall chart is not simply a bar chart with labels. The vertical position of a change column communicates where the running total was before and after the change. Positive values generally rise from the previous level, and negative values fall from it. Totals show the full value at a checkpoint.

When to use a total instead of a change

Use Set as Total when a row records a complete balance or checkpoint, rather than an amount to add or subtract. Common examples include opening and closing cash, a budget subtotal, a department’s total contribution, or the balance after a group of transactions.

Leave a point as a change when its value is an increment or reduction. Revenue earned during a period, costs incurred, units added, and units removed are changes. Marking one of these as a total would make its bar start at the baseline and could conceal how it affects the running amount.

A useful check is to ask: Does this row describe an amount that changes the previous figure, or does it state the figure reached at this stage? A transaction is usually a change; a reported balance is usually a total. Some charts contain both intermediate subtotals and a final total, so the final column is not necessarily the only point that should be set as a total.

Create a clear source table

Arrange the worksheet in the same order you want readers to follow the story. A simple table might have one column for the item and one for its value: Opening balance, 100; Sales, 40; Expenses, -25; Closing balance, 115. Select both columns before inserting the chart. Use negative numbers for decreases rather than typing a minus sign only in a text label.

Keep totals distinct from movements in the labels. Names such as Opening balance and Closing balance make the intended meaning easier to understand, even though Excel still requires you to set total points explicitly. If you include a subtotal after several changes, check the arithmetic first: the subtotal should equal the running amount at that point, not just the sum of the rows since the previous subtotal unless that is what the subtotal represents.

Avoid adding a final balance to the source data and then also charting it as an ordinary transaction. That is a common reason the final column looks doubled. If the chart is meant to show changes only and not a closing-balance bar, remove the ending-balance row from the selected chart range instead of marking a different point as a total.

Format the ending bar for readability

After setting the total, make sure readers can tell it apart from increases and decreases. Excel waterfall charts distinguish increase, decrease, and total points with separate formatting categories. You can adjust their fills through the chart’s formatting controls, but choose colors that remain legible when printed or viewed by people with color-vision differences. Labels such as Closing balance can reinforce the meaning without relying on color alone.

Data labels are useful when the exact ending figure matters. Select the chart, then use Chart Design > Add Chart Element > Data Labels to add them. Check that the label for the final column is not crowded by nearby labels or clipped at the chart edge. A suitable vertical axis scale and an informative chart title also help: a chart titled Cash movement during the quarter communicates more than one titled Results.

Connector lines can help readers follow the running total from one change to the next, especially when there are many categories. Keep the chart uncluttered, though. If the sequence is short, clear category labels and distinct total styling may be enough. Formatting changes do not alter the underlying numbers or the point’s total status.

Common problems and fixes

The final bar is too tall: It may still be treated as a change. Select the individual final point, not the entire series, and choose Set as Total.
The whole series changes when you format the bar: The series rather than one point is selected. Click once to select the series, then click the desired column again before formatting.
The final bar shows the wrong amount: Check the worksheet value and the sequence of changes. Set as Total changes the chart’s interpretation of a point; it cannot correct an incorrect source value.
The final column is missing: Confirm that the final row is included in the chart’s source range. If the data was expanded after chart creation, the chart may not include the new row automatically.
The total setting seems to disappear: Confirm that you are working with a Waterfall chart and reselect the point. If you replace or rebuild the chart, inspect its total points again, since the rebuilt chart may not retain point-specific formatting.

When diagnosing an unexpected result, compare the chart with the source table one category at a time. Verify signs, order, and totals before changing colors or axis settings; visual formatting cannot repair a mistaken calculation.

Frequently Asked Questions

How do I make the last bar in an Excel waterfall chart show the ending balance?
Select the last data point, right-click it, and choose Set as Total. The bar will represent the value itself rather than another change in the running total.

Why does my waterfall chart add the final value twice?
The final balance is probably being treated as a change. Excel is adding it to the accumulated changes. Mark the final point as a total, and make sure the source value is the intended ending balance.

Can I set an intermediate column as a total?
Yes. Select the individual point for the subtotal or checkpoint and choose Set as Total. This is useful when a chart has separate sections, such as monthly movements followed by a quarter-end balance.

Does Set as Total change the worksheet value?
No. It changes the way the selected point is represented in the waterfall chart. The worksheet remains unchanged, and Excel does not recalculate a closing balance from that setting.

Can a waterfall chart have more than one total column?
Yes. You can mark multiple points as totals, such as an opening balance, an intermediate subtotal, and a final balance. Set each point according to what its source value means.

Can I do this in Excel for Mac?
Current Excel for Mac versions support setting a waterfall data point as a total. Select the individual column and use its context menu, opened with a right-click or Control-click; menu placement may differ from Windows.

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.