How to Make a Stacked Waterfall Chart in Excel

Creating a stacked waterfall chart in Excel might seem tricky, but it's one of the best ways to visualize how different positive and negative components contribute to a total over time. This guide breaks down the process into simple, actionable steps, transforming a standard stacked column chart into a powerful and dynamic waterfall visualization. We'll build the chart from the ground up, formulas and all.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What Exactly is a Stacked Waterfall Chart?

A standard waterfall chart shows a running total as values are added or taken away, visualizing the cumulative effect of sequential positive and negative changes. Think of it as a financial storybook, showing how your opening balance (the first bar) is changed by revenue (a positive bar) and expenses (a negative bar) to arrive at your closing balance (the final bar).

A stacked waterfall chart takes this a step further. It doesn't just show that revenue went up, it breaks down why. Each moving bar ("the waterfalls") is composed of multiple segments or "stacks." For example, instead of one green bar for "Q1 Revenue," you might have a stacked green bar showing that revenue came from "Product Sales," "Service Fees," and "Subscription Income."

This allows you to see both the overall change and the performance of the individual components driving that change, all within a single chart.

Graphed

Still Building Reports Manually?

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

Watch the Demo

The Excel "Workaround": Creating Your Chart

Here’s the thing: Excel has a built-in Waterfall chart type, but it doesn't support stacking. To get the chart we want, we need a clever workaround. We will create a standard Stacked Column Chart and use specific helper columns and formulas to make parts of it invisible, creating the "floating" bar effect that defines a waterfall chart.

Step 1: Structure Your Raw Data

Before you can build anything, you need to organize your data. The key is to separate your totals (like starting or ending balances) from your contributing components (the positive and negative changes).

Start with a simple table listing each component, a category, and its value. Positive numbers represent increases (revenue, gains), and negative numbers represent decreases (costs, expenses). For this walkthrough, we'll use a simple Profit & Loss example.

Your raw data might look something like this:

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 2: Create the Calculation Table

This is where the magic happens. We need to restructure the raw data into a new table that Excel's stacked column chart can understand. This new table will have helper columns to calculate the start and end point of each stack.

Your new table will have these columns:

• Labels
• Base (invisible riser)
• Positive Components (one column for each, like "Product A Sales", "Service Income")
• Negative Components (one column for each, entered as positive numbers, like "Material Costs", "Labor Costs")
• Start/End Columns (for our totals, like "Starting Revenue", "Gross Profit")

Here’s how to build it out based on our example. Create this table in a new area of your sheet, ensuring you have enough columns for all your components.

Formula Breakdown:

The table relies on a "running total" logic. The formulas reference the cells above them, so make sure they are entered correctly row-by-row. Note that we flip the sign on negative components to make them positive for charting purposes.

• End columns (G2, H2): These are your total bars. The "Starting Revenue" begins at zero base, so G2 contains the full starting value (100,000), leaving H2 empty. The other total bars are calculated differently.
• Base (C3): The Base for the first increase ("Sales") is simply the footing of the previous bar.

=G2

• Negative Component (F4): Enter your negative component as a positive number. For instance, =20000. In E4, =15000.
• Base (C4): For negative "floating" bars, calculate the Base by starting at the previous accumulation and lowering it by the magnitude of its own negative components.

=C3+D3+E3-F4-G4

Step 3: Create the Stacked Column Chart

1. Select all the data in your calculation table, including headers and empty cells.
2. Go to the Insert tab in Excel.
3. In the Charts group, click the Insert Column or Bar Chart icon.
4. Choose the Stacked Column chart from the 2-D Column options.

Excel will produce a chart that looks quite messy. Don't worry! This is expected. All our components, including the "Base" series and our "Totals" columns, are just stacked on top of each other. Now we will fix it.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 4: Format Your Chart into a Waterfall

This is where we turn the jumbled stack of blocks into a clean waterfall.

1. Make the Base Series Invisible

• Find the "Base" series in your chart. It's usually the bottom-most color.
• Right-click on one of the "Base" segments and choose Format Data Series...
• In the Format Data Series pane, go to the Fill & Line tab (the paint bucket icon).
• Under Fill, select No fill.
• Under Border, select No line.

The bottom series should now disappear, leaving your other bars "floating" in mid-air. You’re already part-way there!

2. Convert the Total Series Into Proper Full Bars

• Find your total bars (Starting Revenue, Gross Profit, etc.) in the chart.
• Give them a neutral color like gray or dark blue to distinguish them from the "delta" or "change" bars that bridge them.

3. Color Code the Stacks

• For clarity, give your stacks consistent colors. All positive components should use one color scheme (e.g., shades of green), and all negative components another (e.g., shades of red).
• Click once on a stack to select the entire series (e.g., all "Product Sales"). Click again to select just a single segment if needed.
• Right-click and choose Format Data Series....
• Under the Fill options, choose your colors accordingly (e.g., dark green for "Product A Sales," light green for "Service Income," dark red and light red for COGS or OpEx).

4. Add Connector Lines (Optional but Recommended)

Connector lines help guide the viewer's eye, showing the flow from the top of one bar to the next. Excel doesn't have an automatic connector line feature, so we'll plot a separate, custom series.

1. In an empty column within your calculation table, create a Connector Line helper data set. This value corresponds to the running total at each interval.
2. Right-click on your chart and choose Select Data.... Add a new series and select your connector line End Value column data.
3. The new series will appear as additional vertical stacked bars. Right-click on these new bars only, choose Change Series Chart Type....
4. In the dropdown, change this series to Line with Markers.
5. Format the line: turn off the round markers (or make them very tiny), and set the line to be thin, dashed, and give it a black or gray color.

Step 5: Tidy Up Final Chart Details

• Legend: Remove "Base" from the legend by clicking on it and pressing Delete. Adjust as needed.
• Title and Axes: Add a descriptive chart title and clearly label axes.
• Enable Data Labels inside the stacks for better clarity. Manually position only the most significant contributions and total bars to avoid clutter.

Pitfalls and Best-Practices Tips

• Be meticulous with your helper formulas, errors will directly affect your chart.
• Use consistent colors for up and down movements, and a separate color for totals.
• Keep the number of stacked elements low (below 3-4) to prevent clutter.

Final Thoughts

Making beautiful, dynamic, and informative stacked charts in Excel hinges on meticulous table preparation and careful, step-by-step formatting of chart elements. It may take a few tries to get it right, but it significantly enhances your presentation and analysis capabilities in Excel.

Creating charts just described in plain English sounds much easier, doesn't it? By using a product like Graphed, you can bypass all of this manual data-wrangling for charts. Simply connect your data streams and ask to "show the revenue vs expenses split for the Q1 financial flow." Graphed can automate your visualization work from start to finish and update it as new data arrives, reducing errors and saving time.