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

Example of waterfall chart with negative values

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
(See the A to J on top, and 1 to 5 on the left? On a sidenote, I figured out how to conveniently copy cells from Excel into an image with the column and row headings, click here to learn how.)
Screenshot of input cells for the waterfall chart

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)
How can you tell if this is done right? An easy way to check is to go to row 21 of the column that was coiped, and make sure each cell references to a value in the column directly previous to it. See example:

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)
Each of these series are calculated and shown on row 11 to row 18. The sequence of the rows are opposite from what you would normally expect in the chart. That is because Excel automatically stacks each series bottom first.

Screenshot of cells used to determine bar lengths in waterfall chart

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.

12 thoughts on “Waterfall chart template download with instructions (supports negative values)

  1. Lotusaurus says:

    I’d suggest that this would be more useful if the inputs were the differences. Not many people will start off having the data for the number row; they are much more likely to have what now appears in the differences row.

  2. Ryan says:

    Is there any way to fix the data labels? When I add data labels for the bars that are in red, it comes up with “0” as the data label. The green labels work fine.

    Thanks

  3. James Keane says:

    This does not seem to work well when the first value is a negative. Is i possible to send you my file and let me know if it’s anything I’m doing wrong? I’ve followed your directions meticulously and validated the formulas are populating correctly.

    Also – I could be wrong – but are the titles (first column) of the last sections of the document labeled wrong? I don’t see any values starting with ‘Negative’.

    JPK

  4. AKE says:

    Great functionality, thanks for sharing. Couple questions 1) should the last bar should go from 0 to final value? currently it is floating in the example file. Fix: zero out cell O12. 2) is Row 4 mislabeled? it says ‘changes’, but it looks to be the moving totals, or cumulatives.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>