# 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 below
• 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

• 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

#### 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

• The chart will be like

• 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.

So the resultant chart will be

• 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

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

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

Yay!! waterfall chart in Excel has been created successfully