How to Create a Waterfall Graphs in Excel

Tutorial: Waterfall Graphs in Excel

Ever wondered about how changes in some parameters are best represented by any MS-Excel plot? For instance, consider a commodity whose prices change annually. The question constantly troubling my mind was how to represent the changes in the most suitable form. As it were, I stumbled upon the best possible solution to my problem. The waterfall graphs in MS-Excel!

As per our example, consider the following data table. Price of a commodity at launch was $200 and changed annually as below. $200 is the initial price. Subsequent changes are shown in the following intervals. $225 is the final price of the commodity.

Make the elaborate table required for the waterfall chart.

Use following formulas:

For C2: =B2

For D3: =MIN(SUM(B$2:B2),SUM(B$2:B3))

For E3: =MAX(B3,0)

For F3: =-MIN(B3,0)

Copy the above formulas up to D7,E7 and F7.

Next select the data omitting price column and plot the stacked column chart.

Make the following formatting:

1) Remove the grid lines.

2) Format the blank column and use white color both in shape fill and shape outline.

3) Remove the legend.

4) The axis title can be inserted by going to layout menu and choosing title axis option.

Eventually, the required graph will be obtained as below.

This approach of using the formulas for making the data table and plotting the corresponding graph seems to be tedious as it involves using some cumbersome formulas. Thus, an alternate method has been suggested with much simpler formulas for data creation.

So, step in my boots and be ready to steal the skills for another method. Consider the same data table as used above.

Elaborate the data table as shown in the figure below for plotting the graph.

Use the following formulas in data creation.

C2: =B2

C8: =SUM(B2:B7)

D3: =SUM(B$2:B2)

E3: =SUM(B$2:B3)

Now, copy the formulas of D3 and E3 up to D7 and E7 respectively.

Select the data omitting the price column and insert a line graph.

In the legend, right click on the End and change the graph type to column.

Next, select any line. Go to layout up/down bars

Make the following formatting:

1) Remove grid lines.

2) Delete the legend.

3) Format the lines and remove the color. Right click on any line shape outline no outline.

4) Change the color of up/down bars. Right click on bar shape fill

5) Insert the axis title as explained above.

Eventually, the graph obtained will look like the image below.

But then, I came across a situation where I had to plot the similar compound graph for two different items. The compound stacked column or compound line graph may also be plotted using similar method. I am demonstrating a method to plot compound graph using line graph method.

Consider data table as shown below.

Formulas used are as under.

E3: =sum(B$2:C2)

F3: =sum(B$2:C3)

G3: =sum(B$2:C3)

H3: =sum(B$2:D3)

Copy the above formula up till E7, F7, G7 and H7.

Next, select the A1-B8 and E1-H8. Insert the line graph.

Then, transfer the before B and after B to the secondary axis. Right click on the line format data series series options secondary axis. Delete the secondary axis.

Next, select after B or before B line and insert up/down bars from layout menu.

Then, select before A or after A line and insert up/down bars from layout menu.

Make the following formatting:

1) Remove grid lines.

2) Delete the legend.

3) The plot lines should have no outline.

4) Change color of the up/down bars.

Depending upon the type of data that is required to be presented and user’s interest, the method of approach can be altered.