Waterfall chart template download with instructions (supports negative values)

This tutorial shows you how to use the waterfall chart template file Excel that supports negative values and columns representing no change.

Download here to download: Waterfall Chart Template

This is the waterfall chart that allows negative values and columns with no change (Change 6).

How to use the template?

The template is ready to use right out of the box. All you need to do is change the input cells

• Enter the values for each waterfall column in row 5
• Enter the desired axis labels in corresponding in row 4

Note that input cells have a orange background and blue text color.

How to add more columns?

1. Try to add and delete columns before you have made any changes to the template. If not possible:
   1. Backup the file if you made changes and you want to be safe in case you break the file
   2. Save the input values on row 4 to 5 so that they can be used later.
2. Highlight entire column I. To highlight an entire column, click on the Column Heading (the letter “I” at the top of the spreadsheet). Highlighting only a part of the column may break the file.
3. Right click -> Copy
4. Do not click any where else, and right click on the current selection -> Insert Copied Cells
5. Repeat step 3 to 4 until you get the number of columns desired
6. Highlight column D
7. Right click -> Copy
8. This step is really important as it fixes the shifted formula. Highlight all the new columns you created in step 3 to 5 and previous column I (would be shifted to the right so it is somewhere else now).
9. Paste formulas over of those columns: Paste special -> formulas (Shortcut: Alt e s t Enter)

Column formula pasted correctly: The cell is referencing a cell 1 column to the left.

Formula not pasted correctly: The reference is to a cell (H5) 2 columns away from it.

How to take away a column?

1. Try to add and delete columns before you have made any changes to the template. If not possible:
   1. Backup the file if you made changes and you want to be safe in case you break the file
   2. Save the input values on row 4 to 5 so that they can be used later.
2. Highlight entire column E. To highlight an entire column, click on the Column Heading (the letter “E” at the top of the spreadsheet). Highlighting only a part of the column may break the file.
3. Right click -> Delete
4. Now you will notice that there is a bunch of “#REF” errors in the new column E
5. Copy the formulas over to the column with errors: Highlight entire column F -> Right click -> Copy
6. Highlight the entire column E (the column with all the errors)
7. Paste formulas over of those columns: Paste special -> Paste as values (Shortcut: Alt e s t Enter)

How does it work? What is behind the blackbox?

This section is not necessary for using the actual template. But it is useful for those who are curious about how the chart actually works.

The waterfall chart is a stacked bar chart, each column has 8 series (each is a row in the table below), each representing:

• Upward change (green) in the positive region in the bar chart (values above the x-axis)
• Downward change (red) in the positive region
• Spacer to properly position the bar at the right height in the position region
• Upward, downward and spacer in the negative region (values below the x-axis)
• Positive and negative optional 0-change bars: The back line you will see if a column does not change from the previous one (See the column “Change 6” from the waterfall chart example at the top of this tutorial)

The actual calculations of the length of each bar (green, red, spacer, etc.) is done at row 26 to 54. There are four sections, each calculating the appropriate bar lengths based on 4 cases:

• Previous value is positive, the current value is positive
• Previous value is positive, the current value is negative
• Previous value is negative, the current value is positive
• Previous value is negative, the current value is negative

You can look through each cell to understand how each of the bars (positive – green, negative – red, and the spacer) are calculated on each of those cases.  

Row 22 then calculates which case to use (1 to 4) based on whether the previous and current values are positive or not (rows 21 to 22). Row 24 indicates if the current value is the same as the previous value, in that case the option bars are used, and all other bars are zeroed out (rows 11 – 18).

These cells determine which section of bar lengths should be used.

There you go! Hope you enjoyed this brief tutorial and find the template useful.