Excel Waterfall Chart Set as Total: Steps and Fixes

To set a column as a total in an Excel waterfall chart, select that individual column, right-click it, and choose Set as Total. Excel then anchors the column to the chart’s baseline instead of treating its value as another increase or decrease in the running total.

What Set as Total means

A waterfall chart shows how successive positive and negative values move a starting amount toward an ending amount. Ordinary columns represent changes: a positive change rises from the previous running balance, while a negative change falls from it. A total column is different. It represents a complete balance at that point, so it begins at zero and extends to the balance shown by its source value.

That distinction matters because a subtotal is not another change to add to everything before it. Suppose a project starts at 100, gains 40, and then has costs of 25. The running balance after those changes is 115. If a row containing 140 represents the balance after the gain, marking it as a total tells Excel to show 140 from the baseline; it does not add 140 to the existing 140. Subsequent changes can then continue from that subtotal.

In Excel, the setting applies to a specific data point, not necessarily to every column with a label such as Total. It is a chart-formatting choice and does not alter the worksheet value, formula, or other data in the workbook.

Prepare the data and insert the chart

For a clear chart, arrange the categories in one column and the corresponding changes or balances in the next. For example:

Opening balance: 100
New sales: 40
Costs: -25
Ending balance: 115

The changes use positive and negative numbers, while the opening and ending rows contain full balance values. Select the category and value cells, including their headings if present. In Excel for Microsoft 365 on Windows, choose Insert > Insert Waterfall, Funnel, Stock, Surface or Radar Chart > Waterfall. Excel creates a chart in which the values are initially interpreted as changes unless you identify the balance columns as totals.

In this example, mark both Opening balance and Ending balance as totals. This makes each one start at the baseline. The sales and costs remain floating change columns between the two balances. If you want an intermediate subtotal, add a row for it and mark that point as a total too.

Set a column as a total

Click the waterfall chart once to select it.
Click the specific column you want to change. The first click may select the whole series; click the same column again to select only that data point. Avoid double-clicking quickly, which may open the Format Data Point pane instead.
Right-click the selected column and choose Set as Total.
Repeat for any other opening balance, subtotal, or ending balance that should be anchored to the baseline.

On a Mac, Control-click the selected data point to open its context menu; the command is also called Set as Total when available in the desktop Excel version. If the menu does not show the command, confirm that an individual data point—not the chart area, axis, or whole series—is selected. Excel for the web may expose a different set of chart-editing controls, so use desktop Excel if the point-level command is unavailable.

You can also select the data point and open the Format Data Point pane to inspect its options. The exact pane layout can vary slightly across Excel releases, but the total setting is associated with the selected point. The context menu is usually the fastest route.

Worked example: opening, subtotal, and ending balances

Consider a monthly cash movement with an opening balance of 1,000, receipts of 300, expenses of -180, and an ending balance of 1,120. The arithmetic is 1,000 + 300 – 180 = 1,120. In the chart, the opening balance is a total column from zero to 1,000; receipts rise from 1,000 to 1,300; expenses fall from 1,300 to 1,120; and the ending balance is a separate column from zero to 1,120.

The chart’s visual shape communicates two different kinds of information at once: the floating columns show movement, while the total columns show the full balance. If the ending balance is left as an ordinary change, Excel treats 1,120 as an additional increase after the expenses. The bar will then extend from 1,120 to 2,240, which is misleading even though the worksheet value itself is correct.

For a subtotal example, imagine an income statement that displays revenue, operating costs, and operating profit before later items. Put the operating-profit balance in its own row and mark it as a total. Later expense or income changes can then continue from that subtotal. Use labels that make the role of each row explicit, such as Opening balance, Operating profit, and Closing balance, rather than relying on color alone.

Calculate the ending value in the worksheet

You can type the ending balance directly or calculate it from the changes. With category labels in A2:A5, opening balance in B2, and the three subsequent movements or balance rows arranged as in the example, keep the input design simple: opening balance in B2, sales in B3, costs in B4, and ending balance in B5. With those values, enter =SUM(B2:B4) in B5. The result is 115 for 100 + 40 – 25.

Mark B5’s chart point as a total. The worksheet formula calculates the ending value; the chart setting controls how that value is drawn. These are separate jobs. Editing a number or formula updates the chart’s data, but it does not automatically change an ordinary point into a total, and setting a point as a total does not add a formula to the worksheet.

If you later add or remove movement rows, adjust the ending-balance formula so its range includes the intended values. Check that the formula does not include the ending-balance cell itself, which would create a circular reference. Also check the sign convention: expenses or other downward movements must be negative if they are meant to reduce the balance.

How to confirm the result and format it

A successfully set total starts at the horizontal baseline and reaches the value represented by that category. A regular positive or negative movement instead floats between the previous and new running balances. Compare the position of the bar with the neighboring connectors and with the values in the worksheet. If a total appears to float, select it again and verify that Set as Total has been applied to that specific point.

Waterfall colors also help readers interpret the chart. Excel commonly distinguishes increases, decreases, and totals with separate colors. To change them, select a relevant point or series and use the formatting controls in the Format Data Point or Format Data Series pane. Keep total columns visually distinct from increases and decreases, and use data labels or clear axis units where exact amounts matter. Color should reinforce the meaning, not carry it by itself; labels and a sensible legend help readers who cannot distinguish the colors.

Common problems and how to fix them

The whole chart or series is selected. Click once to select the series, then click the intended bar again to select the individual point. The selection handles should identify one column.
Set as Total is missing. Right-click the data point itself. A menu for the plot area, axis, or chart background will not contain the point-level command.
The final bar is much too high. The ending balance is probably being treated as a change. Set it as a total so Excel anchors it to the baseline.
The total has the wrong height. Check the source cell and its formula. Excel plots the value supplied by the worksheet; the total setting does not calculate the balance for you.
A reset no longer looks right after editing data. A data point marked as a total can retain that chart-specific status when the source values change. Review the total points whenever categories are added, removed, or repurposed.
A connector or label seems confusing. Check category order, signs, and number formats. A negative cost should reduce the running balance, and a total label should match the balance intended for that stage.

One common misconception is that every row named Total will automatically be displayed as a total. The label is only text; the chart’s point setting determines whether Excel treats the value as a balance or a movement. The opposite is true as well: a point can be set as a total even if its category label uses another name. Clear labels and correct point settings work together.

When to use a total instead of a change

Set a point as a total when its value is a complete level or balance, such as an opening balance, a subtotal, or an ending balance. Leave the point as a change when its value describes movement since the previous stage, such as units sold, money spent, headcount added, or a month-to-month variance.

A useful test is to ask whether the value should be added to the running result. If the row says the current balance is 500, treating 500 as a change would add it again and distort the story; it should generally be a total. If the row says the balance increased by 500, it is a change. A label like Net income can mean either a period’s change or a cumulative balance depending on the data design, so define what the value measures before formatting the chart.

Waterfall charts are particularly helpful when readers need to see the bridge between two balances: where a starting figure came from, which factors moved it, and how the final figure was reached. They are less suitable when the main purpose is comparing many independent categories side by side. A bar or column chart may be clearer for that comparison because every bar shares the same baseline.

Frequently Asked Questions

How do I make the final bar a total in Excel?
Select the final bar as an individual data point, right-click it, and choose Set as Total. If the bar remains floating, check that you selected the point rather than the series.

Can I set more than one waterfall column as a total?
Yes. Set opening balances, intermediate subtotals, and ending balances as totals individually. Other columns can remain increases or decreases.

Does Set as Total change the worksheet value?
No. It changes how that data point is displayed in the chart. The source cell and any formula in it remain unchanged.

Why is my total bar floating instead of starting at zero?
It is probably still formatted as a change, or a different point was selected when you applied the command. Select the exact bar and apply Set as Total again.

Can I use a formula for the final total?
Yes. Calculate the balance in a worksheet cell using the opening value and the intended positive and negative changes, then use that cell as the chart’s source. The formula supplies the number; the chart setting identifies it 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.