Excel Waterfall Chart: How to Show the Final Total

To show a final total in an Excel waterfall chart, select the bar for that value, right-click it, and choose Set as Total. Excel anchors the bar to the chart’s baseline instead of treating its value as a change from the previous point; you can mark an opening balance or intermediate subtotal as a total the same way.

Why a total needs to be marked

A waterfall chart explains how a starting amount changes through a sequence of increases and decreases. Each ordinary bar represents a change: a positive change rises from the running balance before it, while a negative change drops from that balance. The bars therefore appear to float rather than all starting at zero.

A final balance is different. It represents the complete amount reached after adding the changes, not another change to add to the previous balance. If Excel interprets that value as an ordinary change, the last column floats above the prior running balance and the chart implies that the total should be added again. Setting the point as a total tells Excel to draw it from the baseline as a complete result.

This setting affects how the chart displays a data point; it does not change the numbers in the worksheet. The source cell for a total should still contain the actual total you want to communicate.

Prepare the data before inserting the chart

Use one column for the category labels and another for the values. List the opening balance first, then each increase or decrease in the order it occurred, and finish with the ending balance. The change values should be entered as signed amounts: increases are positive and decreases are negative. Keep the final balance as its own value rather than entering it as an additional change.

For example, if a project starts at 100, gains 40, loses 15, and gains 25, its ending balance is 150. The arithmetic is 100 + 40 − 15 + 25 = 150. A suitable source table is:

Category	Value
Starting balance	100
New sales	40
Returns	-15
Other gains	25
Ending balance	150

Use labels that make the meaning of each point clear. “Ending balance” or “Total” signals that the last bar is a result, while a vague label such as “Final” may leave readers unsure whether it is a further change. If you have multiple subtotals, include each as a separate row at the point where it belongs in the story.

Insert a waterfall chart in Excel for Microsoft 365

Select the cells containing the category labels and values, including the headers.
On the ribbon, choose Insert > Insert Waterfall, Funnel, Stock, Surface or Radar Chart > Waterfall.
Check that Excel has used the category names and values in the intended order. If the labels or series are wrong, use Chart Design > Select Data to correct the chart’s source range.
Mark the ending balance as a total using the steps in the next section. Mark the opening balance as a total too if you want it explicitly treated and styled as a total.

The chart can be moved or resized like other Excel charts. The source table remains editable: changing a value in the selected range updates the chart, although you should recheck the running arithmetic and total after editing. Excel for Mac also includes waterfall charts in supported current releases; the Insert tab and chart gallery are the equivalent starting point, though the menu layout can differ. Excel for the web may present different chart-editing controls, so use the desktop app if you cannot find the point-level total setting.

Set the ending value as a total

Click the chart, then click the ending-balance bar once to select the data series. Click that same bar again to select only that data point. Avoid double-clicking quickly if Excel opens a formatting pane before the point is selected.
Right-click the selected bar and choose Set as Total from the context menu.
Check that the bar now begins at the horizontal axis rather than floating at the height of the previous running balance.

If selecting the bar is awkward, enlarge the chart temporarily or use the chart’s data-point selection controls. Depending on the Excel interface, you can open the formatting pane for the selected data point and look for the Set as total option under its series options. The right-click command is usually the quickest route. To undo the setting, select the same point and clear Set as Total in the context menu or formatting pane.

To mark the opening value, select its bar as an individual point and apply the same command. This is particularly useful when you want the opening and closing balances to have the same total-bar treatment. It is not a substitute for checking the source values: the chart does not calculate whether the ending balance equals the start plus all the changes.

Check the example and understand the visual result

In the example, the starting balance is 100. “New sales” raises the running balance to 140, “Returns” lowers it to 125, and “Other gains” raise it to 150. The ending-balance point should be displayed as a full-height column from zero to 150. Without the total setting, Excel reads 150 as another increase from 150, so its top reaches 300; that is a charting interpretation, not a change to the worksheet value.

The chart’s connectors help readers follow the running balance between changes. The total bars provide reference points at the baseline, while the floating bars show the size and direction of individual movements. These visual roles are why it matters to distinguish a total from a change even when both are represented by numbers in the same source column.

For a chart intended to show a net result, verify that the final total is calculated independently or reconciles with the opening value and listed changes. If it does not reconcile, the chart may still display the number you entered, but the story will be misleading. Check for omitted movements, a sign entered incorrectly, or a value counted twice.

Format totals and intermediate subtotals

Excel typically uses a distinct fill for total columns compared with increase and decrease columns. You can change the appearance by selecting a point and using the chart formatting controls, or by choosing a chart style on the Chart Design tab. Keep a clear distinction between increases, decreases, and totals. If the colors are too similar, the chart may be difficult to interpret, particularly for readers who do not distinguish colors easily.

An intermediate subtotal works like a final total: select the subtotal point and choose Set as Total. For instance, a chart of quarterly results might show a subtotal at the end of each quarter, followed by changes that build toward the next subtotal. The subtotal should represent the complete balance at that point, not another movement. Label it clearly so readers understand why the next changes begin from that level.

Formatting should support the data rather than replace labels. A legend may identify the colors, but labels such as “Quarter 1 total” and “Ending balance” make the point’s role explicit. If you add data labels, check that they display the intended values and do not overlap. A bar’s visual height and its source value are not always read intuitively when the chart has many categories.

Common problems and how to fix them

The last bar floats instead of starting at zero: Select that specific data point and apply Set as Total. Selecting the whole series may not expose the point-level command.
The final bar is much taller than expected: Excel is probably treating the ending balance as another change. Mark it as a total, then compare the bar with the source value.
The command is missing: Make sure the chart is a native Waterfall chart, and that one data point—not the plot area, axis, or entire series—is selected. Excel versions and platforms can differ in the controls they expose.
Increases and decreases appear reversed: Check the signs in the source data. Enter decreases as negative values and increases as positive values; do not rely on bar color to correct a sign error.
The chart ends at the wrong amount: Recalculate the running balance from the opening value and all changes. Look for a missing category, a duplicated movement, or a final value that was not updated.
Categories appear in the wrong order: Check the source range and its row order. A waterfall chart follows the data sequence; sort or rearrange the worksheet data if the narrative order is wrong.

A frequent misconception is that marking a bar as a total will make Excel sum the prior rows for you. It does not. The chart setting changes the point’s visual treatment, while the worksheet values remain the source of truth. Calculate or verify the total in the worksheet before presenting the chart.

When to use a different chart

A waterfall chart is a good choice when the purpose is to explain how several movements create a net change between a starting and ending amount. It is less useful when the main goal is to compare unrelated categories, show a continuous trend over time, or display each item as a proportion of one whole. A bar chart is often clearer for comparing categories; a line chart is usually more appropriate for a time trend.

If you need several independent totals or a complex hierarchy of subtotals, a waterfall can still work, but the sequence must be carefully arranged and labeled. For a simple comparison of beginning and ending values, two columns may communicate the result more directly. Choose the chart based on the question the reader needs answered, not simply because the data includes positive and negative numbers.

Frequently Asked Questions

How do I show the grand total in an Excel waterfall chart?
Select the final data point, right-click it, and choose Set as Total. The total then starts at the baseline instead of floating as another change.

Can I show a subtotal in a waterfall chart?
Yes. Select the subtotal’s bar as an individual data point and choose Set as Total. The subtotal should contain the complete balance at that stage, not just the amount of the latest movement.

Why is my total bar showing the wrong height?
First check whether the point is marked as a total. Then verify the source value and the arithmetic of the opening balance plus all positive and negative changes. The total setting does not calculate or repair the value.

Can I change the color of the total bar?
Yes. Select the individual total point and use the chart formatting controls to change its fill. Keep the total visually distinct from increases and decreases so the chart remains easy to read.

Does Set as Total change my worksheet data?
No. It changes how the selected point is drawn in the chart. The cell value remains unchanged, so edit the source cell separately if the total itself is incorrect.

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.