What a waterfall chart shows and why you'd build one

A waterfall chart is a column chart that shows how a starting number changes through a series of additions and subtractions to reach an ending number. Each column represents one step — revenue goes up, expenses come down, and you see the running total shift at each stage. Excel does not have a built-in waterfall chart type in older versions, but Excel 2016 and newer include one. If you have an older version, you can build the same visual effect using stacked columns and some formatting work.

Waterfall charts work best for showing profit and loss statements, budget changes, cash flow from operations, or any situation where you need to explain how one number became another. A manager might use one to show how quarterly revenue of $500,000 became profit of $120,000 after costs. A homeowner might use one to show how a $50,000 down payment plus a $200,000 mortgage became a $250,000 home purchase. The format makes the path from start to finish visible in a way a table of numbers does not.

Key Takeaways

  • Excel 2016 and newer have a built-in waterfall chart type under Insert > Chart > Waterfall, but older versions require you to build one using stacked columns.
  • Your data needs three columns: a label column, a value column (positive for increases, negative for decreases), and a running total column that Excel calculates for you.
  • The first and last rows should show your starting and ending totals, with all the middle rows showing the individual changes that connect them.
  • Formatting the chart — coloring increases one way and decreases another — takes a few clicks but makes the story the chart tells much clearer.

Setting up your data in the right shape

Before you build the chart, arrange your data in three columns. The first column holds labels: "Starting Balance", "Revenue", "Payroll", "Rent", "Ending Balance" — whatever the steps in your story are. The second column holds the amounts. For increases, use positive numbers. For decreases, use negative numbers. Do not put a minus sign in front of expenses and then add them; let the negative sign do the work.

The third column calculates the running total. In the first row (your starting point), the running total is just the starting number itself. In the second row, the running total is the first row's total plus the second row's value. Copy that formula down to every row. By the last row, you will have your ending total. This column is what the waterfall chart actually plots — it shows where the total sits after each step.

Here is a straightforward example: Starting Balance is 100, Revenue is 50, Expenses is -30, Ending Balance is 120. Your running total column would show 100, 150, 120, 120. The chart will draw columns at those heights, and the visual gap between them shows the change at each step.

Building the chart in Excel 2016 or newer

Select your data — all three columns, including headers. Click the Insert tab, then click Chart. In the chart type list, scroll down and click Waterfall. Excel will draw the chart when ready using your data.

The chart will show your starting total as a column, each change as a floating column that starts where the previous total ended, and your ending total as a column. Increases will be one color, decreases another, and the connecting lines will be visible. If the chart does not look right, click it and go to Chart Design > Select Data to check that Excel picked the right columns for labels and values.

You can adjust the chart by right-clicking on it and choosing Format Chart Area to change colors, fonts, or the title. Most of the time, the default waterfall layout needs almost no adjustment — Excel handles the floating columns and the visual connections automatically.

Building a waterfall effect with stacked columns in older Excel

If you have Excel 2013 or earlier, you will build a waterfall using a stacked column chart and some manual formatting. Select your data and insert a stacked column chart. The chart will show all three columns stacked on top of each other, which is not what you want yet.

Right-click on the first data series (the starting balance column) and choose Format Data Series. Set the fill to No Fill — make it invisible. Now the chart shows only the second series (the changes) and the third series (the running total). The changes will appear to float above the baseline, creating the waterfall effect.

The last step is formatting. Click on the columns that represent increases and change their color to green (or whatever color signals positive). Click on the columns that represent decreases and change them to red. The starting and ending columns can stay neutral. The chart now tells the story visually: you see where the total started, what pushed it up, what pulled it down, and where it ended.

Labeling and formatting so the chart is clear

Add a chart title that explains what the waterfall shows — "Q3 Profit Waterfall" or "Home Purchase Funding" works better than "Chart 1". Click the chart, go to Chart Design, and click Add Chart Element to add axis titles. Label the vertical axis with the unit you are measuring (dollars, units sold, percentage points).

If your chart has many steps, the horizontal axis labels might overlap. Right-click the axis, choose Format Axis, and increase the label rotation to 45 degrees so the text does not run together. You can also make the chart wider to give the labels more room.

Consider adding data labels to each column so readers see the exact number, not just the height. Right-click the chart, click Add Chart Element > Data Labels > Outside End. This is especially useful if the differences between steps are small and hard to judge by eye alone.

Common mistakes and how to fix them

The most common mistake is forgetting to make expense values negative. If you enter expenses as positive numbers and then try to subtract them in a formula, the waterfall will not work — the columns will stack instead of showing the drop. Always use negative numbers for decreases.

Another mistake is including too many steps. A waterfall with 15 rows becomes hard to read. If you have many small changes, group them into categories — instead of showing every single expense, combine them into "Operating Costs" or "Other Expenses". The chart should tell a story, not list every transaction.

If your chart looks backwards — if increases show as red and decreases as green — you can fix it by selecting the chart and going to Chart Design > Select Data, then reversing the order of the data series. Or straightforward right-click each series and change its color manually.

When a waterfall chart is the right choice

A waterfall works well when you have a clear starting point, a clear ending point, and a series of steps that explain the difference. It fails when you are trying to compare multiple scenarios or show trends over time — use a line chart for trends and a grouped column chart for comparisons instead.

A waterfall also works best when the changes are meaningful and not too numerous. If you are showing how $1 million in revenue became $50,000 in profit, the waterfall makes the cost structure visible. If you are showing how 100 widgets became 95 widgets because 3 broke and 2 were returned, a waterfall is overkill — a straightforward table or a sentence would be clearer.

Frequently Asked Questions

Can I use a waterfall chart to show multiple years or scenarios side by side?

No. A waterfall chart is designed to show one path from start to finish. If you need to compare two years or two budget scenarios, create separate waterfall charts for each one, or use a grouped column chart instead. A grouped chart lets you show multiple bars at each step so readers can compare them directly.

What if my starting number is zero or negative?

A waterfall still works. If you start at zero, the first column will be invisible or very small, and the chart will show how changes build from nothing. If you start negative (a deficit), the first column will extend below the baseline. Excel handles both cases automatically — just make sure your running total column is correct.

How do I add a total line or target line to the chart?

You cannot add a separate line to a waterfall chart in Excel. If you need to show a target or budget line, add it as a data series in your table and format it as a different chart type — right-click the series, choose Change Series Chart Type, and pick Line. This creates a combo chart with both waterfall columns and a line.

Can I make the connecting lines between columns visible?

In Excel 2016 and newer, the waterfall chart includes connecting lines by default. In older versions using stacked columns, the lines are not there — the floating effect is created by the invisible first series. You cannot add them manually without using a more complex workaround.

What if I want to show both positive and negative values starting from the same baseline?

A waterfall is not the right chart for that. Use a stacked column chart where positive values go up and negative values go down from a center line, or use a regular column chart with some columns above zero and some below. A waterfall assumes you are tracking a running total, not comparing separate positive and negative amounts.