Over the years, Excel's charting capabilities have changed enormously, with new charts being introduced all the time. In this article, we're going to take a look at a chart type that became available in Excel in 2018...
What is a waterfall chart?
A waterfall chart is a type of graphic which shows the effect of a series of changes, positive and negative, on an initial value. These are most commonly changes over time, but the chart can equally be used as an alternative to a stacked column chart, to show the impacts of a series of transactions (for example by cost centre) on an initial budget.
How do you structure the data for a waterfall chart?
As always with Excel's charts, the key is to make sure that your data is structured appropriately. In this case, you need an initial value, followed a series of transactions. You may also want to have one or more totals or subtotals along the way. The transaction values should be positive if they are to "step up" the waterfall chart, and negative if they are to "step down" the chart.
Each of these values should be labelled appropriately to allow the value to be identified.
As an example, let's say that our department has an initial budget, which we gradually use up each month as we incur expenses. Then, mid-year, we have a top-up to the budget and later in the year we have a sale of old equipment which gives us an additional source of income.
Our data might then look something like this:
Creating the waterfall chart
Once you've got the data in place, ensure it's selected, and the next step is to create the chart.
To do this, go to the Insert tab on the ribbon in Excel, and... well, waterfall charts may not be as obvious as you'd like.
Waterfall charts are not typically displayed in Excel's list of "common charts" when you look at the ribbon, so you'll need to click the tiny arrow in the corner of the Charts group, which then shows "All charts"
Then, on the Insert Chart dialog box, you'll need to navigate away from "Recommended charts" to "All Charts"
At this point, you will get a waterfall chart - and it probably won't look bad with its default settings:
But, with a bit of work, we can make it look rather better...
Changing the colours of a waterfall chart
Changing chart colours is normally pretty straightforward in Excel. However, the likelihood with a waterfall chart is that you may want to change some colours at a time, rather than all of them. For example, I'd like all the income to be displayed in green, and the expenses to be displayed in red.
The normal approach for changing colours in an Excel chart is to simply click on any one of the bars, then select the colour you want from the toolbar that appears. But with a Waterfall chart, we need to be a little more specific.
Start by clicking on the Legend, which displays by default above the chart. This will select the whole legend (you'll see a border appear around the whole thing), but this isn't what we want. The next step is to select just the Increase element, so click on that, and a border will appear around just this item in the legend. Then right-click on this element, and the fill button will appear:
I've chosen to make my expenses (decreases, as Excel terms them) display in red, and my Increases in green. I've also made my chart just a little wider, in order to make the labels easier to read:
Adding total bars to a Waterfall chart
The final step I've taken is to display a total on the end.
If you look at the data, this is just another value - it's the last one in the table.
But Excel doesn't know that this is a total - it just looks like another increase. To tell Excel to display this as a total, you need to click on that bar in the waterfall chart. Then, right-click on the bar, and select "Set as Total".
If you're not seeing this option, the reason is almost certainly because you've got a whole series of bars selected, rather than just the one, so once again left click on the bar which contains a total - it should go a different colour to all the others - then right-click on it again.
Setting a bar as a total means that it will display not as a change from the previous bar, as is the case with an increase or a decrease, but rather as a value anchored to the bottom (x) axis. You can do this with any values you want to display as subtotals, but in this case, I'm just doing it with the last item. Here's how that bar looks once you've set it up:
And that's it - a Waterfall chart in less than 2 minutes!
Incidentally, if you're interested in more of what Excel can do, why not take a look at our range of online courses - they cover everything to do with Excel and data, including how to migrate your data from Excel to a database, to gain the full power of a relational database such as Access or SQL.