How to Create a Stacked Waterfall Chart in Excel (With Easy Steps)

If you are looking for ways to create a stacked waterfall chart in Excel, then this article will be useful for you. A stacked waterfall chart helps visualize the gradual changes of factors over time clearly. So, let’s start with our main article to know the procedure of creating a stacked waterfall chart easily.

Download Workbook


3 Steps to Create a Stacked Waterfall Chart in Excel

Here, we have the following dataset containing the records of the change in prices of product “X” from the year 2015 to 2021. Using the following dataset we will try to plot a stacked waterfall chart to show the changes over years explicitly through this graph.

how to create a stacked waterfall chart in excel

We have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.


Step-01: Modifying Dataset to Create a Stacked Waterfall Chart in Excel

Firstly, we need to modify our dataset by adding some values after calculation. For this purpose, we have added an extra column Start Line prior to the Base Value column.

how to create a stacked waterfall chart in excel

➤ Type the following formula in the second cell of the Start Line column corresponding to the year 2016.

=E4

It will link up the value of the increment in cell E4 to cell C5.

modifying dataset

➤ Apply the following formula in the next cell C6.

=C5+D5+E5

Here, C5, D5, and E5 are the values of the Start Line, Base Value, and Increment columns of the previous row (Row 5).

modifying dataset

➤ Press ENTER and drag down the Fill Handle tool.

how to create a stacked waterfall chart in excel

In this way, we have copied the formula all over the way from cell C6 to cell C9.
We can see that the correct formula has been copied through these cells by checking the formula on the last cell (cell C9).

=C8+D8+E8

Here, C8, D8, and E8 are the values of the Start Line, Base Value, and Increment columns of the previous row (Row 8).

modifying dataset

➤ Add up all of the values of the Base Value and Increment columns by using the following formula in cell E10.

=SUM(D4:E9)

Here, the SUM function will add up all of the values in the series D4:E9.

modifying dataset

After pressing ENTER, we will get the total value of $55,680.00 as the 2021 End price.

how to create a stacked waterfall chart in excel


Step-02: Inserting Stacked Column Chart to Create a Stacked Waterfall Chart

In this step, we will plot a stacked column chart using the following dataset.

how to create a stacked waterfall chart in excel

➤ Select the data range and then go to the Insert Tab >> Charts Group >> Insert Column or Bar Chart Dropdown >> 2-D Stacked Column Option.

insert stacked column chart

Then, we will have the following chart.

insert stacked column chart


Step-03: Modifying Stacked Waterfall Chart

Now, we will modify the following chart to make it look like the stacked waterfall chart.

how to create a stacked waterfall chart in excel

Firstly, we will hide the Start Line series from this stacked column chart.
➤ Select the Start Line series and then Right-Click here.

modifying chart

➤ Click on the Fill dropdown and then select the No Fill option.

how to create a stacked waterfall chart in excel

In this way, we have made the Start Line series invisible.

modifying chart

Here, we can see that the color of the 2015 Starting Price column and 2021 End Price column is similar to the Increment series. So, we have to change the color of these two columns to differentiate them as they are quite different from the Increment series.
Double-click on 2015 Starting Price column first, then Right-click here.

modifying chart

➤ Click on the Fill dropdown and then select any color (here we have selected the Red color).

how to create a stacked waterfall chart in excel

In this way, we have changed the color of the 2015 Starting Price column.

modifying chart

➤ Likewise, change the color of the 2021 End Price column.

how to create a stacked waterfall chart in excel

Now, we will increase the width of the columns of the chart to make them more clear.
➤ Select any series of the chart and then Right-click here.
➤ Choose the Format Data Series option.

modifying chart

Then, on the right portion, the Format Data Series wizard will appear.
➤ Go to the Series Option and then decrease the Gap Width value.

modifying chart

So, we have decreased the Gap Width from 150% to 26%.

modifying chart

Then, we will have the final look at the chart like the following.

modifying chart

Moreover, you can change the chart title to “Price Changes of product “X” from 2015 to 2021”.

how to create a stacked waterfall chart in excel


Practice Section

For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.

practice


Conclusion

In this article, we tried to create a stacked waterfall chart in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

2 Comments
  1. This method does not seem to work for negative increments, as it plots them below the x-axis.

Leave a reply

ExcelDemy
Logo