How to Create a Waterfall Chart in Excel

A waterfall chart is one of the most effective ways to show how a starting value is affected by a series of positive and negative changes. Instead of just presenting a start and end point, it tells the story of what happened in between. This guide will walk you through exactly how to create, customize, and use waterfall charts in Microsoft Excel.

What Exactly is a Waterfall Chart?

Think of a waterfall chart (sometimes called a bridge chart) as a visual representation of a financial statement or any sequential data. It breaks down the cumulative effect of sequentially introduced positive and negative values. It’s perfect for visualizing a P&L statement, changes in inventory, or fluctuations in your sales pipeline.

A typical waterfall chart has three main components:

• Initial and Final Values: These are the "bookend" columns that usually touch the horizontal axis, representing the starting point (e.g., Q1 Revenue) and the ending point (e.g., Q2 Revenue).

• Increases: These are floating columns that go up, typically colored green, showing positive contributions (e.g., New Sales, Other Income).

• Decreases: These are floating columns that go down, typically colored red, showing negative contributions (e.g., Cost of Goods Sold, Marketing Expenses).

The "floating" bars are what make a waterfall chart unique. They start where the previous bar ended, making it easy to see the incremental impact of each category on the total.

When Should You Use a Waterfall Chart?

Waterfall charts are incredibly versatile but they excel in specific scenarios where you need to explain a change from one point to another. Here are a few common use cases:

• Analyzing Financial Performance: This is the most popular use. Start with gross revenue, show deductions for costs and expenses, and end with net profit. It tells a much clearer story than a simple income statement.

• Tracking Project Budgets: Start with your initial project budget. Show additions or subtractions as expenses are incurred or scope changes, ending with the final budget remaining.

• Illustrating Sales Funnel Changes: Start with the number of leads at the beginning of the month. Add new leads, and subtract lost or disqualified leads to arrive at your end-of-month total.

• Monitoring Inventory Levels: Show your starting inventory, add new stock received, subtract units sold or damaged, and end with the final inventory count.

• Visualizing Website Traffic Data: Start with last month's total traffic. Then show increases from different channels (Organic, Paid, Direct) and decreases (if tracking bounces or unsubscribes in a specific flow) to explain this month's total.

Anytime you need to answer the question, "We started here and ended here, but what happened in between?" a waterfall chart is probably your best option.

How to Create a Waterfall Chart in Excel (Modern Method)

If you have Excel 2016 or a newer version (including Microsoft 365), you're in luck. Creating a waterfall chart is incredibly straightforward because it’s a built-in chart type.

Step 1: Set Up Your Data Correctly

The way you structure your data is the most important part. You need at least two columns: one for your categories (the labels) and one for their corresponding values.

• Positive numbers represent increases (e.g., Sales Revenue).

• Negative numbers represent decreases (e.g., -5,000 for Expenses).

• Bookend your data with the start and end totals (e.g., Starting Cash, Ending Cash). You will calculate your final total manually so the chart can check your work visually.

Let’s use an example of a simple monthly profit analysis. Here’s how you’d set it up in your worksheet:

Notice that costs are entered as negative numbers, and "Starting Revenue" and "Net Profit" are calculated totals we want to anchor to the baseline.

Step 2: Insert the Waterfall Chart

Now for the easy part. Highlight your data range (including titles). Then, navigate to the Insert tab on the ribbon.

In the Charts group, click the icon for "Insert Waterfall, Funnel, Stock, Surface, or Radar Chart" and select Waterfall.

Excel will instantly generate a waterfall chart. However, it will probably look a little strange. By default, Excel doesn't know which of your columns are supposed to be totals, so it just graphs all the changes sequentially. Your "Starting Revenue" and "Net Profit" bars will likely be floating, which isn't what we want.

Step 3: Define Your Totals

This is the essential final step to make your chart work. You need to tell Excel which bars are totals so they are anchored to the baseline (the horizontal axis).

1. Single-click the first bar in your chart ("Starting Revenue" in our example). This will select the entire data series.

2. Single-click the same bar again to select only that individual data point.

3. Right-click on the selected bar and choose "Set as Total" from the context menu. You can also double-click it to open the "Format Data Point" pane and check the "Set as total" box.

You’ll see the bar immediately drop down to the baseline. Now, repeat this process for your ending value ("Net Profit" in our example).

Once you’ve set both your starting and ending values as totals, your chart will be correctly configured. The bars representing a cumulative total should start from the bottom axis, not float in the middle.

How to Create a Waterfall Chart Manually (for Older Excel Versions)

If you're using Excel 2013 or earlier, you don't have the built-in waterfall chart option. But don’t worry! You can build one yourself using a clever workaround with a stacked column chart. It takes a few more steps, but gives you complete control.

Step 1: Re-structure Your Data with Helper Columns

This "hack" works by creating invisible "base" bars that lift the positive and negative value bars off the axis. You’ll need to add a few helper columns to your original data.

Let's use the same data as before. Your setup should look like this, with five columns:

• Category: Your labels.

• Base: An invisible pillar for bars to rest on.

• Decrease: For negative values.

• Increase: For positive values.

• Total: Your original, calculated cumulative value.

Now, let's fill these with formulas:

(Example formulas for a row assuming data starts at Row 2)

(Adjust formulas according to your actual data and row numbers)

Explanation of key formulas:

• Increase Column: =IF(value > 0, value, 0) — Only positive values are shown here.

• Decrease Column: =IF(value < 0, ABS(value), 0) — Negative values made positive for plotting.

• Base Column: For a row n, =previous_base + previous_increase or =(previous_base + previous_increase) - current_decrease depending on whether it’s an increase or decrease. Total/subtotal rows should have a base of 0 to anchor.

Step 2: Insert a Stacked Column Chart

Highlight only the Category, Base, Decrease, and Increase columns. Then go to Insert > Chart > Stacked Column.

You will see a chaotic stack of colors. Next, we need to format it into a waterfall.

Step 3: Format the Chart to Create the "Waterfall" Effect

1. Make the Base Transparent: Right-click on the "Base" series (bottom color, default blue), choose Format Data Series. In Fill, select No fill. This hides the base bars and makes the others appear floating.

2. Re-color Bars: Change the fill of the Increase series to green, and the Decrease series to red.

3. Add Connector Lines (Optional): In some Excel versions, you can enable connector lines to guide flow.

4. Remove Unnecessary Legend Items: Click the legend, select "Base", then press Delete.

This manual approach requires more setup but gives you flexibility, especially on older versions.

Advanced Customization Tips

Whether you used the modern or manual method, here are some tips:

• Use Meaningful Colors: Green for positive, red for negative, neutral grey or blue for totals helps clarity.

• Add Data Labels: Use Chart Elements > Data Labels > Outside End for clarity.

• Adjust Gap Width: Right-click a series, choose Format Data Series, and reduce Gap Width for bolder bars.

• Write Clear Titles: Descriptive titles improve understanding, e.g., "Monthly Profit and Loss - May 2024".

Final Thoughts

Creating a waterfall chart in Excel, whether using the built-in tool or manual process, transforms a list of numbers into a clear, compelling story of change. It’s powerful for financial and operational storytelling.

While Excel’s tools are convenient, manual setup often gives more control — vital when working with data from multiple sources or custom visualizations. Tools like Graphed automate data collection, allowing you to focus on insights rather than setup, by connecting data sources and creating real-time interactive dashboards instantly.