How to create waterfall chart in Excel (bridge chart)

Step 1: Rearrange the data table

You start with inserting three additional columns in your Excel table. Let's call them Base, Fall and Rise. The Base column will be a calculated amount that is used as a starting point for the Fall and Rise series in the chart. All the negative numbers from the Sales Flow column will be placed in the Fall column and all the positive numbers will be in the Rise column.

I've also added the End row at the bottom of the Month list to calculate the sales amount for the whole year. Now move to the next step and fill in these columns with the necessary values.

Step 2. Insert formulas

The best way to complete the table is entering special formulas in the first cells in the corresponding columns and then copy them down to the adjacent cells using the fill handle.

1. Select cell C4 in the Fall column and enter the following formula there: =IF(E4<=0, -E4,0)

   The formula says that if the value in cell E4 is less or equal to zero, the negative number will be shown as positive and the positive number will be displayed as zero.

   Note. If you want all the values in a waterfall chart lie above zero, you need to enter the minus sign ( - ) before the second cell reference (E4) in the formula. And two minuses will make a plus.

2. Copy the formula down to the end of the table.
   
3. Click on cell D4 and type in =IF(E4>0, E4,0).

   It means if the value in cell E4 is greater than zero, all the positive numbers will be displayed as positive and the negative ones as zero.

4. Use the fill handle to copy this formula down the column.
   
5. Insert the last formula =B4+D4-C5 in cell B5 and copy it down; include the End row.
   

This formula calculates base values that will prop up the rises and falls to the appropriate height.

Step 3. Create a standard Stacked Column chart

Now your data are well-organized and you are ready to build the chart itself.

1. Select your data including the column and row headers, exclude the Sales Flow column.
2. Go to the Charts group on the INSERT tab.
3. Click on the Insert Column Chart icon and choose Stacked Column from the drop-down list.
   

   The graph appears in the worksheet, but it hardly looks like a waterfall chart. Take the next step and turn the stacked column graph into Excel bridge chart.
   

Step 4. Transform the column graph into a waterfall chart

The time has come to know the secret. You just need to make the Base series invisible to get a waterfall chart from a stacked column.

1. Click on the Base series to select them, right-click and choose the Format Data Series… option from the context menu.
   

   The Format Data Series pane immediately appears to the right of your worksheet in Excel 2013 / 2016.

2. Click on the Fill & Line icon.
3. Select No fill in the Fill section and No line in the Border section.
   

   When the blue columns become invisible, just delete Base from the chart legend to completely hide all the traces of the Base series.
   

Step 5. Format Excel bridge chart

Let's finish up with a little formatting. First I will make the flying bricks brighter and highlight the Start and End values in the chart:

1. Select the Fall series in the chart and go to the FORMAT tab under CHART TOOLS.
2. Click on Shape Fill in the Shape Styles group.
   
3. Pick the color you want in the drop-down menu.

   Here you can also experiment with the column outline or add special effects to them. Just use the Shape Outline and Shape Effects options on the FORMAT tab to make changes.

   Then you should do the same trick with the Rise series. As for the Start and End columns, you need to color-code them individually, but they should be of the same color.

   When you are done, the chart should look like the one below:
   

   Note. Alternatively, you can change the color and outline of the columns in the chart by opening the Format Data Series pane or choosing the Fill or Outline options in the right-click menu.

   Then you can remove excess white spaces between the columns to make them stand closer to one another:

4. Double-click on one of the chart columns to bring up the Format Data Series pane.
5. Change the Gap Width to something smaller, like 15%. Close the pane.
   

   Now the holes in the bridge chart are patched.
   

   When you look at the waterfall chart above, some of the flying bricks seem to be of the same size. However, when you refer to the data table, you'll see that the represented values are different. For more accurate analysis I'd recommend to add data labels to the columns.

6. Select the series that you want to label.
7. Right-click and choose the Add Data Labels option from the context menu.
   

   Repeat the process for the other series. You can also adjust the label position, the text font and color to make the numbers more readable.

   Note. If there is an apparent difference in column size and the specifics aren't important, you can omit the data labels, but then you should add a Y-axis for better data interpretation.

When you are done with labeling the columns, just get rid of unnecessary elements such as zero values and the legend. You can also change the default chart title to something more descriptive. Please take a look at one of my previous blog posts how to add titles to Excel charts.

My waterfall chart is ready! It looks completely different from the commonly used types of charts and it is very readable, isn't it?