How to Create Stacked Waterfall Chart with Multiple Series in Excel

In this article, you are going to learn how to create a Stacked Waterfall Chart with multiple series in Excel. We will explore the benefits, best practices, and practical applications of the stacked waterfall chart.

A powerful data visualization tool is frequently needed to visualize complicated financial data, budget breakdowns, or company performance measurements. A Stacked Waterfall Chart is a very handy tool to represent those data more effectively.

This technique for displaying data builds on the waterfall chart and offers a comprehensive view of multiple series.

Download Practice Workbook

If you want a free copy of the illustrated workbook we discussed during the presentation, please click the link below this section.

What Is a Stacked Waterfall Chart in Excel?

A stacked waterfall chart in Excel is a special kind of graph that shows how values change over different categories. It’s like a series of bars stacked on top of each other. But, instead of just showing one set of data, it can display multiple sets of data side by side on each category. This helps you see how different things affect the overall result.

A typical single-series waterfall chart is given below. The dataset shows the cash flow of a store.

The waterfall chart of the cash flow is given below.

How to Create Stacked Waterfall Chart with Multiple Series in Excel: 3 Handy Steps

In this section, I will show you how to make a stacked waterfall chart with multiple series in Excel in 3 steps. But before doing that, let’s look at the dataset first.

The dataset shows the cash flow of ABC company. The company generally sells Mobile and PC. It shows the sales flow of the company from January to June. In the dataset, Rise means profit or positive cash flow and fall means the loss or negative cash flow in the company.

Now, using this dataset, we will produce a  Stack Waterfall chart. Now, let’s see the steps one by one.

Step 1: Modifying Dataset

The waterfall chart, as we know, will have various bases for each column or category. We must specify the base value for each category in order to place the base values of all the categories in the waterfall chart in their corresponding positions. So, insert a column to store the base value.

The initial base value will start from 10000.

• Before calculating the base value you need to know about the sales flow first. The sales flow indicates the total profit or loss of the company in that particular month. The formula of sales flow is

• Put the formula in cell E6 first and then drag down the Fill Handle up to cell H11.

If you notice the formula you can easily understand that, we are adding all the profits and subtracting the losses. If the final output is positive then you will see a positive sales flow which indicates profit in that corresponding month. And if the final output is negative then you will see a negative sales flow in the dataset which indicates loss in that corresponding month.

We took 10000 as starting base value. The base value is working like an initial investment here in this ABC company. We need to add the base value to the sales flow. So according to the sales flow the base value will change. To calculate the base value-

The formula for the Base value is

• Select cell C6 and then enter the following formula.

• Drag down the fill handle up to the cell

If you see the End base value is below 10000 then the company is facing losses and if the base value is higher than 10000, the company is gaining profit. Here the End cash flow is 16300, so the company is profiting.

Step 2:  Inserting Stacked Waterfall Chart

In this part, we are going to insert a 2-D Stacked Column chart. To do that-

• First, select the entire table without sales flow.
• Then, go to the Insert tab, and from the Charts group, select the 2-D Stacked Column chart.

•       As a result, you will get a stacked chart like this below.

Since it doesn’t seem to be a Stacked Waterfall chart, we need to modify it to make it one.

Step 3: Formatting Stacked Waterfall Chart

To look like a stacked waterfall chart, you need to remove the base stacks from the chart. To do that-

• Double-click on any base stacks on the chart.

• As a result, the Format Data Series window will open up on the right side of your worksheet like the image below.
• From the window, go to the Fill Series option and select No fill and No line in the Fill and Border options respectively.

• After that, your waterfall chart will look like the image below.

• We further modified the chart. We added the base stack at the starting and ending point of the chart which is more appealing visually.
• We also decreased the grid width.
• You can also change the chart according to your use. Add data labels, change colors, change axis, and many more. Excel has a big number of customization features.

Things to Remember

• The default waterfall chart feature in Excel 2016 and later versions can be used to create a waterfall chart with just one series.
• Ensure that the chart is appropriately sized for the medium in which it will be presented (e.g., paper, slide, online platform). It should be easily viewable and not too small to read or understand.
• When building a waterfall chart with multiple series using the default 2D stacked column chart, place all positive values (such as sales, earnings, rise, etc.) in one group and all negative values (such as losses, falls, compensations, etc.) in another.
• When calculating the base value, keep in mind that revenue items add up to the running total, while expense items subtract from the running total. As a result, the formula for determining the base value will change for both revenue and expense items.
• Use different colors for positive and negative values within each series.
• Consider adding data labels to individual bars to show specific values, especially when the chart gets crowded with multiple series.

Frequently Asked Questions

1. What’s the difference between a waterfall and a stacked waterfall chart?

Waterfall Chart: Displays cumulative changes in data over categories, showing the effect of positive and negative values on a starting total.

Stacked Waterfall Chart: Extends the waterfall chart by incorporating multiple series, visualizing cumulative changes in each series while comparing their impacts on the starting total. Useful for financial data analysis and budget comparisons.

2. What is the limitation of the waterfall chart? 

The limitation of the stacked waterfall chart is that it can become visually complex when there are too many series or categories. With numerous segments stacked on top of each other, the chart’s readability may suffer.

3. What are the other names for waterfall charts?

The waterfall charts are also known as the Flying Bricks Chart, Mario Chart, or Bridge Chart.

4. Can I have more than two series in a stacked waterfall chart?

Yes, you can have as many series as needed, making it versatile for visualizing complex data with multiple contributing factors.

Conclusion

After reading this article, now you have a comprehensive idea about how to insert a stacked waterfall chart with multiple series in your Excel worksheet. We have gone through 3 simple steps to create and modify a stacked column chart.

Thanks for reading this article. I hope you find this article helpful. You can write your suggestions or if you have any questions, you can write them in the comment section below. Finally, visit the ExcelDemy website to learn more exciting features and capabilities of Excel.