Stacked Waterfall Chart in Excel: Handling Negatives

Excel’s built-in Waterfall chart can show positive and negative changes, including a running balance that falls below zero, but it cannot stack several colored components inside each floating step. For a reliable chart, use a native Waterfall chart for net changes; if you need to show component detail, expand those components into consecutive steps or build a more involved custom chart.

What “stacked waterfall” means in Excel

A Waterfall chart explains how a starting amount changes through a sequence of increases and decreases to reach an ending amount. Each change appears as a floating column: an increase rises from the prior balance, while a decrease falls from it. The chart is useful for explaining revenue bridges, budget changes, inventory movement, and other sequential totals.

People often use “stacked waterfall” to mean one of two things. They may want the ordinary waterfall steps to show gains and losses, or they may want each step divided into multiple colored parts—for example, a quarterly cost decrease split into labor, materials, and freight. Excel’s native Waterfall chart handles the first meaning, including negative values. It does not provide the second as a built-in stacked format.

This distinction matters because a standard stacked column chart and a Waterfall chart encode different things. A stacked column chart combines series from a shared baseline. A waterfall step instead starts at the cumulative result of earlier steps. Excel does not offer a single chart type that automatically combines those behaviors for mixed positive and negative components.

Create a native Waterfall chart with negative values

For Microsoft Excel for Microsoft 365 on Windows, put one starting value and then one signed change on each row. Use positive numbers for increases and negative numbers for decreases. Select the labels and values, then choose Insert > Insert Waterfall, Funnel, Stock, Surface or Radar Chart > Waterfall.

For example, suppose the table has labels in A2:A6 and values in B2:B6:

Opening balance: 100
Revenue: 40
Operating costs: -25
Write-off: -130
Closing balance: -15

The changes calculate as 100 + 40 – 25 – 130 = -15. If the closing balance should be calculated rather than typed, with the opening balance in B2 and changes in B3:B5, enter this in B6: =SUM(B2:B5). The result is -15. The formula assumes those exact cells contain the opening amount and three changes.

Excel initially treats every number as a change. To make the opening and closing columns start from the chart baseline as totals, right-click each of those columns and choose Set as Total. On a Mac, Excel for Microsoft 365 also supports Waterfall charts; the Insert command is in the chart options, and Control-click can open the point’s context menu. Ribbon wording can vary slightly by release.

How negative steps and balances are drawn

A negative change is not an error or a special text label: enter it as a negative number. Excel draws the decrease from the previous running total. If a step takes the cumulative result through zero, the chart can show the column crossing the zero line, and later steps continue from the new balance. The final balance can therefore be negative.

For the example above, the running balance is 100 after the opening total, 140 after revenue, 115 after costs, and -15 after the write-off. The write-off column spans from 115 down to -15. The closing total should be marked as a total so it is displayed as a full column from the baseline rather than as another change from the prior step.

Positive and negative colors are controlled by the chart’s formatting, not by changing the sign of the underlying data. Select the chart and use the chart formatting options to adjust the increase, decrease, and total fills. Color can help distinguish direction, but retain clear category labels and, where useful, data labels: readers should not have to infer a value from color alone.

Show component detail without misrepresenting the chart

If one business category contains several causes, you can show each cause as its own consecutive Waterfall step. For example, instead of one “Operating costs” change of -25, list “Labor” at -12, “Materials” at -8, and “Freight” at -5. Their combined impact is still -25, and each component’s bar begins where the prior component ended. Add a subtotal or checkpoint if readers need to see the balance after the group.

This approach is a sequence of components, not a stack of segments occupying one shared step. The order also affects the intermediate balances shown. If the order has no analytical meaning, choose and disclose a consistent order—such as largest change first—or show a separate breakdown chart next to the waterfall.

A useful design is to keep the main waterfall focused on the net movement by period, then use a separate stacked bar or column chart to show the composition of one period’s change. The two charts answer different questions: the waterfall shows how the total moved over time; the stacked chart shows how categories contribute to a selected total. Make their time periods and units consistent so readers can compare them sensibly.

Why a stacked column chart is not a direct substitute

It is tempting to choose Stacked Column and add a hidden “base” series so the visible columns float. That technique can work for relatively simple floating bars when the base and segment values are prepared carefully. It becomes unreliable as a general solution for a waterfall made of multiple components that change direction, especially when cumulative values cross zero.

In a stacked column chart, positive series stack above the zero baseline and negative series stack below it. They do not automatically follow the running total from the preceding category. A negative component in a period with positive components can therefore appear on the negative side of zero instead of as a downward segment attached to the current cumulative level. Hiding a base series does not change that stacking rule.

There are custom chart designs that use helper ranges, shifted values, or separate floating series to approximate more elaborate layouts. They require deliberate handling of positive and negative ranges, axis labels, and data labels. Treat such a chart as a custom visualization that needs testing—not as a chart type Excel will maintain automatically when someone inserts rows or changes the data structure.

Choose the chart that matches the question

Use a native Waterfall chart when the main need is to explain a sequence of signed changes and an ending total. It is the simplest choice for negative steps and balances.
Use a Waterfall chart with one row per component when you need to show the order and cumulative effect of multiple causes. Clearly label the component steps and add totals or subtotals where they help.
Use a stacked bar or column chart when you want to compare the composition of totals across categories, not trace a running balance from one category to the next.
Use a custom floating chart only when the combined layout is essential and you can maintain the supporting calculations, axis treatment, and labels.

A common misconception is that a “stacked” chart is automatically better for explaining a total. Stacking is helpful when the parts add to a whole at a common baseline. A waterfall is more suitable when the important story is the path from a starting figure to an ending figure. If readers need both composition and movement, two coordinated charts are often clearer than forcing both questions into one graphic.

Common problems and checks

The last column looks like a change. Right-click the point and select Set as Total. Check the opening balance as well if it should be a full starting column.
A decrease appears to increase the balance. Check the source sign. Reductions should be negative numbers, not positive values with a minus sign included as text.
The chart does not match a calculated ending amount. Recalculate the running total independently and check for omitted rows, duplicate entries, or numbers stored as text. Confirm that the chart’s source range includes the intended rows.
The negative result is hard to see. Make sure the value axis displays both sides of zero. If you manually set axis bounds, choose limits that include the lowest and highest balances and avoid cutting off columns.
The chart is hard to interpret after adding many steps. Group or summarize minor changes, use descriptive labels, and consider a subtotal after a meaningful block. Too many narrow columns can obscure the sequence.

After changing data, inspect the total points again. A total designation belongs to a chart point, and changing the source range or rebuilding the chart can change which points are present. Also check that the chart title, units, and number formats make clear whether values are dollars, units, percentages, or another measure.

Frequently Asked Questions

Can an Excel Waterfall chart show negative numbers?
Yes. Enter decreases as negative numeric values. Excel draws each decrease from the previous running balance, and the cumulative balance can pass below zero.

Can I make a Waterfall chart stacked by category?
Not with the built-in Waterfall chart. It does not stack several colored components inside each floating step. You can show components as separate sequential steps or use a separate stacked chart to show their composition.

Why does my ending balance not start at zero?
Excel may be treating the ending value as another change. Right-click its column and choose Set as Total. The same setting is commonly used for an opening balance that should appear as a full column.

Should decreases be entered as positive values?
No. Enter decreases as negative numbers. For instance, a cost reduction of 25 is entered as -25 if it reduces the running total by 25. Use positive values only when the change increases the total.

Can I use a stacked column chart for a negative waterfall?
A regular stacked column chart does not automatically float each mixed-sign step from the previous running balance. It can be adapted with helper data for particular cases, but the native Waterfall chart is usually clearer and less fragile for a negative running 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.