How to make waterfall graph in excel

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 have moved over last week, last month etc. OR various components of Net profit and so on. In the screenshot below, example 1 depicts the headcount movement w.r.t. last month and example 2 shows the various components of Net profit.

Example 1: (Headcount movement w.r.t. last month)

Example 2: Various component of P&L

Below mentioned are the steps to make waterfall graph (example 1 above).  

Step 1: Make the data table.

1. Axis label – Self explanatory

2. Adjustment – Used for hidden bars. (Use formulas to derive these values)

a.  For Base bar (bar starting from the X axis) – Keep this as 0 (Zero).

b.  For addition bar - It is equal to Sum of all previous points. In our example “New Additions” is the bar of addition and adjustment value is same as opening HC (since there is only one point before the current bar).

c.  For reduction bar - It is equal to sum of all previous points minus the value of current point. In our example “Attrition” is the bar of reduction and adjustment value is calculated as 1000 (opening HC) + 200 (New Additions) – 150 (Attrition)

3.    Values – Values of the respective parameter



Step 2:  Insert stack bar graph

1.  Select the data table

2.  Click Insertà Column Chart à Stacked column (this will insert stack bar graph as shown in the screenshot below)

Step 3: Hiding/Deleting unwanted things and adding data labels.

1.  Select the legend  and press delete

2.  Select grid lines and press delete

3.  Select adjustment series (bars in blue) and hide it by selecting No shape fill and no shape outline from format menu.

4.  Select Values series and select Layout à Data labels à Center

5.  Change other formatting like font color and series color etc. as per your requirement.

That’s it, your waterfall graph is done. Finally it should look like the below…

The steps mentioned in this post works well if the values are positive. Visit Creating Advanced Waterfall Graph for making waterfall graph with negative values.