Excel Waterfall Chart Total Bar: Set It Correctly

To make a bar in an Excel waterfall chart represent a total, right-click that data point and choose Set as Total. Excel for Microsoft 365 on Windows then anchors the column to the value-axis baseline instead of treating it as another change that continues the running total.

What a total bar means in a waterfall chart

A waterfall chart shows how a starting amount changes through a sequence of increases and decreases to reach an ending amount. Ordinary bars represent changes: a positive value raises the running total, while a negative value lowers it. They appear to float between the previous and new cumulative values. A total bar represents the full value at that point, so it rises from or falls toward the chart’s value-axis baseline rather than floating as an increment.

This distinction matters because a total is not the same thing as a large positive change. If you enter 110 as the last row after a sequence that already reaches 110, Excel will add 110 to the running balance unless you mark that point as a total. The chart could then show a final value of 220, even though the intended ending amount is 110. Setting the point as a total changes how the chart displays and calculates the sequence; it does not edit the source cell.

People often use total bars for opening balances, intermediate subtotals, and closing balances. For example, a business might show revenue at the start of a period, then the effects of price changes, returns, and new sales, followed by a final revenue total. A marked subtotal can also establish a new reference point for the changes that follow.

Example: opening balance, changes, and ending total

Suppose a chart tracks a balance that starts at 100, increases by 30, decreases by 20, and ends at 110. The data values are 100, 30, -20, and 110. The arithmetic is 100 + 30 – 20 = 110. In the chart, the opening 100 and final 110 should be shown as totals; +30 and -20 should remain changes.

If the final 110 is left as an ordinary change, Excel interprets it as an additional increase, taking the running value from 110 to 220. Marking the final point as a total tells the chart to show 110 as the ending balance instead. This is why the total-bar setting is essential when the source table includes a final value alongside the changes that led to it.

A useful visual check is to follow the tops and bottoms of the floating bars. The first increase should begin at 100 and finish at 130. The decrease should then run from 130 down to 110. The ending total should extend from the baseline to 110. If the intermediate bars do not connect in that sequence, check the source values and which points are marked as totals.

Set a data point as a total in Excel 365 for Windows

First create a waterfall chart from a two-column table containing category labels and numeric values. Select the table, then choose Insert > Insert Waterfall, Funnel, Stock, Surface or Radar Chart > Waterfall. After Excel inserts the chart, set the point or points that represent totals:

Click the chart once to select it, then click the specific column you want to change. If the first click selects the whole series, click that same column again to select just the data point. Avoid double-clicking quickly if it opens the formatting pane before the point is selected.
Right-click the selected column. In the context menu, choose Set as Total.
Repeat for every opening balance, ending balance, or intermediate subtotal that should be anchored to the baseline.
Check the chart against the arithmetic in your table. Confirm that the changes between totals build to the intended next balance.

Excel’s selection can be a little particular: clicking a column once may select every column in the series, while a second click on that column selects the individual point. If the menu does not show Set as Total, click the chart and then the column again, and make sure you have selected a data point rather than the plot area or a data label.

On Excel for Mac, waterfall charts are available in supported current desktop versions, though the ribbon and context-menu details can vary by release. Select the individual column and look for the same Set as Total command in its context menu or formatting controls. Control-click or a two-finger click can open a context menu when there is no separate right mouse button. Excel for the web may not expose every chart-formatting control; use the desktop app if you cannot find the command.

Prepare the data so the chart tells the right story

A simple source table has one column of step names and one column of values. Enter changes as signed numbers: increases are positive and decreases are negative. Include a separate row for an opening or ending balance when you want it displayed as a total bar. For example, labels might be Opening balance, New sales, Returns, and Closing balance, with values 100, 30, -20, and 110.

Keep the rows in the order you want readers to follow. A waterfall chart follows the order of its categories; it does not automatically sort steps into a meaningful sequence. Use descriptive labels such as “Returns” or “Operating costs” rather than labels that make a negative change look like a total. Check that numbers are stored as numbers, not text, and that decreases carry a minus sign. A value entered as positive by mistake changes the running total in the opposite direction.

For a monthly report, you might include January total, February change, March change, and quarter-end total. Mark the January and quarter-end points as totals. If you want a February month-end subtotal followed by March changes, include the February subtotal as its own row and mark it as a total too. That makes the chart’s checkpoints explicit rather than asking readers to infer them from a sequence of floating bars.

Format total bars and make them easy to read

After setting the totals, improve the chart’s readability without changing the underlying meaning. Add a concise chart title, use category labels that fit, and show data labels if readers need exact values. With the chart selected in Excel for Microsoft 365 on Windows, use Chart Design > Add Chart Element to find chart elements such as data labels and axis titles. The exact best placement depends on how many categories and labels the chart contains.

Use consistent colors to distinguish increases, decreases, and totals, but do not rely on color alone. Labels, a legend, or a short note can help readers who print in grayscale or have difficulty distinguishing colors. If a total column looks like it is floating, first verify that the point is marked as a total; changing its fill color will not correct its position. If the total is clipped, inspect the value-axis bounds and the chart’s available space.

Data labels deserve a quick arithmetic check. A label on a change bar should show that row’s change, not necessarily the cumulative amount at the top of the bar. The chart’s vertical position communicates the running balance, while the source row and data label typically communicate the step value. If readers need both, explain the distinction or add a clearly labeled cumulative-value column to the source data rather than assuming the bar label shows both numbers.

Common total-bar problems and fixes

The final bar is much too tall: The ending balance may still be treated as a positive change. Select that individual point and choose Set as Total.
A decrease goes upward: Check that the source value is negative. A chart cannot infer that a category named “Costs” should reduce the balance.
A bar does not connect to the previous step: Check the row order, signs, and whether an intermediate point was marked as a total. A total resets the running reference for later changes.
The total begins below the visible chart area: The value axis may have custom minimum or maximum bounds. Review the axis scale and chart size so the baseline and columns are visible.
The wrong bar changes when you choose the command: Select the individual data point rather than the series. Click once to select the series and again on the intended column to select that point.
The chart looks right but the source total is wrong: The total setting only changes chart interpretation. It does not calculate or validate your worksheet values, so check the arithmetic in the cells separately.

Total bars compared with ordinary changes

The key choice is whether a row describes a value at a checkpoint or a movement between checkpoints. Use a total for a full balance, such as beginning cash, quarter-end expenses to date, or ending inventory. Use an ordinary change for an amount that should be added to or subtracted from the prior running value, such as cash received, a cost, or a monthly increase.

A subtotal is also a total bar, even if the chart continues after it. Marking the subtotal tells Excel to anchor that point as a complete value; subsequent changes then build from that checkpoint. This is different from inserting a row that merely repeats the subtotal as an ordinary positive number, which would add it again. When a chart contains several stages, identify each checkpoint in the labels so readers can distinguish it from a change.

When to use another chart or layout

A waterfall is useful when the path from one value to another is important: for example, explaining why a forecast changed, how a balance moved, or how components contribute to a final result. If the main purpose is to compare independent category values rather than show a running sequence, a bar or column chart may be clearer. If readers need to see a trend over time, a line chart may communicate that more directly.

You can also create a waterfall-style display manually with a stacked column chart and helper values, but that approach requires extra setup and maintenance. Excel’s built-in waterfall chart is usually simpler for a straightforward sequence of increases, decreases, and totals. A manual construction may be justified when you need a layout or behavior the built-in chart does not provide, but it is easier to make the helper calculations inconsistent with the source data.

Frequently Asked Questions

How do I make the last bar in an Excel waterfall chart a total?
Select the last column as an individual data point, right-click it, and choose Set as Total. Confirm that its source value is the ending balance, not another change to add.

Can I set more than one bar as a total?
Yes. Mark each opening balance, subtotal, or ending balance separately. Intermediate total bars are useful when the chart has distinct stages or checkpoints.

Does Set as Total change the number in my worksheet?
No. It changes how Excel treats that chart point. Your worksheet values remain as entered, so use a cell formula or another appropriate calculation if you need Excel to compute the total in the source table.

Why does my final bar show the wrong total?
Check whether the ending point is marked as a total, whether the source changes have the correct signs, and whether the rows are in the intended order. Then verify the arithmetic independently of the chart.

Can a subtotal be a total bar if more changes come afterward?
Yes. Mark the subtotal as a total to show the checkpoint, then keep the later increases and decreases as ordinary change bars. This makes the next stage easier to read without adding the subtotal twice.

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.