Excel Waterfall Chart: How to Set the Last Bar as a Total

To make the last bar in an Excel waterfall chart show the final total, right-click that bar and choose Set as Total. Excel then anchors it to the chart’s baseline instead of treating its value as another increase or decrease in the running total.

What the last bar means in a waterfall chart

A waterfall chart shows how a starting amount changes through a sequence of additions and deductions before reaching an ending amount. The intermediate bars represent changes; the first and final bars usually represent full totals. Connector lines help show how each change moves the running value from one step to the next.

The distinction matters because a total and a change are interpreted differently. A change begins at the current running value and moves up or down by its amount. A total begins at the axis baseline and reaches the full value. If Excel interprets an ending balance of 115 as a change of positive 115, rather than as a total of 115, the chart can imply a final amount of 230. Marking the point as a total tells Excel what the number means.

Excel often identifies the first and last points as totals automatically, but that behavior can depend on the chart and the data. Check the actual bars rather than relying on the default. If the last bar floats above the baseline or appears to add to the preceding steps, set it explicitly as a total.

Set the last bar as a total in Excel

In Excel for Microsoft 365 on Windows, create the waterfall chart and then set the ending data point as a total:

Arrange the category labels and values in two columns. Include the final total as the last row, after the positive and negative changes.
Select the data and choose Insert > Insert Waterfall, Funnel, Stock, Surface or Radar Chart > Waterfall.
Click the last bar once to select the series. Click that same bar again to select only the individual data point. Avoid double-clicking quickly, which may open formatting options instead.
Right-click the selected last bar and choose Set as Total.
Check that the bar now starts at the horizontal axis baseline and that the preceding connector leads to the correct ending level.

If the menu item is not visible, make sure you have selected the individual bar rather than the whole series or the plot area. You can also open Format Data Point from the context menu and look under Series Options for the total setting. The precise pane layout can vary slightly between Excel builds.

On Excel for Mac, the same total setting is available for waterfall data points in supported desktop versions. Control-click or use a two-finger click on the individual bar to open its context menu, then choose Set as Total. If the point is difficult to select, click the series once and then the bar again before opening the menu.

A small example: from opening balance to closing balance

Suppose a report starts with cash of 100, adds 40 from sales, and subtracts 25 for expenses. The closing cash is 115. The source table should contain the amounts as shown here:

Opening cash: 100
Sales: 40
Expenses: -25
Closing cash: 115

The arithmetic is 100 + 40 – 25 = 115. In the waterfall, opening cash is a total at 100, sales raises the running value to 140, expenses lowers it to 115, and closing cash is a total at 115. If the final 115 is treated as another positive change, the running value instead rises from 115 to 230, which is not the intended story.

This example also shows why a final total should not be calculated by adding every displayed row. The closing cash row summarizes the opening amount and the changes; it is not another change to include in the running calculation. The chart uses the row’s value to draw a total once the point is marked accordingly.

Prepare the source data and check the signs

Most waterfall mistakes begin in the source table rather than in the chart formatting. Use one row for each event or category, keep the labels descriptive, and enter decreases as negative numbers. Positive numbers raise the running amount; negative numbers lower it. If expenses are entered as positive values, Excel will draw them as increases unless the source values or chart design are changed.

Before inserting the chart, verify the sequence with a calculator or a separate check in the workbook. Confirm that the starting total plus all intermediate changes equals the ending total. For a more detailed chart, the calculation should follow the order of the rows because each step is positioned relative to the running value at that point.

Use a clear label such as Closing balance for the final row, rather than a second generic label like Total.
Keep the ending value in the same units as the starting value and the changes.
Check for blank rows, text values that look like numbers, and accidental duplicate categories.
Do not include a final total among the intermediate changes when checking the arithmetic.

For example, if the table shows an opening amount of 500, changes of +80, -30, and -20, the expected ending amount is 530. A final bar labeled 530 is a summary of those steps, not a fourth change. This simple check can reveal a wrong sign or a missing adjustment before it becomes a confusing chart.

Why the last bar may look wrong

A last bar that floats rather than starting at the baseline is the most obvious sign that Excel is treating it as a change. Right-click the point and inspect whether Set as Total is enabled. If the setting is absent or the wrong bar changes, select the individual data point more carefully and try again.

Other visual problems can have different causes. A final column that starts at the baseline but has an unexpected height may be a correctly marked total based on an incorrect source value. A bar that seems to connect to the wrong step may be affected by the order of the source rows. If the connector lines are hard to see, changing colors or labels may help, but it will not correct the arithmetic.

When the chart appears inconsistent after editing its data, recheck both the source values and the total setting. Excel may keep a point’s total status as the chart’s categories change, so a point that used to be the ending total could remain marked as one after rows are rearranged. Confirm that the intended final row—not a different category—is set as the total.

Format the ending total for readability

Once the final bar is anchored correctly, make the chart easy to interpret. A distinctive color for totals can separate the opening and closing balances from the increases and decreases. Excel’s waterfall chart formatting options include separate colors for increases, decreases, and totals; select a bar or use the chart formatting controls to adjust them. Avoid relying on color alone if the chart will be printed in grayscale or viewed by people with color-vision differences.

Data labels can show the exact ending amount, but check that they do not overlap the category names or other bars. A useful chart title or subtitle should explain what the values measure and, when it is not obvious, the time period. Axis formatting also matters: currency values should use an appropriate number format, while percentages or units should be labeled clearly. Formatting improves comprehension, but it should not obscure the sequence of changes or imply a different result.

If the chart contains many categories, consider whether every small movement needs its own bar. Combining minor items into a clearly labeled category such as Other can reduce clutter, provided the grouped value is calculated correctly and the underlying detail remains available to readers who need it.

When the last bar should not be a total

Not every waterfall chart needs a final total. If the final row is another event or adjustment—such as a tax charge, a one-time cost, or a final sales increase—then it should remain a change. The ending level after that bar communicates the resulting amount. Marking an adjustment as a total would make it start at the baseline and break the running sequence.

A useful test is to ask what the final number represents. If it is the complete ending balance, revenue, headcount, or other accumulated result, set it as a total. If it is only the amount of one last movement, leave it as a change. A label such as Final adjustment is usually a clue that the row is a movement; a label such as Ending balance is usually a clue that it is a total. The meaning of the data, not its position alone, determines the right setting.

Frequently Asked Questions

Why is the last bar in my Excel waterfall chart floating?
The point is probably being displayed as a change rather than as a total. Right-click the individual final bar and choose Set as Total. If it still looks wrong, check that the source value is the intended ending amount and that you selected the correct point.

Does Excel automatically make the last waterfall bar a total?
Excel often treats the first and last data points as totals by default, but you should verify the chart. If the last point behaves like an increase or decrease, set it explicitly as a total. The appearance of the bar is a better check than assuming the default was applied.

Can I make a bar in the middle of the waterfall a total?
Yes. Select the individual point, right-click it, and choose Set as Total. This is useful for showing a subtotal or checkpoint within a longer sequence. The following changes then continue from that total, so confirm that the value and subsequent movements tell the story you intend.

How do I remove the total setting from a bar?
Right-click the point that is currently marked as a total and choose Set as Total again to clear or toggle off the setting. The wording and check mark can vary by Excel build. Then verify that the point is displayed as a change from the prior running amount.

Should the final total be added to the other values?
No. The final total summarizes the starting amount and all intervening changes. Use it to show the ending level in the chart, but do not count it as another movement when checking the arithmetic. For instance, 100 + 40 – 25 produces 115; adding the ending 115 again would double-count the result.

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.