How to Create a Waterfall Chart in Excel?
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.
Follow the below steps to create a Waterfall/ Bridge 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. Click Here to know more about the IF Function in Excel.
Similarly, add the formula =IF(B2>0,B2,0) to the Column, RISE.
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: 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 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!