Creating a Stacked Waterfall Chart in Excel 2010: A Step-by-Step Guide

The stacked waterfall chart, also known as bridge or cascade charts, is a powerful data visualization tool used in business to show how a value changes from one state to another through a series of intermediate (positive and negative) changes. In this article, we will take you through 4 simplified steps of creating this non-standard advanced waterfall chart using Excel 2010.

Step 1: Preparing the Data

Before we start creating the chart, it's essential to reorganize the data and introduce new columns. The first step is to identify the values that need to be plotted on the x-axis and the corresponding y-values for each segment of the chart.

Step 2: Creating the Cumulative Column

The cumulative column provides the starting point for the floating negative and positive series. To get the start value, type =D3 in cell F3 and then copy the formula in cell F8. Alternatively, you can use the formula E3+D4 in cell E4.

Step 3: Creating the Invisible Column

The invisible column provides support or the starting point for the floating negative and positive series. To get the start value, type =D3 in cell F3 and then copy the formula in cell F8.

Step 4: Creating the +ve Change Column

The rationale of this column is that if the change is positive (D4>0), then this positive value's starting point will be the previous total (E3). If there is a decrement, then the absolute value of this negative change will be supported by a series whose value is current row cumulative total (E4). The formula used here reads =IF(D4>0,E3,E4).

Step 5: Creating the -ve Change Column

The values in this column provide support or the starting point for the floating negative and positive series. To get the start value, type =D3 in cell F3 and then copy the formula in cell F8.

Step 6: Plotting the Chart

Once you have created all the necessary columns, it's time to plot the chart. Go to the 'Insert' tab and select 'Waterfall' from the charts group. Then, select the data range that includes all the columns you've created.

Tips and Tricks:

* Use the formula =IF(D4>0,D4,NA()) to return these values, otherwise return an error.

* The stacked waterfall chart is one of the standard charts in Excel 2016, but for those with MS Excel 2013 and lower versions, you can follow these steps.

In this article, we have taken you through a step-by-step guide on creating a stacked waterfall chart using Excel 2010. This chart is an excellent way to present income statement components, how a product price has changed over the last few years, etc. With these simple steps, you can create a stunning visualization that will help your audience understand complex data more easily.

Stacked waterfall chart excel 2010