Business Tools Blog

How to build a waterfall chart in Microsoft Excel

We use waterfall charts to visually explain what happened or in the case of a forecast - what will happen.

Example, if recurring revenue was $1M in January and $1.3M in April, a waterfall chart explains what caused the change.  The verbal explanation would be “The revenue started at $1M; then grew by $500K from new sales; declined by $400K from disconnects; and grew by $200K from re-rates.  The end result was $1.3M revenue in April.”

How to create a waterfall? It sounds scary, right?  Wrong, the waterfall is just a stacked bar, where the bottom bar has no pattern or area.

First - set up the data.

• The columns B-F are the categories you are trying to explain
• The rows 18-21 are the stacked bars
  • Row 18 shows the starting and ending revenue
  • Row 19 shows additions to revenue
  • Row 20 shows subtractions to revenue
  • Row 21 shows the blank bar
• Row 23 is just a running total to make sure everything is adding up

Next - calculate the blank bar (this is the hardest part)

• The blank bar in column C “New Sales” is calculate B18-C20+B19  …  B18 (Starting Revenue) -C20 (Subtractions in the Column you are Calculating) + B19 (Additions from the Prior Column)

• The blank bar in Columns D & E use a similar formula.  C21-D20+C19 … C21 (Blank Bar from the Prior Column)-(Subtractions in the Column you are Calculating)+(Additions from the Prior Column)
• Copy the formula in D21 to E21 … the formula in E21 will be D21-E20+D19

• The Running Total sums row 18 to row 21

Last Step, insert a stacked bar chart:

• Highlight rows A17:F21, click “ALT+i” then “ALT+h” to insert a stacked bar, then click next twice

• On the Titles Tab, add the Chart Title and the (Y) axis Title

• On the Legend tab, uncheck the “Show Legend” selection box

• On the Data Labels tab, check Label Contains “Value” selection box, then click Finish

Your chart will look like this … there are just a few more steps to clean up the chart and make is look snazzy.

• The light blue bar is the blank bar and it needs to be on the bottom
• Double click on the light blue bar to bring up the “Format Data Series Menu”
• On the Series Order Tab, click “Move Up” until the light blue bar is the second from the top in the Series order

• Then click the Patterns Tab, and select Border None and Area None

• On the Data Labels Tab, unclick the Label Contains “Value” selection box

Your chart now looks like this:

Next, change the scale so the numbers are in $ thousands

• Double click on the (Y) axis to bring up Format Axis menu
• On the Scale tab, select “Display Units: Thousands”

• On the Number tab, select Category = Currency; Decimal places = 0; and Symbol = $
• Click OK

• Next change the color of the Plot Area
• Double click on the Plot Area to bring up the Format Plot Area menu
• Select Border = None & Area = None

Your chart now looks like this:

The next steps are optional

• You can change the width of the bars by double clicking on any data series to bring up the Format Data Series Tab
  • On the Options Tab, change the Gap Width (I used Gap Width = 30)

• Change the color of any data series by double clicking the data series to bring up the “Format Data Series” menu
  • On the Patterns tab, select the desired Area color for the bar
  • I use red for drops & green for additions

  

• I manually added arrows using the draw menu to make it easy to see which bars were additions and which were drops.

The final product looks like this:

Click here for a free template - waterfall chart in excel.