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.


Watch Video – Create a Stacked Waterfall Chart in Excel



How to Create a Stacked Waterfall Chart in Excel: 3 Steps

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

Read More: How to Create Stacked Waterfall Chart with Multiple Series 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

Read More: How to Make a Waterfall Chart with Multiple Series in Excel


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

Read More: How to Create a 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


Download Workbook


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.


Related Articles


<< Go Back to Waterfall Chart in Excel | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo