Below are the steps to create a waterfall chart:
Applies To: Microsoft® Excel® 2010 and 2013
- We start by setting up the data. Select the Income and Expense Analysis worksheet.
Note:
The value in E5 (Running Total) is Revenue (G4) – Cost of Sales (F5).
The Running Total in E7 equals Gross Profit (G6)-Fixed and Semi-fixed Expense (F7).
The value in E8 is the Net Profit and equals Running Total (E7) – variable Expenses (F8).
- While holding down the Ctrl key, select cells B3:B9 and cells E3:G9.
- Select the Insert tab.
- Select Column under the Charts group.
- Select the Stacked Column Chart.
- Right click on the Running Totals series on the chart.
7. Select the Format Data Series menu item.
8. Select the Fill
9. Select the No fill radio button.
- Select the Border Colour
- Select the No line radio button.
- With the Format Data Series window still open, select the series on your chart that reflect Income.
- Select the Fill
- Select the Solid fill radio button.
- Select the colour that suits the theme of your dashboard.
- With the Format Data Series window still open, select the series on your chart that reflect Expenses.
- Select the Fill
- Select the Solid fill radio button.
- Select the Red
- Click the Close
- Right click on one of the Horizontal Major Gridlines and select
- Right click the Vertical Value Axis and select Format Axis.
23. From the Axis Options menu, select the Display Units drop down.
24. Select Millions
- Select Close.
- Select the Waterfall Chart.
- Select the Layout tab then Chart title.
- Select above Chart.
- Enter Income and Expense Analysis.
The Revenue, Gross Profit and Net Profit are whole columns and show positive values. The Cost of Sales, Fixed and Semi Fixed expenses and Variable expenses show negative values and are depicted as floating columns.
