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 (Easy Steps)
The following dataset contains records of price changes of product “X” from 2015 to 2021. A stacked waterfall chart will show the changes over the years.
We have used Microsoft Excel 365 version for this article. Other versions can be used.
Step 1 – Modifying Dataset to Create a Stacked Waterfall Chart in Excel
Modify the dataset by adding values. Add an extra column named Start Line before the Base Value column.
➤ Type the following formula in the second cell of the Start Line column corresponding to 2016.
=E4
It returns the value of the increment from E4 to C5.
➤ Apply the following formula in C6.
=C5+D5+E5
C5, D5, and E5 are the values of the Start Line, Base Value, and Increment columns of the previous row.
➤ Press ENTER and drag down the Fill Handle tool.
It copies the formula from C6 to C9.
=C8+D8+E8
C8, D8, and E8 are the values of the Start Line, Base Value, and Increment columns of the previous row.
➤ Add all of the values of the Base Value and Increment columns by using the following formula in E10.
=SUM(D4:E9)
The SUM function adds all of the values from D4:E9.
Press ENTER
It returns the total value of $55,680.00 as the 2021 End price.
Read More: How to Create Stacked Waterfall Chart with Multiple Series in Excel
Step 2 – Inserting Stacked Column Chart to Create a Stacked Waterfall Chart
This is the dataset:
➤ Select the data range and go to Insert Tab >> Charts Group >> Insert Column or Bar Chart Dropdown >> 2-D Stacked Column.
The following chart is showcased:
Read More: How to Make a Waterfall Chart with Multiple Series in Excel
Step 3 – Modifying a Stacked Waterfall Chart
How to modify the following chart.
Hide the Start Line series from this stacked column chart.
➤ Select the Start Line series and Right-Click.
➤ Click Fill dropdown and select the No Fill option.
The Start Line series is invisible.
When the color of the 2015 Starting Price column and the 2021 End Price column are similar to the Increment series, changing the color is necessary.
➤ Double-click the 2015 Starting Price column first, then Right-click.
➤ Click Fill dropdown and select any color (Red here).
The color of the 2015 Starting Price column was changed.
➤ Change the color of the 2021 End Price column.
Widen the columns for better visualization.
➤ Select any series of the chart and Right-click.
➤ Choose the Format Data Series option.
On Format Data Series:
➤ Go to the Series Option and then lower the Gap Width value.
Gap Width was lowered from 150% to 26%.
This will be the final chart:
Change chart title to “Price Changes of product “X” from 2015 to 2021”.
Read More: How to Create a Waterfall Chart in Excel
Practice Section
Practice using the table below:
Related Articles
- How to Make a Vertical Waterfall Chart in Excel
- Excel Waterfall Chart with Negative Values
- Excel Waterfall Chart Change Colors
- Excel Waterfall Chart Total Not Showing
<< Go Back to Waterfall Chart in Excel | Excel Charts | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
This method does not seem to work for negative increments, as it plots them below the x-axis.
Hi Alex,
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.