How to Create a Stacked Waterfall Chart in Excel

You have seen various types of charts available in Microsoft Excel. But you are not going to find the stacked waterfall chart there. In this case, you have to build one for yourself. In this article, I will guide you through creating a stacked waterfall chart in Excel.

What is a Stacked Waterfall Chart?

A stacked waterfall chart is usually applied to show the change in multiple datasets or if data changes over a large range. The chart helps identify trends and transitional effects by presenting data points as upward or downward bars on the chart. A regular stacked waterfall looks like this:

Introduction to the Dataset

First of all, I need a dataset for you to demonstrate this. Here’s the one that I’m going to use for this article:

A company has six best-selling products (Product: A, B, C, D, E, and F). In 2022, the overall sale of the company was $90,000. Products C, D, and F experienced a loss, whereas Products A, B, and E made a profit. The 2023 net sale has yet to be determined in cell B9, and a stacked waterfall chart will be created for this dataset.

Easiest Way to Create the Stacked Waterfall Chart in Excel

Building a stacked waterfall chart will be a lengthy process because there is no built-in option for a stacked waterfall chart in Excel. But don’t worry! The steps are super easy to follow!

Phase 1: Reorganize the Dataset

Step_1: Right-click on any of the cells in the leftmost column.

The context menu will appear beside the cell.

Step_2: Now select Insert.

As you select the option Insert, the Insert dialog box will pop up.

Step_3: Select the entire column.

Step_4: Hit the OK button.

A column will be created in the middle of the two existing columns.

Step_5: This way, create three columns in the middle of the column Product and Product Sale Flow.

Columns B, C, and D are my newly made columns.

Step_6: Give the columns names: Base (column B), Rise (column C), and Drop (column D).

Step_7: Insert a row below the data table.

Step_8: Give the row a name, 2023 (cell A9).

Phase 2: Insert the Formulas

Now I will separate the rise and fall of prices for Product A to Product F in the column Rise and Drope.

Step_1: In cell C2, type the formula:

=IF(E2>0,E2,0)

Step_2: Press the ENTER key.

Step_3: Now copy the formula by dragging the Fill Handle (+ icon) from cell C2:C8.

Formula Explanation

• E2 is the Product Sale Flow in 2022, which is $90,000.
• E2>0 checks if the value in cell E2 is greater than 0.
• If the condition E2>0 is TRUE (meaning the value in cell E2 is indeed greater than 0), the formula returns the value of cell E2.
• If the condition E2>0 is FALSE (meaning the value in cell E2 is not greater than 0 or is equal to 0), the formula returns 0.
• Since $90,000 is greater than 0, the result in cell C2 was $90,000 in this instance.

Step_4: Now in cell D2, write the formula:

=IF(E2<0,E2,0)

Step_5: Press ENTER to apply the formula.

Step_6: Copy the formula by dragging the Fill Handle down from cell D2 to D8.

Formula Explanation

• E2 indicates $90,000, the Product Sale Flow in 2022.
• E2<0 checks if the value in cell E2 is less than 0.
• If E2<0 is TRUE, then the outcome will return the value from cell E2.
• If E2<0 is FALSE, that means the argument is not suitable for cell E2 and returns 0.
• Here, E2<0 or $90,000<0 is a FALSE argument, that’s why the result in cell D2 is 0.

Next, you will need to calculate the cumulative frequency to find out the base of the dataset.

Step_7: In cell B3, type the below formula:

=B2+C2-D2

Step_8: Hit ENTER.

Formula Explanation

• B2 is the Base.
• C2 is the Rise of sales.
• D2 is the Drop of sales.

Step_9: After that, drag the Fill Handle in cell B3:B9 to apply the formula.

So, the base sale in 2023 was $2,45,840.00.

Phase 3: Create the Stacked Waterfall Chart

I extracted all the data from column E and organized it into three new columns (B, C, and D). So, I am only going to take cell A1:D9 into consideration as the core data table.

Step_1: Select cell A1:D9.

Step_2: Go to the Insert tab.

Step_3: From the Charts group, click on the option Insert Column or Bar Chart.

A drop-down menu of different charts will come along.

Step_4: Select a column chart style from the menu.

I picked the second option from the 2-D Column here.

As you choose the column chart, you will see a bar chart like this one:

Step_5: Double-click on the bar of Base in the chart

Step_6: Select Format Data Series from the context menu.

Step_7: Select the option Fill and Line.

Step_8: Select the No Fill radio button from the Fill section.

Step_9: Also, select the No Line radio button from the Border section.

This way, you can remove the Base Sale Flow from the chart. But carefully notice that the bar for 2023 is also gone now from the chart.

In the stacked waterfall chart, the starting and ending points need to be marked with a different color.

So, to pinpoint the bars for 2022 and 2023 as the initial and final points of the chart, follow these steps:

Step_10: Double-click on the bar for 2023.

This will particularly select that bar.

Step_11: Now open the context menu by right-clicking on bar 2023.

Step_12: Choose a color of your choice other than the ones the Rise and Drop bars use.

Select the same color for the bar of 2022. For this chart, I’ve chosen the lime color.

Final Result

The stacked waterfall chart is already ready. But I made a few more edits to the chart to make it more attractive. Check this out:

Things to Remember

• Adjust the color schemes, data labels, and other chart elements to make it more readable and visually appealing.
• Review your chart, make any necessary adjustments, and ensure that it effectively communicates the cumulative impact of each month’s revenue changes.

Advantages of Using Stacked Waterfall Charts

• Clarity in Data Analysis: Stacked waterfall charts provide a clear and intuitive way to analyze data, allowing viewers to easily understand how individual components contribute to a total.
• Sequential Impact: They excel at illustrating the sequential impact of positive and negative values, making them valuable for tracking changes over time or across categories.
• Highlighting Trends: Stacked waterfalls highlight trends, showing which factors are driving growth or causing declines in a data series.
• Comparative Analysis: They facilitate easy comparison between categories or time periods, aiding in identifying outliers or significant contributors.

Conclusion

In conclusion, a stacked waterfall chart in Excel is a potent visualization tool that simplifies complex data narratives. By showcasing sequential impacts and contributions, it empowers decision-makers to gain valuable insights and make informed choices. I think you found this article helpful, and now you can create a stacked waterfall chart in Excel on your own. Thanks for following our writing!