What a waterfall chart shows and why you might use one

A waterfall chart is a visual way to show how a number changes when you add and subtract things from it. The chart looks like a series of floating bars, each one starting where the last one ended. It answers questions like: "I started with $5,000, spent $800 on rent and $200 on groceries, and earned $1,200 from a side job — where did I end up?" or "My savings account had $10,000 in January, what happened month by month to get to $8,500 in December?"

Excel does not have a built-in waterfall chart type in older versions, but Excel 2016 and newer do. If you have an older version, you can build one using stacked bar charts. This guide covers both methods so you can use whichever applies to your copy of Excel.

Key Takeaways

  • Excel 2016 and newer have a waterfall chart type you can insert directly from the Charts menu.
  • Older Excel versions require you to build a waterfall effect using stacked column or bar charts and hidden helper columns.
  • Your data needs a starting value, a row for each change (positive or negative), and a final total row.
  • The chart works best when you have between three and ten categories to track, so it stays readable on screen or on paper.

Setting up your data for a waterfall chart

Before you build the chart, organize your data in three columns. Column A holds the category names (like "Starting Balance," "Rent," "Groceries," "Side Job Income," "Ending Balance"). Column B holds the dollar amounts for each category. Column C will hold a helper column if you are using an older Excel version — you can skip this for now if you have Excel 2016 or newer.

Start with your opening number in the first row. Then list each change as a separate row, with positive numbers for money coming in and negative numbers for money going out. Put your final total in the last row. For example, if you are tracking a checking account, row one might be "Starting Balance: 5000," row two "Rent: -800," row three "Groceries: -200," row four "Side Job: 1200," and row five "Ending Balance: 5200."

Do not use currency symbols or commas in the cells yet — just the numbers. Excel will format those after you build the chart. If a number represents money going out, type it as a negative number (with a minus sign in front).

Creating a waterfall chart in Excel 2016 and newer

Select all your data, including the category names in column A and the amounts in column B. Click the Insert tab at the top of the ribbon. Look for the Charts section and click the small arrow next to the chart icons to open the full chart menu. Scroll down until you see "Waterfall" listed among the chart types. Click it.

Excel will insert a basic waterfall chart into your sheet. The chart will show each category as a floating bar, with connecting lines showing how the total moves from one step to the next. The starting balance and ending balance usually appear in a different color so you can spot them easily.

Right-click on the chart and select "Format Chart Area" to change colors, fonts, or the title. You can also click once on the chart and use the Chart Design tab that appears in the ribbon to switch between different waterfall styles or add data labels (the numbers on top of each bar).

Building a waterfall effect in older Excel versions

If you have Excel 2013 or earlier, you will use a stacked column chart instead. First, add a helper column (column C) next to your amounts. In the first row of column C, type the formula =B1 (this copies your starting balance). In the second row, type =B1+B2 (this adds the first change to the starting balance). In the third row, type =B1+B2+B3, and continue this pattern down to your final row. These formulas create the "floating" effect by calculating where each bar should start.

Now select columns A, B, and C (all three columns of data). Go to Insert and choose Column Chart. Pick the stacked column type. The chart will show two series stacked on top of each other — column B (your changes) and column C (your running totals). Right-click on the column C series (the taller one) and select "Format Data Series." Change the fill color to "No Fill" so it becomes invisible. This hides the helper column and leaves only the changes visible, creating the waterfall look.

Add data labels to show the amounts on each bar. Click the chart, then right-click on the column B series and select "Add Data Labels." The numbers will appear on top of each floating bar so viewers can see the exact amounts.

Customizing your waterfall chart

Once your chart is built, you can adjust how it looks. Double-click the chart to enter edit mode. Right-click on any bar to change its color — for example, you might make income bars green and expense bars red. Click on the chart title to edit the text, or right-click it to change the font size or color.

If your numbers are large (like thousands or millions), add a title that says what the numbers represent. For example, "Monthly Cash Flow (in dollars)" or "Savings Account Changes (in hundreds)." This helps anyone reading the chart understand the scale at a glance.

You can also adjust the axis labels. Click on the numbers along the left side of the chart (the vertical axis) and right-click to format them. You might want to show them as currency (with a dollar sign) or round them to whole numbers if they have many decimal places.

Common mistakes and how to fix them

The most common error is forgetting to make expense amounts negative. If you type "800" instead of "-800" for rent, the bar will point upward instead of downward, making the chart confusing. Go back to your data, add the minus signs, and the chart will update automatically.

Another mistake is including too many categories. If you have more than ten or twelve line items, the chart becomes hard to read. Instead, group similar expenses together — combine "groceries," "gas," and "household supplies" into one "Living Expenses" row, for example.

If your ending balance does not match what you calculated by hand, check that your last row is truly a total, not another transaction. The ending balance should equal the starting balance plus all the changes. If the numbers do not match, you may have missed a transaction or made a typo in one of the amounts.

Frequently Asked Questions

Can I use a waterfall chart to show profit and loss?

Yes. Put your total revenue in the first row, then list each expense as a separate negative row, and your net profit or loss in the final row. The chart will show visually how each expense reduces your profit, making it straightforward to see which costs have the biggest impact.

What if some of my numbers are very large and others are very small?

The chart will still work, but the small bars may be hard to see. Consider breaking the data into separate charts — one for major categories and another for smaller items — or round your numbers to a simpler scale (like thousands instead of individual dollars) so the differences are easier to spot.

Can I add a waterfall chart to a report or email?

Yes. Right-click on the chart and select "Copy." Then paste it into Word, PowerPoint, or an email. The chart will paste as an image. If you need to update the numbers later, you will need to update the Excel file and copy the chart again.

How do I show what each bar represents if the category names are too long?

Shorten the names in your data (use "Rent" instead of "Monthly Rent Payment to Landlord"). You can also rotate the axis labels so they read at an angle instead of straight across. Click the chart, right-click the category names along the bottom, select "Format Axis," and look for a rotation option.