How to create a Waterfall chart in Microsoft Excel
Waterfall Chart
A waterfall chart is a form of data visualization that helps in understanding the cumulative effect of sequentially introduced positive or negative values. The waterfall chart is also known as a flying bricks chart or Mario chart due to the apparent suspension of columns (bricks) in mid-air. Often in finance, it will be referred to as a bridge. Waterfall charts are used to show effects (cummulative) on positive and negative amounts.Example: Use an Excel Waterfall Chart net cash flows - helping you to visualize which periods had positive and negative results.
The waterfall chart is normally 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. The columns are color-coded for distinguishing between positive and negative values.
Set Up the Data
To create your own waterfall chart, the first step is to set up your data. In the screen shot below:
- 5 columns have been inserted between the list of months, and the column with Net Cash Flow amounts
- Base is a calculated amount for a series that will be hidden in the completed chart. It creates a starting point for the Up and Down series in the chart.
- End is the final column in the chart
- Down is a list of negative numbers in the net cash flow column
- Up is a list of positive numbers from the net cash flow column
- Start is the starting value, from the net cash flow column.
- A row was inserted above the Start row, and it will create spacing at the left of the chart. An arbitrary value (2000) was entered there, and any number within the range of net cash flow amounts could be used instead.
- An End row was inserted at the bottom of the months list
- A row was inserted below the End row, and it will create spacing at the right of the chart. An arbitrary value (2000) was entered there
Enter the Waterfall Chart Formulas
- The next step is to enter the formulas that will be used in the chart. In the screenshot above, values are typed in the yellow cells, and other coloured cells contain formulas.
B2: =" "F3: =G3B4: =SUM(B3,E3:F3)-D4D4: = - MIN(G4,0)E4: =MAX(G4,0)C16: =SUM(B15,E15:F15)-D16
Copy the formulas in B4, D4 and E4 down to row 15
Create the Waterfall Chart
To create the waterfall chart: (For Excel 2007). For Excel 2013, right click and go to format data series
- Select cells A1:F17 -- the heading cells and data -- but don't include the column with the Net Cash Flow numbers.
- On the Excel Ribbon, click the Insert tab, and click Column Chart, then click Stacked Column
- Click on the Base series to select it, and format it with no fill and no border, so it isn't visible in the chart.
- Select one of the Down series columns, and format the series with red fill colour
- Select one of the Up series columns, and format the series with green fill colour
- Format the Start and End columns with grey fill colour
- Select any column, and on the Excel Ribbon, click the Format tab
- Click Format Selection, and reduce the Gap Width to a small amount, about 10-12%
- Remove the Legend
---
Waterfall definition courtesy of Wikipedia.
