How to build a Waterfall chart in Excel

A Waterfall chart???? Yes, in this post we are going to see what I mean by that.

The Waterfall chart in Excel is a type of chart that includes what appears to be floating columns, which help to better visualize the contribution of all parties to the total.

Let's assume we have the following data in the range A1:B7

Summing all the data we get a total of 11,109.

As shown in the table we have an initial value of 5,000 and then the factor 1 contributes with 1,500, factor 2 reduces it in 2,404, factor 3 increases it in 8,945 , factor 4 reduces it in 4,500 and the last one adds 2,568, making a total of 11,109, which is an increase of 6,109 compared with the baseline.

How to create the Waterfall chart

There are a few steps to go through.

Step 1. Rearranging the data - making calculations

The first thing to do is rearrange the data, doing some extra calculations.

We add 4 columns:

• Initial / final - it will only have data for the initial and final values
• Positive - it will have positive data for positive data and 0 for negative, calculated as MAX(value, 0)
• Negative - it will the absolute value for the negative data and 0 for negative, calculated as -MIN(value, 0)
• Invisible - that show the starting point of each factor, calculated as = SUM($C2:$E2) -F3 for cell C3 (Factor 1 - Invisible series)

Step 2. Inserting a stacked column chart

We now insert a stacked column chart from the table created in step 1, but only including Start/End, Positive and Negative columns.

Step 3 - Clean and format the chart

We now starting to make the chart look pretty.

Step 4 - Add invisible series

We now add the Invisible series to the chart.

Step 5 - Make the invisible series not visible

We now have to make the Invisible series not visible, by selecting the series, right click and go to Format data series, and choose No Fill & No Line (border).

Step 6 - Adding data labels

Right-click in a series and select Add Data Labels. Repeat for all series.

Step 7 (bonus) - Shapes can also be added

For more clarity, we can also add shapes to the series. Select a shape we like by going to Insert > Shapes.

Now we format the shape however we like and we copy it and go to the chart and click in the chosen series and paste it.

Download

To download the example file click on the following link:

Waterfall chart

Subscribe and get all entries via email

You can do it by providing your email into "Join the newsletter" box in the right sidebar.

Or follow me via Social Media

Facebook, Twitter and Google+