Excel Waterfall Chart End Value: Set the Final Total

To make the last bar in an Excel waterfall chart show the ending balance, right-click that bar and choose Set as Total. The bar will then start at the chart’s baseline and represent the value you entered for that final data point, rather than another change added to the running total.

What the end value means in a waterfall chart

A waterfall chart explains how a starting amount changes through a sequence of increases and decreases. Each intermediate bar represents a movement: a positive value raises the running total, while a negative value lowers it. The vertical position of each step shows the balance after that movement.

The end value is usually the final balance after all the listed movements. It is not automatically calculated just because a category is named Ending balance or Final total. Excel plots the values in your source data, so the final row needs to contain the correct ending amount. You then tell Excel to draw that point as a total.

This distinction matters because a waterfall chart normally treats every source value as a change. If your data contains a starting balance of 100, an increase of 25, a decrease of 10, and a final balance of 115, Excel can treat the last 115 as another increase unless you mark it as a total. The resulting last step would be misleading. Setting the final point as a total displays 115 from the baseline instead.

Prepare the source data

Keep the categories in one column and the chart values in the next. Use positive numbers for increases and negative numbers for decreases. Include a separate row for the ending balance if you want the chart to show a closing bar.

Opening balance: 100
New sales: 25
Returns: -10
Closing balance: 115

The closing balance is the opening balance plus the changes: 100 + 25 – 10 = 115. If the values are in B2:B4, with the opening balance in B2 and movements in B3:B4, enter =SUM(B2:B4) in B5. The result is 115. Put the category names in A2:A5 and select A1:B5 if the first row contains column headings.

Using a formula for the closing balance can reduce mistakes when the movements change. It does not, by itself, make Excel treat that cell as a total in the chart. The chart setting and the calculation are separate: the formula supplies the number, and Set as Total changes how Excel draws that point.

Create the waterfall chart in Excel

In Excel for Microsoft 365 on Windows, select the category and value range, then choose Insert > Insert Waterfall, Funnel, Stock, Surface or Radar Chart > Waterfall. Excel creates a chart using the selected rows as categories and the numeric column as the series. If your range includes headings, Excel generally uses them to label the chart and series.

Check that the steps appear in the intended order. Waterfall charts use the source row order, so sort or rearrange the source table before charting if the sequence is wrong. The chart does not normally reorder changes by size or determine a business process sequence for you.

If you already have the chart, click it and then click the final bar once to select the series and a second time if needed to select only that individual data point. Right-click the selected final bar and choose Set as Total. The point should become a full-height bar from the baseline, while the preceding positive and negative bars remain floating steps.

Set the final bar as a total

Be precise about selecting the point. A click on the chart background or an axis opens options for the chart or axis, not the data point. When the correct bar is selected, the context menu includes Set as Total. In the Format Data Point pane, the equivalent option is a checkbox labeled Set as total. The precise pane layout can vary slightly with the Office interface, but the setting applies to the selected point.

Click the final bar in the waterfall chart. If the entire series is selected, click the same bar again to select just that point.
Right-click the bar and select Set as Total, or open Format Data Point and select its Set as total checkbox.
Check that the bar begins at the baseline and that its height corresponds to the final value in your source data.
Repeat for any other point that should be displayed as a standalone subtotal or total.

Excel’s total setting changes the chart’s interpretation of that individual point; it does not rewrite the worksheet value or insert a formula. If the ending balance is incorrect, correct the source data or its formula as well. A visually correct total bar can still show an incorrect amount if its underlying value is wrong.

Show the end value as a label

A bar’s height gives a visual comparison, but readers may want to see the exact ending number. Add data labels to the chart and make sure the final point’s label is visible. Select the chart, then use Chart Design > Add Chart Element > Data Labels and choose a placement. Depending on the chart and Excel layout, the chart’s plus button can also provide chart-element options.

If labels on every step make the chart crowded, select an individual bar and add or format a label for that point rather than displaying labels everywhere. You can also retain labels for all points but adjust their position, number format, or font size. Check that a negative step is not confused with a positive one and that the final label is not clipped at the chart edge.

For currency, percentages, or large values, format the worksheet cells or the data labels to use a consistent number format. The label should communicate the same unit as the source data. For example, a chart of thousands of dollars should not silently display one bar in dollars and another in thousands. Avoid changing only the visible label text to a value that no longer matches the plotted point.

Totals, subtotals, and running balances

A total point and a change point play different roles. A change starts from the current running balance and moves it up or down. A total is drawn from the baseline to its own value. That is why both a closing balance and a meaningful intermediate subtotal can be marked as totals.

For example, a monthly cash-flow chart might show an opening balance, several receipts and payments, a month-end balance, and then another set of movements for the next month. Marking the month-end balance as a total makes it easier to see the checkpoint before the next group of changes. The following movement should still be entered as a change, not as the new balance, unless the data structure and chart are designed to represent a separate total.

Do not mark every bar as a total. Doing so removes the step-by-step visual explanation that makes a waterfall chart useful. Use the setting only for points that represent a complete balance or subtotal, not for ordinary additions and deductions.

Common end-value problems and fixes

The last bar is too tall. Check whether the ending balance is being added as another movement. Set that point as a total, and verify that the source value is the actual closing balance rather than a cumulative figure that should not be added again.
The bar floats instead of starting at zero. The point may not be marked as a total. Select the individual bar and apply Set as Total.
The total is visually right but numerically wrong. Inspect the source cell and its formula. Excel’s chart setting does not calculate the end balance for you.
The total label is missing. Turn on data labels and check whether the final point’s label is enabled, outside the plot area, or obscured by the chart edge.
The chart changes after editing data. Confirm that the chart range includes the relevant rows, that the final category still has the intended value, and that the correct point is set as a total. If you add or reorder categories, inspect the chart again rather than assuming the visual role of every point is unchanged.

When a chart is built from a changing data range, test it after adding a new period or movement. The categories, formulas, and total designation should still agree. A sensible worksheet check is to compare the displayed closing balance with the sum of the opening balance and all movements.

When to use a different chart or layout

A waterfall chart is a good fit when the reader needs to see how individual changes bridge a starting value to an ending one. It is less suitable when the main task is comparing many unrelated totals, tracking a continuous trend over time, or showing a composition at one point in time. A column chart can make direct category comparisons simpler; a line chart is often clearer for a sequence of regularly spaced measurements.

If your purpose is only to show a start and end value with no meaningful intermediate movements, a simple column chart may be easier to read. If the movements matter, keep the waterfall and make the final bar a total. The choice is about the question the graphic should answer, not merely which chart style looks more decorative.

Frequently Asked Questions

Why is my Excel waterfall chart end value being added twice?
Excel is probably treating the ending balance as another change. Right-click the final data point and choose Set as Total. Also check that the source row contains the closing balance itself, not a number already combined with a prior total.

Does Excel calculate the final waterfall value automatically?
No. Excel plots the value in the source data. Calculate the ending balance in the worksheet, for example by adding the opening balance and the movements, then mark its chart point as a total.

Can I make an intermediate waterfall bar a total?
Yes. Select the point that represents a subtotal or checkpoint and choose Set as Total. Its bar will start at the baseline, which distinguishes it from the changes around it.

How do I show the exact ending number on the chart?
Add a data label through Chart Design > Add Chart Element > Data Labels, then ensure the final point’s label is enabled and readable. You can format the number to match the units used in the source data.

Will the final bar remain a total if I change the worksheet value?
Changing the value normally changes the plotted amount, while the point’s total setting controls how it is drawn. If you add, remove, or reorder chart categories, review the chart and confirm the intended final point is still marked 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.