If you wonder how to create a Stacked Waterfall Chart in Excel, then your search ends here. In this in-depth guide, we will explore the details of making attractive stacked waterfall charts in Excel. 

Financial and other revenue or sales data is represented using stacked waterfall charts, which may also be used to track changes over time and analyze the cumulative impacts of numerous causes. 

You will be able to learn the skill of creating these informative charts like a pro with the help of my detailed step-by-step instructions and seasoned guidance.

Understanding Stacked Waterfall Charts

It’s absolutely essential to understand the idea of stacked waterfall charts before we proceed with the design process. In these graphs, a sequence of positive and negative values, that add up to a total are shown. 

The flow of money, expenses, or any other quantitative data can be illustrated using these infographics very well. For financial analysts and other business professionals, stacked waterfall charts are a useful tool due to the fact that they let you pinpoint specific components inside of a dataset.

Preparing Your Data

The foundation of a great stacked waterfall chart lies in clean and organized data. Here’s how you should prepare your data for this task:

1. Gather Your Data:

Suppose you have to present the quarter-on-quarter revenue data for 3 cities where your company does business to the executive leadership team. The data is shown in the below picture:

Instead of a simple stacked column chart, you want to show a quarter-on-quarter cumulative roll-up chart, that is a Stacked Waterfall Chart, so that it shows clear insights to the top management about each city’s revenue contribution for each period. 

But, there is no readymade Stacked Waterfall Chart is available in the Excel. 

So, how to create such a chart? Well, we will use some simple tricks here and will convert a normal stacked column chart into a Stacked Waterfall Chart. Let’s begin with the data preparation in the required format for the same.

2. Arrange Your Data in Required Format:

First, you need to arrange the data in the below manner:

• Add a row below the table and name it as say – accumulated 
• Put 0 value in the first cell of this row, that is for the Opening column (cell C7)
• Use the formula [ =SUM(C4:C7) ] in cell D7 which will create the base value for each of the quarters which is the closing value for the earlier quarter exactly the way a Bridge Chart or a Stacked Waterfall Chart represents the data

• Now, drag this formula for all the time periods, that is till Qtr4 or till cell G7 in this case. 
• This Accumulated row’s value represents the opening value for that quarter or the closing value for the earlier quarter. For example, Qtr2 started with 470 USD revenue and during the quarter 2 period its 3 cities, Delhi, Noida, and Gurugram generated 150, 120, and 90 USD respectively.
• Now add a column for closing values. Here this new column is named “Closing” as shown in the below picture.

• Values for the calls of the Closing column should represent the total of the respective rows. For doing so, use the formula [ =SUM(C4:G4) ] in cell H4
• Now drag this formula till cell H6
• Leave the grand total cell that is cell H7 in the above case blank. Your data is now ready to create a nice Stacked Waterfall Chart

Creating a Stacked Waterfall Chart in Excel

Now, let’s jump into the practical part of creating a stacked waterfall chart in Excel. Follow the below steps:

• Select the entire data table from cell B 3 till cell H 7 
• Click on the Insert tab (shown with yellow highlighter) 
• Go to the Chart section and click on the Insert Column or Bar Chart icon

From the drop-down menu, select the stacked column chart (shown in red underlined) under 2-D Column type charts (highlighted in yellow)

• If you follow these steps as shown in the above picture, then a normal stacked column chart will appear as shown in the below picture.

• Now, we will use a few useful tricks to convert the above-stacked column chart into a Stacked Waterfall Char
• First, click anywhere on the chart so that the chart area is selected, and then right-click to open the popup. 
• Click on the Select Data as shown below with Red underline

• The below popup window will be opened

• Now select the last item, that is, Accumulated (shown as Blue highlighted), and click on the UP Arrow button (circled in red color) till the time this particular item comes to the top
• The window will look like the below picture
• Click on OK

• Once you take the Accumulated column to the top, your stacked column chart will be transformed.
• Now, instead of the Accumulated parts (a yellow stacked portion of the column bar) coming to the upper part of the columns, these will now be shown in the bottom part of each column and will form the base of the opening value for each period as shown in the below picture.

• Now single-click on the yellow part of the column so that all yellow stacked portions get selected
• Right-click on any of the yellow parts and click on the Format Data Series

• The format window will be opened as a panel on the right side as shown below

• Click on the first icon (Fill and Line) which is encircled with red color in the above picture
• Select the No Fill option (underlined in red)
• Once you follow the above steps and close this Format panel, the Accumulated portion of each stacked column bar will vanish and the chart will start looking like a Stacked Waterfall Chart as shown in the below picture

Beautification of the Chart

Let’s beautify this chart a bit more so that it exactly looks like a stacked Waterfall Chart. To do so, follow the below steps:

• Click on any of the gridlines and delete
• Select the Accumulated or the base values and delete those values
• The chart will start looking like the below

• Now, click on the Chart Title and change the name to a suitable title that shows the meaningful nature of the chart
• I named the chart “Zone-wise Quarterly Stacked Profit Chart”
• Select each series of the data labels and change their font to Bold and font color to white
• The final Stacked waterfall Chart will look as shown in the below picture. It provides the opening profit for each city and how each of these cities performed on a quarter-on-quarter basis.
• At the end of the year, what was the accumulated closing profit for the entire company and each city’s contribution to it.

 Conclusion

Mastering the art of creating Stacked Waterfall Charts in Excel can significantly enhance your data analysis and presentation skills. By following our step-by-step guide and implementing our expert tips, you will be well-equipped to create impactful charts that effectively convey complex financial data. 

Stacked Waterfall Charts can be a game-changer for your reports, presentations, and business insights. So, give it a try and unlock the power of data visualization with Excel.