Waterfall Chart in Excel

What is Waterfall Chart?

A waterfall chart is a special type of column chart used for understanding how an initial value is affected by a series of intermediate positive or negative values. Usually the initial and the final values are represented by whole columns, while the intermediate values are denoted by floating columns.

Data used for creation of Waterfall Chart in Excel:

Weekly cash flow data is used for creation of waterfall chart in excel. The data is shown below.

WeekWeekly Cash flow
Week0(Start)2000
Week1-100
Week21200
Week3-200
Week4-500
Week51200
Week6250
Week7350
Week8380

Lets discuss how to create a waterfall chart in Excel through following steps

Step 1: Prepare the Data

To create your own waterfall chart in excel, the first step is to set up your data.

  • Insert Five columns between Week column and weekly cash flow column
  • Name the Column as Base, End, Down, Up and Start as shown belowwaterfall chart in excel 1
  • Insert the formula in the K2 cell as =L2 (start value)
  • J3: =MAX(L3,0)   (Positive Movement in Cash flow)
  • I3: =MIN(L3,0)*-1 (Negative Movement in Cash flow)
  • G3: =SUM(G2,J2:K2)-I3 ( we will be hiding the completed Chart)

So the sheet will be like

waterfall chart in excel 2

  • Copy the formulas in G3, I3 and J3 down to row 10
  • H11: =SUM(G10,I10:J10)

So Finally the Sheet will be like this

waterfall chart in excel 3

Step 2: Create the Waterfall Chart

To create the waterfall chart:

  • Select cells F1:K11 –which includes heading cells and data — but don’t include the last column i.e. Weekly Cash flow.
  • Click Recommended Charts -> All Charts -> Stacked Column select the second chart  as shown below

waterfall chart in excel 4

  • The chart will be like

waterfall chart in excel 5

  • Right Click on the Baseseries -> Format Data Series. Under FILL, click no fill and under BORDER, click No Line, so it isn’t visible in the chart.

waterfall chart in excel 6

So the resultant chart will be

waterfall chart in excel 7

  • Select one of the Down series columns, and format the series with red fill Color
  • Select one of the Up series columns, and format the series with green Fill Color the resultant chart will be

waterfall chart in excel 8

  • Right Click on any of the series ->  Format Data Series, and reduce the Gap Width to a small amount, about 30%

waterfall chart in excel 9

  • Remove the Grid lines and Legend, So the final chart will be like

waterfall chart in excel 10

Yay!! waterfall chart in Excel has been created successfully

previous small waterfall chart in excel                                                                                                                next small waterfall chart in excel