Excel Help: August 2013

Waterfall graph is a floating bar graph used to pictorially depict movement of any parameter or how various parts contribute to a whole parameter. For e.g. how the Headcount or profit has moved over last week, last month etc. OR various components of Net profit and so on. Given below is the example of a sample waterfall graph.

Please note that in the above graph values moves from Positive to negative and vice-versa, which makes process of making this graph little complicated. If your data is not going to cross 0 axis (i.e. not going to move from positive to negative) then please click HERE

to see steps of making simple waterfall graph.

Steps to make waterfall graph

Step 1: Make the data table.

Create the data table for base values and movements in the below mentioned format.

Since the values moves from positive to negative and negative to positive, we will have to create another table linked to the above table

In the table above, I have used two dummy columns (cells with gray background) which are not used in graph, but are used for calculating other values
Before – Running total before the current row value

Formula – =SUM($B$2:C2)

After – Running total after the current row value

Formula – =SUM($B$2:C3)

Axis Label – Self explanatory
Base Value – For Start and end points

Formula – =IF(B2<>0,B2,0)

Blank – For a hidden series for adjustment

Formula – =IF(J2*K2>0,IF(J2*C2>0,J2,K2),0)

Positive Bars – Used for depicting bars above 0 (Horizontal axis)

Formula – =IF(J2>0,IF(K2>J2,C2,IF(K2<0,J2,ABS(C2))),IF(K2>0,K2,0))

Negative Bars – Used for depicting bars below 0 (Horizontal axis)

Formula – =IF(J2<0,IF(K2<0,-ABS(C2),J2),IF(K2<0,K2,0))

Step 2: Make the graph

Select the second data table (Only upto Negative Bar column) and Click Insert --> Column Chart --> Stacked column (this will insert stack bar graph as shown in the screenshot below)

Select the legend and press delete to remove legend
Select adjustment series and hide it by selecting No shape fill and no shape outline from format menu.

Apply same Shape colour to Positive and Negative Bars. So that it appears as a single bar while crossing the Horizontal axis.

Change the Axis label setting to Move the axis label below the negative values. Right click on Axis --> Format Axis --> Change Axis Label property to “Low”