Excel Waterfall Chart: Make the Last Column a Total

To make the last column in an Excel waterfall chart a total, select the chart, click the final column twice to select that data point, right-click it, and choose Set as Total. Excel then anchors the column to the chart’s baseline instead of treating it as another increase or decrease in the running balance.

Why the last column may float

A waterfall chart shows how a starting value changes through a sequence of positive and negative amounts. Intermediate columns usually float: each one begins where the prior step ended, so the viewer can follow the running total. A beginning balance and an ending balance are different. They represent complete values, so their columns should start at the baseline.

Excel does not automatically know that the last row in your source data is an ending balance. It interprets each data point according to the chart’s waterfall rules, and a final value can therefore appear as a floating increment. Marking that point as a total tells the chart to display its full value from the baseline. It changes how the point is drawn, not the number stored in the worksheet.

This distinction matters even when the final number is correct. A column floating above the previous step can make a balance look like a change rather than the result of all the changes. Setting it as a total makes the chart easier to read and prevents a misleading visual impression.

Set the final data point as a total in Excel

In Excel for Microsoft 365 on Windows, use the chart’s context menu. The selection can be finicky because a click on a column may select the entire series rather than the individual point.

Click the waterfall chart once to activate it.
Click the last column once to select the series, then click that same column again to select only the final data point. Avoid double-clicking rapidly; the second click should be a separate click on the same column.
Right-click the selected column and choose Set as Total.
Check that the column now begins at the baseline. If it did not change, try selecting the individual point again and reopening the context menu.

You can also select the point and use the chart formatting controls if they are available in your Excel build: open Format Data Point and turn on Set as total. The right-click command is usually the quickest route. To reverse the change, select the point and clear the total setting, or use the context-menu option to reset it as an increment if shown.

The chart must be a native Waterfall chart for this setting to be available. To create one from a category-and-value table, select the data, then choose Insert > Insert Waterfall, Funnel, Stock, Surface or Radar Chart > Waterfall. Menu labels can vary slightly with ribbon layout and updates.

Prepare the source data correctly

A simple waterfall source table has one column of category labels and one column of values. Enter increases as positive numbers and decreases as negative numbers. The first row can be a starting value, the middle rows can be changes, and the final row can be the ending value. Keep the final value in the table even if the chart also calculates a running total visually: Excel needs a data point to mark as the ending total.

For example, a department starts with 100 units, gains 40, loses 15, and loses 10. The ending balance is 115. If the categories are in A2:A6 and the values are in B2:B6, with the starting value in B2 and the changes in B3:B5, enter =SUM(B2:B4) in B5 only if the three change rows are actually B2:B4? That layout would be incorrect for this example. A clear layout is more useful: put the starting value in B2, the three changes in B3:B5, and the ending total in B6. With those values in B2:B5, the formula in B6 is =SUM(B2:B5), which returns 115.

In the example, the chart categories might read Opening balance, New orders, Returns, Adjustments, and Closing balance. The starting value is 100, the changes are 40, -15, and -10, and the formula-generated closing balance is 115. Mark both the opening and closing balance points as totals. The three middle columns then show the movements between those endpoints.

Take care not to include a total row in a sum that already includes that same total. A circular reference or a duplicated amount can make the ending value wrong even though the chart is formatted correctly. Also check that the source values are numeric: numbers stored as text, blank rows, or labels accidentally included in the values range can produce unexpected chart behavior.

Mark starting values and intermediate subtotals

Setting only the final point as a total is common, but it is not the only use of the feature. A starting balance should generally be a total as well, because it is a complete value rather than a change from zero. Select the first column and use the same Set as Total command. The chart will then show a clear opening and closing boundary with the movements between them.

You can also set an intermediate point as a total when the data contains a meaningful checkpoint. For instance, a project budget may show an approved budget, several additions and reductions, a revised budget subtotal, then further changes and a final forecast. Marking the revised budget as a total makes it a fixed checkpoint; subsequent changes continue from that amount.

Use subtotals selectively. If every other bar is marked as a total, readers may lose the sense of which columns are changes and which are cumulative checkpoints. Label the categories clearly, such as Revised budget or Quarter-end balance, and keep the underlying sequence logically ordered. The chart does not verify that an intermediate subtotal equals the preceding value plus the changes shown; that remains a data-quality check for the worksheet author.

Format the chart so totals are easy to read

After setting the total, review the chart’s labels, colors, and axis. Excel typically uses different colors for increases and decreases, while total columns have their own default appearance. You can change these colors by selecting a column in the relevant category and using the formatting controls, but preserve enough contrast that totals remain distinguishable from changes.

Use descriptive category names. Closing balance is clearer than Total 2, especially if the chart is copied into a report.
Show data labels when exact values matter. Select the chart and use the chart elements control or the Chart Design options to add data labels; check that labels do not overlap.
Keep the vertical axis appropriate. A baseline that is hidden or a scale that begins far from zero can make totals harder to interpret. For a chart intended to compare full balances, a visible zero baseline is often helpful.
Check the number format. If the worksheet values are currency, percentages, or units, format the chart labels consistently so readers do not have to infer what the figures mean.

Formatting does not set a point as a total. Changing a column’s fill color or adding a data label may make it look more prominent, but only the total setting changes its position and role in the waterfall calculation.

Common problems and how to fix them

Set as Total is missing. You may have selected the whole series, a chart area, or a different chart type. Click the column again to select one data point and confirm that the chart is a Waterfall chart. If the workbook is open in Excel for the web and the needed formatting command is not available, open it in the desktop application.

The column still floats. The selected point may not be the final one, or the second click may have selected the series again. Look for selection handles on only the intended column, then use the point’s context menu. Also confirm that the chart is based on the row you expect; sorting or editing the source table can change which category appears last.

The total starts at zero but has the wrong height. This is usually a source-data or formula issue rather than a chart-format issue. Check the final value against the opening balance plus all the changes. In the example above, 100 + 40 – 15 – 10 equals 115. Confirm that decreases have negative signs and that the ending formula does not include itself or count any movement twice.

The chart changes after data edits. A chart point is tied to its source value and category. If you replace the final row, insert new rows, or change the chart’s source range, verify that the intended ending point is still marked as a total. Recheck any subtotal points too; the chart’s visual setting should agree with the meaning of the revised data.

Waterfall totals versus formulas and other chart types

A waterfall chart’s total setting is not a substitute for a worksheet formula. The formula calculates the ending balance; the chart setting tells Excel how to draw that value. Use both when you need a reliable, updateable result: calculate the endpoint in the worksheet, then mark its chart point as a total.

A regular column chart may be better if the goal is to compare separate values, such as sales by region, rather than explain how a value changes step by step. A line chart is often better for a time series where the shape of movement across many dates matters. A waterfall is most useful when the reader needs to see how a starting amount becomes an ending amount through individually named additions and deductions.

In Excel for Mac, the same point-level total concept is available in supported desktop versions, although ribbon placement and context-menu wording can differ. Excel for the web may not expose every chart-formatting control found in the Windows desktop app. If you cannot find the total command, use Excel for Microsoft 365 on Windows or Mac rather than trying to imitate the effect with colors or manually drawn shapes.

Frequently Asked Questions

Can I make both the first and last waterfall columns totals?
Yes. Select the first data point and choose Set as Total, then repeat for the last point. This is the usual setup when the first and last rows represent opening and closing balances.

Does Set as Total change the cell value?
No. It changes the chart’s treatment of that data point, making it start at the baseline. The worksheet value and any formula remain unchanged.

Can I set a middle column as a total?
Yes. Select the intermediate point and use Set as Total. This is useful for a genuine subtotal or checkpoint, provided later changes are meant to continue from that value.

Why is the last bar not the sum of the earlier bars?
A waterfall chart does not automatically calculate an ending value in the source table. Enter or calculate the ending balance yourself, check the arithmetic, and then mark that chart point as a total.

Can I undo the total setting?
Yes. Select the same data point and clear or reverse its total setting using the context menu or Format Data Point controls. This chart-formatting change is separate from changing the worksheet data.

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.