How to Prepare a Waterfall Chart in Excel Step by Step

To prepare a waterfall chart in Microsoft Excel for Microsoft 365 on Windows, put each step in a category column and its change in a values column, select the data, then choose Insert > Insert Waterfall, Funnel, Stock, Surface or Radar Chart > Waterfall. Add any starting or ending balances as rows, and mark those bars as totals so the chart shows how increases and decreases build to the final result. A waterfall chart makes those cumulative changes easy to follow without requiring a separate chart for every step.

What You’ll Need

An Excel workbook with one column for step names and one column for numeric changes or balances.
A clear decision about which rows are changes and which rows represent fixed totals, such as the opening and closing balance.
Excel for Microsoft 365 on Windows for the ribbon paths below. The chart is also available in supported desktop versions of Excel with the native Waterfall chart type.

Step-by-Step

Plan the story the chart should tell. Decide what the first bar represents, which changes should follow, and what final figure readers should see. A common example is a monthly profit bridge: opening profit, additional revenue, added costs, savings, and closing profit. Changes are usually entered as positive or negative numbers. A positive change raises the running total; a negative change lowers it. Starting and ending balances are entered as their actual values, not as changes.
Arrange the source data in two columns. In a blank worksheet, enter a heading such as Category in A1 and Amount in B1. Put one step on each row below the headings, with its numeric amount beside it. For example, use Opening balance 1000, New sales 300, Refunds -80, Operating costs -150, and Closing balance 1070. The arithmetic is 1000 + 300 – 80 – 150 = 1070. This makes a valid example because the closing balance matches the opening balance plus all the intermediate changes. Keep the categories in the order you want them to appear; Excel uses worksheet row order for the chart.
Check that Excel can read the values as numbers. Select the amounts and look at the Home tab’s Number format controls. Set the format to Number, Currency, or another appropriate numeric format if needed. Avoid typing explanatory text such as “up” or “down” into the amount cells. If the source uses accounting parentheses for negatives, confirm they are stored as negative numbers rather than text. Leave blank rows and merged cells out of the selected data range, since they can make the chart harder to interpret.
Select both columns, including the headings. Drag across the category and amount cells, from the heading row through the final category. For the example, that means selecting A1:B6. Including the headings helps Excel use the first column for category labels and the second for values. If you have additional notes or calculations beside the data, do not include them in the selection. You can also convert the range into an Excel table first with Insert > Table, but a simple contiguous range is enough to create this chart.
Insert the Waterfall chart. With the range selected, open the Insert tab. In the Charts group, choose Insert Waterfall, Funnel, Stock, Surface or Radar Chart, then choose Waterfall. Excel inserts a chart on the worksheet. If Excel places the category names in the legend or shows an unexpected series, select the chart and use Chart Design > Switch Row/Column. For a basic waterfall, the desired result is one series of bars and category names along the horizontal axis.
Set the opening and closing bars as totals. Excel initially treats every value as a change, so the opening balance in the example may appear to float from zero rather than start at zero. Right-click the opening balance bar and choose Set as Total. Repeat for the closing balance bar. Each total should begin at the horizontal axis and extend to its actual value; the intermediate bars should float from the previous running total. The Set as Total setting applies to individual bars, so repeat it for every subtotal or final balance that needs this treatment.
Check the running total and category order. Follow the bars from left to right and confirm that each increase and decrease starts where the preceding step ended. In the example, the levels move from 1000 to 1300, then to 1220, and then to 1070. The final total should also be 1070. If the bars do not follow that sequence, check for a sign error, a missing row, or a closing balance that was not marked as a total. Also confirm that the categories appear in the intended order.
Add a useful chart title and labels. Click the chart title and type a short description, such as Monthly Profit Bridge. If there is no title, select the chart and use Chart Design > Add Chart Element > Chart Title. To show amounts on the bars, use Chart Design > Add Chart Element > Data Labels and select a suitable label position. Use labels when readers need exact values; without them, they can still compare the bars visually. If labels overlap or crowd the chart, remove them or resize the chart before reducing the font size.
Format the chart for readability. Click a bar and open Format Data Series to adjust the colors for increases, decreases, and totals. In the formatting pane, use the series options and fill controls to distinguish the categories clearly. Choose colors with adequate contrast and do not rely on color alone if the chart will be printed in grayscale. Use a number format that matches the data, such as currency for financial amounts, and keep the horizontal gridlines light enough that they do not compete with the bars.
Keep the chart connected to its source data. When you change a value in the selected source range, Excel updates the chart. If you add new categories, check whether they fall inside the chart’s data range. Select the chart and choose Chart Design > Select Data to review or adjust the range. If the chart will grow regularly, using an Excel table as the source can help the range expand as rows are added, but verify the result after adding data and recheck which bars are set as totals.

Common Mistakes

Entering balances as changes. The first and last rows often describe balances, not movements. Enter the actual balance for those rows, then set each one as a total. If you enter an ending balance as a change, Excel adds it to the running total and the chart becomes misleading.

Using the wrong sign for a change. A cost or reduction should generally be negative, while an increase should be positive. Check the arithmetic separately before interpreting the chart. A single sign error can make later bars appear to start at the wrong level.

Forgetting to set totals. The chart does not automatically know that a row named Closing balance is a fixed endpoint. If it is left as an ordinary change, its bar floats from the prior level. Use Set as Total on the opening, closing, and any intermediate subtotal bars.

Selecting extra cells or mixing text with values. Including notes, blank separator rows, or a third unrelated column can cause Excel to build an unintended chart structure. Keep the source range to the category and amount columns, and make sure the amount cells contain numbers.

Tips

Use short category names so the axis labels remain legible. If labels are long, widen the chart or rotate the labels through the axis formatting options rather than shortening them until their meaning is unclear.
Use a consistent unit throughout the data. Do not mix dollars with thousands of dollars, or whole units with percentages, in the same series.
For a presentation, add a brief note explaining the period or scope of the figures. A chart title alone may not tell readers whether the values are monthly, yearly, or cumulative.
When printing or sharing, check the chart at its final size. A chart that looks clear on a large worksheet may have tiny labels when pasted into a report.
On Excel for Mac, the chart type and total-bar setting are broadly similar: use the Insert tab to insert a Waterfall chart, then Control-click or right-click an individual bar and choose Set as Total. Ribbon group placement can vary with window size and Excel version.

Troubleshooting

The waterfall chart option is missing. Confirm that you are using an Excel version that includes the native Waterfall chart type and that you are looking in the Insert tab’s chart menu. If your version does not offer Waterfall, update Excel if possible or open the workbook in a supported desktop version. Older versions without a native chart type require a manual stacked-column construction, which is a separate, more involved method.

Every bar starts at zero. The source values may be formatted or selected incorrectly, or the total bars may not have been set. First check that the change values are numeric. Then right-click the opening and ending bars and select Set as Total. Intermediate changes should float from the running level rather than start at the axis.

The chart has the wrong labels or shows multiple series. Select the chart and choose Chart Design > Select Data to inspect the range and series. Confirm that the category names are in the first column and the values are in the next column. If Excel has interpreted rows and columns in the wrong direction, use Chart Design > Switch Row/Column, then check the result again.

A new row does not appear in the chart. Select the chart and use Chart Design > Select Data to extend the chart data range to include the new category and amount. If the source is an Excel table, add the row directly beneath the table and confirm that it has expanded. You may still need to mark a new subtotal or ending balance as a total.

Labels are clipped or overlap. Enlarge the chart area, shorten category text, or reduce the number of displayed data labels. You can also change the label position from Chart Design > Add Chart Element > Data Labels. If the chart will be printed, preview it at the intended page size before sharing.

Frequently Asked Questions

What is a waterfall chart used for in Excel?
A waterfall chart shows how a starting value changes through a sequence of positive and negative movements to reach an ending value. It is useful for explaining bridges such as changes in profit, inventory, headcount, or project cost.

Can I add subtotals between the start and end?
Yes. Add a row for each subtotal using its actual cumulative value, then right-click that bar and choose Set as Total. The subtotal will start at the axis instead of being treated as another change. Make sure the subsequent changes are consistent with the subtotal you show.

Can I change the increase and decrease colors?
Yes. Select a bar in the series, open Format Data Series, and adjust the fill for the increase, decrease, or total categories in the formatting pane. Choose colors that remain distinct in the way the chart will be viewed or printed.

Why does my final bar not equal the calculated result?
Check the opening balance and recalculate the changes in order, including every positive and negative amount. Confirm that no numbers are stored as text, no rows are missing from the chart range, and the ending value is the actual cumulative result. Setting the ending bar as a total changes how it is displayed; it does not correct the underlying arithmetic.

Can I make a waterfall chart in Excel for the web?
Web features can differ from desktop Excel and may vary as Microsoft updates the service. If the chart menu does not offer Waterfall or you cannot set an individual bar as a total, use Excel for Microsoft 365 on Windows or a supported desktop version to create and format it.

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.