How to Create a Stacked Waterfall Chart with Multiple Series

What Is a Stacked Waterfall Chart in Excel?

A stacked waterfall chart is a special type of graph that illustrates how values change across different categories. It resembles a series of bars stacked on top of each other. However, unlike a standard bar chart, a stacked waterfall chart can display multiple sets of data side by side within each category. This allows you to visualize how various factors impact the overall result.

Example of a Single-Series Waterfall Chart: Consider the dataset showing the cash flow of a store.

Cash flow dataset of a store

The waterfall chart below represents this data.

Waterfall chart of the departmental store.

Let’s walk through the steps to create a stacked waterfall chart using an example dataset for ABC company’s sales flow from January to June. In this dataset:

  • “Rise” indicates profit or positive cash flow.
  • “Fall” indicates loss or negative cash flow.

Cashflow chart of ABC company

Step 1: Modifying the Dataset

  • Add a column to store the base value for each category.
  • Start the initial base value at 10,000.

Inserting base column

  • Calculate the sales flow using the formula in cell E6:
  • Drag down the Fill Handle up to cell H11.

Calculating Sales Flow

  • Adjust the base value based on the sales flow by entering the following formula in cell C6:
  • Drag down the fill handle up to the cell C11.

Calculating base value

If the end base value is below 10,000, the company faces losses. If it’s higher, the company gains profit. For example, an end cash flow of 16,300 indicates profitability.

Read More: How to Create a Stacked Waterfall Chart in Excel

Step 2:  Inserting the Stacked Waterfall Chart

  • Select the Data Table:
    • Highlight the entire table containing your data (excluding the sales flow column).
  • Insert the Chart:
    • Go to the Insert tab.
    • From the Charts group, select the 2-D Stacked Column chart.

Inserting 2D stack column chart

You’ll now have a stacked chart, but it won’t look like a Stacked Waterfall chart yet.

The stacked waterfall chart with multiple series

Read More: How to Make a Waterfall Chart with Multiple Series in Excel

Step 3: Formatting the Stacked Waterfall Chart

  • Remove Base Stacks:
    • Double-click on any of the base stacks (the initial and final columns) in the chart.

Selecting the base stack

    • In the Format Data Series window that appears on the right, go to the Fill and Border options.
    • Select No fill and No line respectively.

Changing the Border and Fill Color of the Base Stacks

Your chart will now resemble a Stacked Waterfall chart.

Formatted Stacked Waterfall Chart

  • Additional Modifications:
    • For visual appeal, consider adding base stacks at the starting and ending points of the chart.
    • Adjust the grid width as needed.
  • Customization:
    • Excel offers various customization features. You can:
      • Add data labels.
      • Change colors.
      • Adjust axes.
      • Explore other options based on your specific use case.

Added start and end base column into the stacked waterfall chart with multiple series

Read More: How to Create a Waterfall Chart in Excel

Things to Remember

  • The default waterfall chart feature in Excel 2016 and later versions allows creating a waterfall chart with a single series.
  • Ensure the chart size is appropriate for its presentation medium (e.g., paper, slide, online platform).
  • When building a waterfall chart with multiple series using the default 2D stacked column chart:
    • Group positive values (e.g., sales, earnings) together.
    • Group negative values (e.g., losses, expenses) separately.
  • Calculate the base value considering revenue items add up and expense items subtract from the running total.
  • Use distinct colors for positive and negative values within each series.
  • Consider adding data labels for clarity, especially when dealing with multiple series.

Frequently Asked Questions

  1. Difference between Waterfall and Stacked Waterfall Chart:
    • Waterfall Chart: Shows cumulative changes in data over categories, highlighting positive and negative impacts on a starting total.
    • Stacked Waterfall Chart: Extends the waterfall chart by incorporating multiple series, making it useful for financial data analysis and budget comparisons.
  2. Limitation of the Stacked Waterfall Chart:
    • Complexity: Too many series or categories can visually clutter the chart.
    • Readability: Numerous stacked segments may affect readability.
  3. Other Names for Waterfall Charts:
    • Flying Bricks Chart, Mario Chart, or Bridge Chart.
  4. Multiple Series in a Stacked Waterfall Chart:
    • Yes, you can have as many series as needed to visualize complex data with multiple contributing factors.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

<< Go Back to Waterfall Chart in Excel | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mizbahul Abedin
Mizbahul Abedin

Md Mizbahul Abedin, BSc, Textile Engineering and Management, Bangladesh University of Textiles, has been working with the ExcelDemy project for 11 months. Currently working as an Excel and VBA Content Developer who provides authentic solutions to different Excel-related problems and writes amazing content articles regularly. He has published almost 20 articles in ExcelDemy. He has passions for learning new things about Microsoft Office Suite and Data analysis. Besides, he also likes to travel, photography, international politics, and read... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF