10. Waterfall Charts
Waterfalls are a great alternative to barcharts but can be difficult to get right in PowerPoint. Find out how to build them in this lesson.
- Perfect for charting the change in a company's resource over time
- These resources could be cash balance, inventory or employee headcount
Creating waterfall charts - overview
1 Create a bar-chart with the total figures for each time period
2 Right click on the bar-chart and go to edit data
3 Create two columns on the excel sheet for up and down movements
4 Use these columns to create the base values
5 Switch the base values and up/down values into the chart area
6 Return to PowerPoint to format the chart correctly
Waterfall charts can be very effective in displaying changes in a company’s resources.
These resources could be Cash balance, Inventory level, or in the case of this example, Employee headcount.
In this lesson, we’ll convert this Bar chart into a Waterfall chart that shows the growth and decline in the number of employees from 2006 to 2013.
Unfortunately, PowerPoint 2010 doesn’t have an inbuilt Waterfall chart, so instead we create a Bar chart that consists of two items, the Invisible base underneath each Bar, and the Bar itself.
Let’s start by returning to PowerPoint and first duplicating the Bar chart slide.
So I’ll right click and select Duplicate, and then I’ll right click on the chart and Edit Data.
Most of the work in creating a Waterfall chart is actually finding the base level underneath each Bar, and we’ll accomplish that task here in the spreadsheet.
We’ll start off by creating two columns, one called Up, and the other Down.
Up will show the years in which the headcount grew, and Down will show the years in which the headcount fell.
To return only the positive values, we’ll have to create a formula that uses the MAX function.
So I’ll write “=max” and the MAX function will check if the difference between two years is greater than zero.
If it is, it’ll return the difference, and if it’s not, it’ll return zero.
So I’ll check if B3 minus B2 is greater than zero, and obviously in the first case, it is. I can now copy this cell and paste for the remaining years with Alt + E S F.
This tells me that we have two negative years in 2011 and 2012.
For the Down column I’ll use a MIN function, and this will check if the difference between B3 and B2 is less than zero.
And it’ll return zero for all the positive years, and it’ll return the Value for all the negative years.
For the Waterfall to work, the values in both columns must be positive, so I’ll need to change the formula. I’ll press F2 to jump back into the formula and put a minus before the MIN.
I’ll then copy and paste for the remaining cells.
And now with these two columns created, I can find my Base values.
So I’ll label a new column and call it Base.
Unfortunately the formula for the Base values are slightly different for positive and negative numbers.
For Positive Bars the Base value is simply the previous year’s total, but for Negative Bars, the Base is the previous year’s total minus this year’s reduction.
So I’ll need to create an IF function to do this.
And my logical test for the IF function will be if the Up value is greater than zero.
And if it is, I’ll just want the headcount from the previous year, and if it isn’t, I’d like B2 minus the Down value.
Close the bracket and press Enter.
I can now copy this for the remaining cells.
I’ll also create an extra row at the bottom which adds up the final total, which is 72 plus 8.
We’re now almost ready to transfer these values into our chart area.
However, we must first change these cells from formulas into values.
If we don’t, the numbers will have errors when we paste over the inputs to our formulas.
So I’ll copy the cells we’ve created, Ctrl + C and I’ll re-paste over them as values with Alt + E S V.
And if we enter these cells now, you can see that they’re just numbers and no formulas appear in the Formula bar.
We can now start by pasting in the Base values.
So I’ll select this column and I’ll paste with Ctrl + V, and in the next column we’ll simply add the Up and the Down cells.
And PowerPoint automatically fills this in for the remaining years.
Before we return to the PowerPoint slide, our last task is simply to write the word Final in the bottom left hand corner.
Let’s now return to our PowerPoint slide and format the Bars.
As you can see, we’ve quite a bit of formatting to do.
Let’s start by making all of the Base Bars invisible, by giving them no fill.
And the two exceptions will be the Starting value which I’ll fill with blue, and the Ending value.
Let’s also give these Data labels, so I’ll right click and add Data Label.
And I’ll do the same for the small Bar.
These Data labels will probably look best in white given the dark background, so I’ll select it and give it a white colour.
Most of the Bars will be positive and I’m going to colour these green.
So let’s select all of the Bars and select a green colour.
We can also give these Data labels by right clicking and add Data Labels.
Let’s then isolate the two negatives by double clicking and colouring it red.
For the remaining negative I may need to zoom in to make sure I select it correctly, and when I do, I’ll colour this red as well.
We might also want to remove the zero’s at the top of the two totals by just pressing Delete once it’s selected.
And we can also remove the Scale on the left hand side and extend our chart to the left.
As you can see from this example, Waterfall charts offer a nice alternative to the traditional Bar chart, but they do require quite a bit of Excel work to find the Base values.
To help you remember how to do this, I’ve included the individual steps in the show notes for you to review.
Also, if you’ve found some of this Excel work tricky, be sure to check out my Excel Foundations courses which will quickly help you up to speed.