How to create waterfall chart in Excel 2013

Waterfall chart (or Bridge chart) is great tool to analyze how an initial value is affected by series of intermediate positive or negative values.

Generally, the initial and final values are represented by whole columns, while the intermediate values shown by floating columns. Again, let’s show positive values with GREEN bars and negative values with RED bars.

For example, we take data below to create waterfall chart in Excel 2013:

To create a simple waterfall chart in Excel 2013 follow below steps:

DATA ARRANGEMENT

Insert additional columns between the given data. Here we will add 4 columns >>> "Ends", "Rise", "Fall", "Hide"

Ends – used to get the columns pertaining to first and last values

Rise – positive values contributing to increase in initial value

Fall – negative values (absolute) contributing to decrease in initial value

Hide – calculates values required to get starting point for Rise or Fall

INSERTING FORMULAS

Cell B2: =F2 (initial)

Cell D3: =MAX(F3, 0)     >>>>    Drag this formula to D8 (don't include FINAL row)

Cell E3: =MAX(-F3, 0)    >>>>    Drag this formula to E8 (don't include FINAL row)

Cell C3: =B2+C2+D2-E3   >>>>    Drag this formula to C8 (don't include FINAL row)

Cell B9: =F9 (final)

CREATE CHART

Now we have the data properly organized and formulas in place. Let's start creating the stacked column chart

1. Select the data including the row and column headers; exclude "ORIGINAL" column

2. Go to "INSERT" tab >>> Column Charts >>> Stacked Column

HIDDEN SECRET

Coming to the hidden secret of the Waterfall chart, we need to apply trick on "Hide" values (basically HIDE them).

1. Select the "Hide" series by clicking on any column, then right-click and select "Format Data Series…"

2. This will open "Format Data Series" dialog box on the right side

3. Press on "Fill" icon and select "No fill" and "No line" option

FORMAT WATERFALL CHART

We have the basic structure of the chart ready. It's time to do little formatting.

Let's first decrease the GAP between the columns so that its visually appealing.

Double-click any column >>> Series Options >>> Gap Width >>> 10%

Quickly change colour of different columns: Ends >>> BLUE; Rise >>> GREEN; Fall >>> RED

Select Series >>>  Right-click >>> Format Data Series >>> Fill >>> Choose Colour

Add Data Label to make values of the columns visible.

Select Series >>> Right-click >>> Add Data Labels

Finally, remove unnecessary data labels and legends from the chart