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.
|Week||Weekly Cash flow|
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