How to Make a Waterfall Chart in Excel

A waterfall chart is one of the best ways to tell a financial story, breaking down how a starting value changes over time through a series of positive and negative steps. While it looks complicated, creating one in Excel is surprisingly straightforward once you know the steps. This article will walk you through exactly how to prepare your data and build a professional-looking waterfall chart, both with Excel's built-in tool and a manual method for older versions.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is a Waterfall Chart, Anyway?

Think of a waterfall chart as a visual bridge that connects a starting point to an ending point. It shows a running total as values are added or subtracted, making it incredibly useful for explaining the "why" behind a final number. Each bar appears to "float" and is connected to the previous one, showing a cumulative effect.

For example, you could use a waterfall chart to show:

• Profit and Loss: Start with sales revenue, subtract the cost of goods sold, subtract operating expenses, and end with net profit.
• Monthly Cash Flow: Begin with your opening bank balance, add incoming payments, subtract payroll, rent, and other expenses, and end with your closing balance.
• Sales Team Performance: Start with the Q1 quota, add new deals closed, subtract deals lost, and end with the final quota attainment.
• Website Traffic Breakdown: Start with last month's total traffic, add new traffic from organic search, subtract bounced traffic from paid ads, and end with this month's total traffic.

Essentially, if you need to explain how you got from Number A to Number B, an Excel waterfall chart is probably the perfect tool for the job.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Prepare Your Data for a Waterfall Chart

Before you can make the chart, you need to set up your data correctly. This is the most important step. Excel needs a clear list of categories and their corresponding numerical values. More importantly, you need to distinguish between changes (positive or negative) and totals (start and end values).

Here's a simple data structure. Let's imagine we're building a simple P&L for a small online store for Quarter 3.

Your table should have two columns:

1. Category: The labels for each step in your story (e.g., "Revenue," "Ad Spend," "Shipping Costs").
2. Value: The amount for each category. Use positive numbers for increases and negative numbers for decreases.

A common mistake is forgetting to include the starting and ending totals in your data. Your chart should always begin and end with columns that represent the total values at those points.

Here is how the data for our P&L example would look:

Notice that "Returns," "Ad Spend," "Salaries," and "Software Costs" are negative because they decrease the balance. The "Ending Balance (Q3)" is the calculated final number (100k + 50k - 5k - 10k - 25k - 2k = 108k). Excel uses this table to build the chart.

How to Create a Waterfall Chart in Modern Excel (2016 and Newer)

If you're using a modern version of Excel, you have access to a built-in waterfall chart function that makes this process incredibly easy.

Step 1: Select Your Data

Click and drag to highlight the cells containing your data, including the headers. In our example, you would select the full table from "Category" down to "108,000."

Step 2: Insert the Waterfall Chart

With your data selected, navigate to the Insert tab on the Excel ribbon. Click on the icon that looks like a small waterfall or histogram, which is labeled "Insert Waterfall, Funnel, Stock, Surface, or Radar chart." From the dropdown menu, select Waterfall.

Excel will instantly generate a chart. It will look something like this, but we're not quite done yet.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Set Your Start and End Totals

Excel does a good job of guessing your increases and decreases, but it usually doesn't know which columns are your official start and end totals. By default, it will treat all bars as floating "change" values. You need to manually tell Excel which bars should be grounded "totals."

• First, click once on the "Starting Balance" bar to select just that bar. If you click once and all bars are selected, click the "Starting Balance" bar a second time.
• Right-click on the selected bar and choose "Set as Total." The bar will immediately drop down to start from the horizontal axis.
• Repeat this process for the "Ending Balance" bar. Click it once or twice to select it, right-click, and choose "Set as Total."

Now your chart correctly shows the opening balance, the journey of ups and downs, and the final result.

Step 4: Customize Your Chart's Appearance

Your chart is functionally complete, but a few cosmetic tweaks can make it easier to read:

• Colors: By default, Excel colors increases ("up" bars) blue and decreases ("down" bars) orange. A common convention is to make increases green and decreases red. To change this, double-click on one of the "Increase" bars to open the "Format Data Series" panel. Under the "Fill & Line" (paint bucket) icon, you can set custom colors for increases, decreases, and totals.
• Data Labels: To show the exact value for each bar, click the chart, then click the Chart Elements button (the green "+" sign) on the top right. Check the box for Data Labels.
• Chart Title: Click the default title and give your chart a descriptive name, like "Q3 Profit & Loss Breakdown."
• Gridlines: You can remove gridlines to give the chart a cleaner look. Click the Chart Elements button and uncheck Gridlines.
• Connector Lines: These are the thin gray lines connecting the bars. You can turn them on or off in the "Format Data Series" panel under "Series Options."

How to Create a Waterfall Chart in Older Excel Versions (Pre-2016)

If you have an older version of Excel, don't worry. You can still create a waterfall chart, but it requires a bit of clever manipulation using a Stacked Column chart. This method is more manual but gives a great result.

Step 1: Add "Helper" Columns to Your Data

This is where the magic happens. We need to restructure our data table to trick Excel into creating floating bars. Next to your original columns, add three new "helper" columns: Base, Increase, and Decrease.

• Base: This column will be invisible in our final chart. It represents the "bottom" part of each stacked bar, acting as a hidden platform for our real data to sit on.
• Increase: This will hold our positive values.
• Decrease: This will hold our negative values (represented as positive numbers for charting purposes).

You'll need a set of simple formulas to populate these columns based on your original values. Here's how you'd set up the table next to the original P&L data:

The formulas needed here track the cumulative total and separate positive/negative values:

• For the Increase column, a formula like =IF(B3>0, B3, 0) works (assuming your value is in B3). For totals, just pull the total value.
• For the Decrease column, use =IF(B4<0, -B4, 0) to make the negative value positive.
• For the Base column, the formula is the cumulative total. It's the base from the row above plus the increase, minus the decrease from the row above. Starts at 0.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Insert a Stacked Column Chart

Highlight only your Category column and the three new helper columns (Base, Increase, Decrease). Do not include the original "Value" column.

Go to the Insert tab, click the Column Chart icon, and select Stacked Column. You'll get a chart that already starts to look like a waterfall - it just needs some formatting.

Step 3: Format the Chart into a Waterfall

1. Make the "Base" Series Invisible: This is the key. Right-click on any of the "Base" bars (usually the bottom-most, blue-colored bars) and select "Format Data Series." In the panel that opens, under "Fill," choose "No fill." Just like that, your other bars will appear to be floating.
2. Color Code Your Bars: Right-click the "Increase" bars and color them green. Then right-click the "Decrease" bars and color them red. Your chart now clearly distinguishes between positive and negative changes.
3. Fix the End Total Bar (if needed): In our example, we set it up so the Total bars would appear in the "Increase" column. If you created a separate "Total" column, you would color that one gray or blue.
4. Adjust the Gap Width: To make the columns look more like traditional waterfall bars, right-click any series, select "Format Data Series," and under "Series Options," reduce the Gap Width to around 50%. This makes the bars thicker.

After a quick title change and potentially adding data labels, you'll have a perfect waterfall chart, created even without the built-in feature!

Final Thoughts

Waterfall charts are an incredibly effective way to visualize financial performance and tell a clear story with your data. Whether you're using Excel's dedicated chart type or the classic stacked column method, mastering this skill will elevate the quality and clarity of your reports.

While creating charts like this in Excel is a great skill, we know it can become tedious, especially when you need to combine data from multiple platforms like Shopify, Google Analytics, and Facebook Ads. We built Graphed to solve this problem. After a simple one-click connection to your data sources, you can ask in plain English, "Create a waterfall chart showing last month's Shopify revenue, minus ad spend from Google and Facebook," and get a live, automated dashboard in seconds, skipping the CSV downloads and manual updates entirely.