Last month your revenue was $40,000 and this month it’s only $30,000? What happened? In Excel, you could print a nice report that shows each revenue stream for last month and this month, so you can compare the amounts.
You could even create a bar chart to compare the different revenue streams.
The bar chart lets you see the differences for each stream, but maybe you’d like to see how each revenue stream contributed to the overall change in revenue. A waterfall chart will let you see the changes that occur between a starting point and an ending point. In Excel, you can create a waterfall chart by building a column chart, and making some changes to it.
Create a New Table
The first step in building a waterfall chart is to create a table that calculates the individual changes, and a running total. In the example below:
- June and July revenues are at the far right.
- F3 is the total for June.
- C9 is the total for July.
- Column D shows the difference, where amounts have gone down.
- Column E shows the difference, where amounts have gone up.
- Column B is the running total, from the June start, to the July end.
Add a Column Chart
To start the waterfall chart, select the range in the thick border (A1:F10), and insert a clustered column chart.
Hide the Running Total
To focus on the revenue stream changes, you can hide the series for the running total:
- Click on a dark blue column, to select the Run Ttl series
- On the Ribbon’s Format tab, for Shape Fill, select No Fill
To remove the Run Ttl series from the legend:
- Click on the legend to select it
- Click on Run Ttl, then press the Delete key
Format the Waterfall Chart
Next, you can add a bit of formatting to make the column chart look more like a traditional waterfall chart. To widen the columns:
- Right-click on any column, and click Format Data Series.
- In the Series Options category, set the Gap Width to 0%.
- Close the dialog box.
To lighten the gridlines:
- Right-click on a gridline, and click Format Gridlines.
- In the Line Color category, select Solid Line.
- From the Color drop down list, select a light shade, such as the lightest grey.
- Close the dialog box.
In this revenue chart, up is good, and down is bad, so you can change those series colors to red and green. If the End series is red, change it to a different color, to avoid any confusion. To change a series colors:
- Click on a column, to select its series
- On the Ribbon’s Format tab, for Shape Fill, select the color you want
And here’s the finished waterfall chart. I also added data labels with a custom number format, to show up and down arrows.
Waterfall Chart Utility
The example shown here is a very simple waterfall chart, with only a few changes. If you need to make lots of waterfall charts, or more complex charts, you should invest in Jon Peltier’s Excel Chart Utility. To use the utility for the revenue stream data, you could set up a simple table that showed the start and end amounts, and the change in each revenue stream. These are just links to the original table, that had monthly revenue and the change amounts.
When you install the Chart utility, it adds a Waterfall command to the Ribbon. Select a cell in the table, then click the Waterfall Chart command, to open the dialog box.
You can change the sort order, and the option to use values as data labels, or go with the default settings. Then click OK, and the chart is created and formatted instantly. Much quicker and easier than doing everything manually!
So, to save yourself some time and headaches, take a look at Jon Peltier’s Excel Chart Utility. Use it to create waterfall charts, and other custom charts. It’s very reasonably priced, and will quickly pay for itself, in time saved.