Waterfall chart in Excel tutorial | Download free template

In this Microsoft Excel tutorial we will learn to make a waterfall chart (bridge chart). This tutorial applies to Excel 2007 and 2010. Follow these easy steps and you are all set. You can see the finished chart below.

Waterfall chart in Excel



First create a new Excel sheet and type in the following data. The total row contains all the component values and column 1 through 5 contain the value for each individual component.



Waterfall chart in Excel pic 1

Next count SUM of the values  row "code" (row 2) to cell B3.

Waterfall chart in Excel pic 2

Repeat this for the whole column 1 and you should get the following results.

Waterfall chart in Excel pic 3

Then select the entire table and create a stacked column chart from Insert tab > Charts group > Column > Stacked column.

Waterfall chart in Excel pic 4

You should get the following chart as a result.

Waterfall chart in Excel pic 5

Select a Chart layout of your liking from Design tab > Chart layouts group.

Waterfall chart in Excel pic 6

Next we will hide the unneeded blocks from the column. Select the bottom block from the column "customization" and go Format tab > Shape fill > No fill. You should get the following result.

Waterfall chart in Excel pic 7

Repeat this for the rest of the columns (NOT for the last one) as well and you should have the following result.

Waterfall chart in Excel pic 8

Next we will create the lines bridging the columns together. For this type in the following to the table.

Waterfall chart in Excel pic 9

Next click on the chart and you see the blue frame surrounding our data area. Grab from the bottom right corner of the blue frame and drag the frame over the values in "line 1". You will then see two new blocks in your chart, as seen in the image below (orange blocks).

Waterfall chart in Excel pic 10

Click on either of the orange blocks and change the chart type to line from Design tab > Type > Change chart type.

Waterfall chart in Excel pic 11

You should now have the following result.

Waterfall chart in Excel pic 12


Next click on the chart and you see the blue frame surrounding our data area again. Grab from the bottom right corner of the blue frame and drag the frame over the values in "line 5". You will then see the rest of the connecting lines in your chart, as seen in the image below.
Waterfall chart in Excel pic 13

Format the new lines as you wish from Format tab > Shape styles group > shape outline. I have chosen to make the lines dashed and black, weighing 1/2 pt.

Waterfall chart in Excel pic 14


Now you should have the following result.
Waterfall chart in Excel pic 15

Next delete the legend on the right and  hide the gridlines by right clicking on the chart area and select Format Gridlines > no line.

Waterfall chart in Excel pic 16

Click on the chart area and make the background color light grey from Format tab > Shape styles group > Shape fill.

Waterfall chart in Excel pic 17

Now all you need to do is to write down the title for the chart and you have the finished product.

Waterfall chart in Excel pic 18

Free Download Waterfall chart template Excel


Download link for the finished  Excel Waterfall chart spreadsheet (file hosted by mediafire)

1 comment:

  1. Thanks, Ilkka. Very useful article. Now I better understand how it works. But you'll be forced to repaint columns and move labels after data change. I use add-in which does it all automatically and supports charts with crossing axis and subtotals. Look at this http://fincontrollex.com/?page=products&lang=en

    ReplyDelete