How to Create a Waterfall Chart in Excel

Step 1: Create a data table. 

So, let’s create the data table with a Start Value as shown below.

Now, make three additional columns as shown below.

Note that I’ve added an END row after the months.

In the FALL Column, we’ll enter the formula: =IF(B2<0,-B2,0) and then apply it to all the cells of the column. 

Note: If you want all the values in the waterfall chart to lie above zero, you need to enter the minus(-) sign before the second cell reference (B2) in the formula and two minus signs will make a plus.

Similarly, add the formula =IF(B2>0,B2,0) to the Column, RISE. It means if the values in cell B2 is greater than 0 then, all the positive number will be displayed as positive and negative as zero. displayed

Now, add the formula =B4+D4-C5 in cell B5 and apply it to all cells till the end of the column including the END row.

Step 2: Create a Standard Stacked Column Chart

Now select your entire table, excluding the SALES Column, and go to Insert. Locate the Stacked Column Chart under the Column Charts section and click on it.

It will look something like this

Step 3: Conversion to a Waterfall Chart

Click on any of the blue bars to select them all. Then right-click and select Format Data Series and set the following values as shown.

The final result will look like this

Now, let’s make a couple of more tweaks to make our Waterfall chart more presentable.

Click on any of the Bars once and then right-click. Select Format Data Series and set the Gap Width to a small in the range of 5-25%

Now, on your chart select the transparent bar just above END and double-click on it. Then, go to Fill > Solid Fill > Fill Color > Color > Green

You’re all set. Your Waterfall Chart will now look like this.

You can try experimenting will colors, labels, etc., and see what works out best for you!

A waterfall chart is used to visualize data and understand the cumulative effect of sequentially introduced positive or negative values. For this article, we’ll see the sales data of a company from January till December. It helps to see the changes that occur between a starting point and an ending point.