Excel Waterfall Chart Inputs and Setup

Excel waterfall chart inputs are a list of categories and one matching list of numeric changes or balances: put the labels in one column and the values in the next. Positive values raise the running total, negative values lower it, and any opening, closing, or intermediate subtotal that represents a complete balance should be marked as a total in the chart.

What a Waterfall Chart Input Represents

A waterfall chart explains how a starting amount changes through a sequence of additions and deductions to reach an ending amount. Each row in the source data supplies one step in that story. The chart positions the step against the accumulated running balance: an increase rises from the previous level, while a decrease falls from it. This makes the chart useful for showing not only where a total began and ended, but which changes account for the difference.

The chart needs a category for each step and a numeric value for that step. In a basic Excel layout, the first column contains descriptions such as Opening balance, New sales, Returns, and Closing balance. The second column contains the corresponding numbers. A third column is not required for the standard single-series waterfall chart. The categories provide the horizontal labels; the values determine the bar heights and direction.

Unlike a regular column chart, the values in the middle of a waterfall are usually changes, not separate totals. Excel accumulates them as the chart progresses. Entering a sequence of monthly sales totals as though each were a change would therefore produce a misleading result. First decide whether each number is a movement or a balance, then organize the rows accordingly.

Build the Source Data

For a clear chart, use one header row followed by one row for each step. For example, a simplified account reconciliation could use these inputs:

Opening balance — 1,000
Sales received — 450
Refunds — -80
Operating costs — -300
Closing balance — 1,070

The arithmetic is 1,000 + 450 – 80 – 300 = 1,070. The first and last entries are balances; the three entries between them are changes. In the worksheet, put the labels in A1:A6 and the values in B1:B6, with a header such as Category in A1 and Amount in B1. The example has five chart categories below the headers. Enter negative changes with a minus sign; do not type parentheses or currency symbols into the numeric value if Excel would interpret them as text. Apply a currency or number format to the cells instead.

Keep the order of rows aligned with the sequence the chart is meant to explain. A waterfall does not sort the categories or infer their chronology. If the story runs from an opening figure through several causes to a closing figure, arrange the worksheet in that same order. Descriptive labels are especially helpful when the chart is shared without the source table.

Choose Changes, Balances, and Subtotals

Use a change value when a row represents an increase or decrease to the current running amount. Examples include revenue, units added, expenses, returns, hiring, or inventory adjustments. Use a total value when a row represents the complete balance at a particular point, rather than an amount to add to the prior point. Typical totals include an opening balance, a closing balance, or a subtotal after a defined group of changes.

That distinction is important because an ending balance entered as a number is not automatically the same thing as an ending change. In the example, 1,070 is the final balance, not another increase of 1,070. If Excel treats it as a change, the plotted result will be wrong. After creating the chart, mark the closing balance as a total. Do the same for the opening balance and any subtotal that should begin at the chart baseline.

A subtotal can make a long chart easier to read. Imagine a department budget with several individual expense changes followed by a row called Total expenses. If that row is the combined balance for the expense section, set it as a total rather than adding it as another change. Otherwise Excel accumulates the subtotal on top of the individual expenses and effectively counts them twice. Use subtotal rows only when they clarify the narrative, and check whether they duplicate amounts already represented by preceding steps.

Create the Waterfall Chart in Excel

In Excel for Microsoft 365 on Windows, select the category and value columns, including their headers. Then choose Insert > Insert Waterfall, Funnel, Stock, Surface or Radar Chart > Waterfall. Excel creates a chart with one plotted series and the selected categories. If the chart uses the wrong labels or includes unrelated blank rows, select the chart and adjust its data range through Chart Design > Select Data.

Enter category labels and numeric values in adjacent columns, with a header above each.
Select the complete two-column range, including the headers.
Choose the Waterfall chart from the Insert tab.
Right-click the first balance bar and choose Set as Total. Repeat for the closing balance and any genuine subtotal bars.
Check that each increase, decrease, and total is positioned as intended, then add a title and labels if they help explain the data.

To mark a balance, click its bar once to select the series and click the same bar again if needed to select that individual data point. Right-click the point and choose Set as Total. On a Mac, Excel also supports waterfall charts in current desktop versions; the ribbon arrangement can differ, and Control-click can open the contextual menu if a right-click is not available. On either platform, look for the data point formatting option rather than changing the source number to force the bar into place.

Set Totals and Check the Running Arithmetic

Setting a data point as a total changes how Excel draws that point: it starts from the chart baseline instead of continuing from the preceding cumulative level. The source value remains in the worksheet. This is why changing a closing balance to a negative number, or inserting a made-up adjustment merely to make the columns line up, is a poor fix. Keep the input values faithful to the underlying data and use the total setting for balances.

Before relying on the picture, calculate the expected running balance independently. For the example, begin at 1,000, add 450 to reach 1,450, subtract 80 to reach 1,370, then subtract 300 to reach 1,070. The final total should agree with the last balance row. If it does not, check for a missed change, a duplicated subtotal, an incorrectly signed number, or a closing figure that was entered as a change.

A chart can show several totals, but the meaning of each should be apparent from its label and placement. If a subtotal is not a true balance, do not mark it as one simply because its column looks visually convenient. The total setting is a presentation instruction, not a substitute for sound accounting or operational logic.

Format Labels, Values, and Readability

Choose labels that are brief enough to fit but specific enough to distinguish each step. For example, Returns is more informative than Adjustment if the audience needs to know why the amount fell. If labels are long, widen the chart or shorten the wording in the source cells. Avoid relying only on color to explain the chart; a clear title, axis units, and data labels can make the meaning accessible to more readers.

Use Chart Design > Add Chart Element to add items such as a chart title, axis titles, or data labels. The exact options shown can depend on the selected chart element and Excel build. Number formatting should match the subject: use currency for money, percentages only when the plotted values really are percentage-point changes, and whole numbers for counts. A percentage change and a percentage-point change are not interchangeable. For instance, a rate moving from 10% to 12% rises by 2 percentage points, not by 2% of the original rate.

Waterfall charts commonly use different colors for increases, decreases, and totals. Excel applies chart styling automatically, but the default appearance can vary by theme. Check the legend and any data labels after changing the style. If the chart will be printed in grayscale or viewed by people with color-vision differences, add labels or use sufficiently distinct fills and borders so that direction is not communicated by color alone.

Common Input Problems and Fixes

The final bar does not match the ending balance: confirm that the first and last balance points are set as totals, then reconcile the changes in their displayed order.
A bar moves in the wrong direction: check the sign of the source value. An increase is positive and a decrease is negative, unless the data is intentionally defined using a different convention.
The closing amount appears to be added again: the final balance may still be treated as a change. Set that point as a total rather than altering its value.
The chart has an unexpected category or blank bar: review the selected range in Chart Design > Select Data and remove extra rows or columns that should not be plotted.
Numbers are ignored or displayed oddly: check whether imported values are stored as text. Convert them to numeric cells and apply number formatting rather than embedding symbols in text.
A subtotal is much too large: determine whether it repeats changes already included above it. A subtotal that summarizes earlier steps should be a total point, not an additional movement.

Zeros also deserve attention. A zero change may be useful to show that a category was reviewed and had no effect, but it can add a label without adding much visual information. Remove it if it is irrelevant; keep it if the audience needs to see that the step was considered. Missing data should not be silently replaced with zero, because no recorded value and a confirmed zero change mean different things.

When a Waterfall Is the Right Chart

A waterfall is a good choice when the central question is how a total moved from a starting point to an ending point. It works well for budget bridges, profit and loss explanations, inventory movement, project estimates, and account reconciliations. The chart is strongest when the number of steps is manageable and each step has a meaningful label.

A standard clustered column chart is often better for comparing independent values, such as sales across regions, because each column starts from the same baseline. A line chart is generally clearer for continuous time trends, where the path over many dates matters more than explaining individual additions and deductions. A stacked column chart focuses on parts that make up totals across groups. These charts answer different questions; using a waterfall for unrelated standalone values can imply a cumulative relationship that does not exist.

Excel’s built-in waterfall chart is designed around one sequence of categories and one value series. If the analysis requires many parallel scenarios or several independent series, consider separate waterfall charts, a carefully designed alternative chart, or a custom layout. Do not add extra columns to the source range assuming the ordinary waterfall will interpret them as multiple comparable series in the same way a clustered column chart does.

Frequently Asked Questions

What columns do I need for an Excel waterfall chart?
For a basic chart, use one column of category labels and one adjacent column of numeric values, with headers. The values should be changes except where a row is an opening balance, closing balance, or subtotal that you will set as a total.

Should increases be positive or negative in a waterfall chart?
Enter increases as positive numbers and decreases as negative numbers. For example, add 450 for an increase of 450 and enter -80 for a reduction of 80. The signs should reflect the direction of movement in the running total.

How do I make the first or last bar a total?
In the chart, select the individual bar, right-click it, and choose Set as Total. Repeat for each opening balance, ending balance, or subtotal that should start from the baseline. This changes the chart treatment, not the worksheet value.

Can I use formulas in the values column?
Yes. A cell formula is acceptable if it returns a numeric value, because the chart plots the cell result. Keep the formula output consistent with the row’s meaning: a change row should return a change, while a balance row should return a balance and be set as a total when appropriate.

Why does my total appear too high or too low?
Check the order and signs of the changes, look for duplicated subtotal rows, and verify that balance rows are marked as totals. Then compare the running arithmetic with the closing value in the worksheet. Formatting cannot correct a mismatch in the underlying 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.