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.
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.
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.
➤ Type the following formula in the second cell of the Start Line column corresponding to the year 2016.
It will link up the value of the increment in cell E4 to cell C5.
➤ Apply the following formula in the next cell C6.
Here, C5, D5, and E5 are the values of the Start Line, Base Value, and Increment columns of the previous row (Row 5).
➤ Press ENTER and drag down the Fill Handle tool.
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).
Here, C8, D8, and E8 are the values of the Start Line, Base Value, and Increment columns of the previous row (Row 8).
➤ Add up all of the values of the Base Value and Increment columns by using the following formula in cell E10.
Here, the SUM function will add up all of the values in the series D4:E9.
After pressing ENTER, we will get the total value of $55,680.00 as the 2021 End price.
Read More: Excel Waterfall Chart with Negative Values (3 Suitable Examples)
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.
➤ Select the data range and then go to the Insert Tab >> Charts Group >> Insert Column or Bar Chart Dropdown >> 2-D Stacked Column Option.
Then, we will have the following chart.
Step-03: Modifying Stacked Waterfall Chart
Now, we will modify the following chart to make it look like the stacked waterfall chart.
Firstly, we will hide the Start Line series from this stacked column chart.
➤ Select the Start Line series and then Right-Click here.
➤ Click on the Fill dropdown and then select the No Fill option.
In this way, we have made the Start Line series invisible.
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.
➤ Click on the Fill dropdown and then select any color (here we have selected the Red color).
In this way, we have changed the color of the 2015 Starting Price column.
➤ Likewise, change the color of the 2021 End Price column.
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.
Then, on the right portion, the Format Data Series wizard will appear.
➤ Go to the Series Option and then decrease the Gap Width value.
So, we have decreased the Gap Width from 150% to 26%.
Then, we will have the final look at the chart like the following.
Moreover, you can change the chart title to “Price Changes of product “X” from 2015 to 2021”.
Read More: How to Make a Vertical Waterfall Chart in Excel (with Easy Steps)
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.
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.
This method does not seem to work for negative increments, as it plots them below the x-axis.
Here, I have shown the procedure of creating a waterfall chart for positive increments only. However, you can follow the article of this link- https://www.exceldemy.com/excel-waterfall-chart-with-negative-values/ – to create a chart with negative increment values.