Excel Waterfall Chart Data Format: Examples and Setup

For an Excel waterfall chart, use one column for category labels and one column for numeric changes, with one item per row. Enter increases as positive numbers and decreases as negative numbers; add opening and ending balances as rows, then mark those balance points as totals in the chart.

The Basic Data Layout

A native Excel waterfall chart uses a sequence of categories and a single series of values. The category column supplies the labels along the horizontal axis, while the values tell Excel how each step changes the running total. A simple worksheet might look like this:

Category	Amount
Opening balance	12000
New sales	4500
Refunds	-800
Operating costs	-2600
Tax	-900
Closing balance	12200

Each row represents one step in the story. The opening balance establishes the starting amount; sales add to it, while refunds, costs, and tax reduce it. The closing balance is the result at the end of the sequence. Keep labels and values in adjacent columns, include a header row, and avoid inserting blank rows inside the data range.

The key point is that the values are not all independent totals. Most rows are changes to the running amount. In this example, 12,000 + 4,500 – 800 – 2,600 – 900 equals 12,200. Excel uses that sequence to draw the floating columns that make a waterfall chart recognizable.

How the Values Work

A waterfall chart visualizes how a starting value becomes an ending value through a chain of additions and subtractions. Positive changes typically rise from the previous running level, and negative changes fall from it. The floating position of each change shows its contribution, while the column height represents the size of that contribution.

This is why the data format differs from a conventional column chart. In a regular column chart, each value usually represents a complete measurement from zero. In a waterfall, most values represent movement from the prior step, not a fresh total. The chart calculates the running position from the sequence, so the order of the rows matters.

For example, a value of -800 means a reduction of 800 from the running amount. It does not mean that the final balance is negative 800. Likewise, a positive 4,500 indicates an increase, not a balance of 4,500. A useful check is to calculate the starting amount plus all intermediate changes and compare that result with the ending balance.

Build a Concrete Example

Suppose a team wants to explain how monthly cash changes from an opening balance of 12,000 to a closing balance of 12,200. The worksheet values above tell the story in order: sales add 4,500; refunds subtract 800; operating costs subtract 2,600; and tax subtracts 900. The net movement is 200, so the final amount is 12,200.

To create the chart in Excel for Microsoft 365 on Windows:

Put the category names in column A and the corresponding numeric values in column B, including the headers.
Select the full range, for example A1:B7 for the six rows in the example.
Choose Insert > Insert Waterfall, Funnel, Stock, Surface or Radar Chart > Waterfall.
Check that the horizontal labels appear in the intended order and that the bars represent the expected changes.
Mark the opening and closing balance columns as totals, as described in the next section.

On recent Excel for Mac versions, select the data and use Insert > Waterfall to insert the chart; the ribbon layout and formatting-pane details can differ from Windows. If the chart type is not shown, check that the installed Excel version supports waterfall charts and is up to date. The desktop Windows path above is the reference for the steps in this article.

Mark Opening Balances, Endings, and Subtotals

Excel does not reliably know from the number alone that a row is an opening balance, final balance, or subtotal. It initially treats the selected values as changes in the sequence. In the example, if the opening value of 12,000 is treated as an increase, the chart will build from zero rather than show it as the baseline. You need to set balance points as totals.

In the chart, select the individual column for Opening balance, right-click it, and choose Set as Total. Repeat for Closing balance. If selecting the point is difficult, click once to select the chart series and then click the desired column again to select that individual data point. You can also open Format Data Point and use the Set as total option when it is available in the pane.

The same method applies to a subtotal in the middle of a chart. Suppose a report shows beginning inventory, purchases, returns, a subtotal called Inventory before sales, sales, and ending inventory. Mark the subtotal as a total if it should begin at the chart baseline and show the accumulated amount at that stage, rather than appear as another change. A total point is a visual checkpoint; it does not alter the underlying worksheet value.

Setting the final point as a total is especially useful when the ending balance is supplied by a separate accounting calculation. The ending value should agree with the opening value plus the intervening changes. If it does not, the chart can still display the supplied number as a total, but it will reveal a reconciliation difference rather than correct the data for you.

Prepare and Check the Source Data

Good source data makes the chart easier to build and maintain. Use one row for each meaningful step, keep category labels concise, and enter real numeric values rather than numbers stored as text. Include the sign in the value column: decreases should be negative, not positive values accompanied only by labels such as Cost or Refund.

Keep the sequence intentional. The row order is the calculation order. Sort or rearrange categories only when the business story calls for it.
Use clear labels. Names such as Returns or Hiring costs are easier to interpret than generic labels such as Item 1.
Separate labels from amounts. Put units, dates, or explanatory text in the category column or in notes, not in the numeric value cells.
Check signs and arithmetic. Confirm that increases are positive, decreases are negative, and the ending figure reconciles to the start plus changes.
Keep the selected range clean. A title row above the headers is fine if you do not include it accidentally; blank rows or unrelated columns inside the selection can confuse chart setup.

For recurring reports, keep the source range in a consistent two-column layout and add new categories at the end in the order they occur. If you convert the data to an Excel table with Insert > Table, the table can make the source easier to manage as rows are added, although you should verify the chart range after adding data and reapply total settings if the new rows change which points are checkpoints.

Format the Chart for Clear Reading

Once the data is correct, formatting should make the movement easy to follow rather than decorate it. Select the chart to use the Chart Design and Format tabs. The exact available controls depend on the selected chart element and Excel build. Add a descriptive chart title, such as Monthly cash movement, and use data labels if readers need the exact size of each change.

Excel distinguishes increases, decreases, and totals with different default colors. Those distinctions are useful, but color alone should not carry the meaning: keep labels readable and choose colors with enough contrast. If the chart is printed in grayscale or viewed by someone with color-vision differences, labels and the direction of each floating bar should still make the sequence understandable.

Long category names can crowd the horizontal axis. Shorten labels while keeping the meaning clear, widen the chart, or reduce the number of categories shown at once. For a large number of steps, consider grouping small items into a clearly labeled category or splitting the explanation into more than one chart. Do not hide meaningful changes just to make the visual cleaner.

Common Problems and Fixes

The opening balance appears as a change from zero. Select that bar and set it as a total. A starting balance is a total point, not a movement.
The ending bar does not match the last floating step. Check whether the ending point is set as a total, then verify the arithmetic and signs in the source values.
A decrease rises instead of falls. Check that its value is negative and numeric. A number stored as text, or a positive number with a descriptive label, will not behave as a decrease.
The categories are in the wrong order. Reorder the worksheet rows. A waterfall chart follows the data sequence; it does not automatically arrange items by size or date.
The chart includes unwanted labels or empty categories. Review the selected source range and remove accidental blank rows or unrelated columns from it.
The chart looks like a regular column chart. Confirm that the selected chart type is Waterfall and that intermediate values are changes rather than repeated full balances.

A common misconception is that entering a final balance at the bottom automatically tells Excel it is a total. It does not: Excel may initially interpret it as another change. Another is that every negative bar must extend below the horizontal axis. In a waterfall, a negative change usually descends from the current running level; it may remain above zero if the cumulative amount is still positive.

When a Waterfall Chart Is the Right Choice

Use a waterfall chart when the audience needs to understand the components behind a net change: for example, a profit bridge, project budget movement, inventory reconciliation, headcount changes, or cash balance explanation. It answers not just what the beginning and ending numbers are, but which changes moved the result along the way.

A regular column chart is often clearer when comparing separate values, such as sales by region, because each bar starts from a common baseline. A line chart is usually better for showing a continuous trend across time. A waterfall is most helpful when the categories form a sequential bridge from one total to another; it is less suitable when the items have no meaningful order or when there are so many small changes that the bridge becomes hard to read.

Frequently Asked Questions

What columns do I need for an Excel waterfall chart?
For a basic native chart, use one category column and one numeric value column. Put one item per row, use positive values for increases and negative values for decreases, and include any opening, ending, or subtotal rows you want to show.

Should a waterfall chart use running totals or changes?
Use changes for the intermediate rows. Excel accumulates them to create the floating steps. Opening balances, ending balances, and selected subtotals are totals, which you mark on the chart with Set as Total.

How do I show a subtotal in the middle of a waterfall chart?
Add a row for the subtotal at the appropriate point in the sequence, then select its chart column and choose Set as Total. The value should represent the accumulated amount at that checkpoint.

Can an Excel waterfall chart have multiple value columns?
The built-in waterfall chart is designed around a single series of sequential changes. Put the values for that series in one column. If you need to compare several independent bridges, separate charts or a different visualization may be clearer.

Why is my waterfall chart not adding the values correctly?
Check that each change is numeric, that decreases have negative signs, and that rows are in the correct order. Also confirm that opening and ending balances are marked as totals; otherwise Excel may treat them as changes in the running sequence.

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.