Some of the more business savvy out there immediately recognize and understand the value of the Waterfall Chart. It’s essentially the single greatest way to simultaneously create a great looking bridge chart AND show off your crazy awesome Excel skills.
Now if you’ve already upgraded to Excel 2016, Waterfall Charts are now a built-in feature (at long last, I might add).
However, if you’re one of the less fortunate, and stuck in a prehistoric Excel hell, see below.
Let’s say you own a coffee shop cleverly named “The Coffee Shop” and you want to create a beautiful depiction of your plan to kill it in 2016 (so you can show it off to potential investors, perhaps).
You sell Drinks, Food, and Merchandise (because people need your swag), and you already know that you expect to sell $8,000 more in drinks, $2,700 less in food, and $6,500 more in merchandise than you did in 2015.
Total 2015 sales were $20,000 and 2016 sales are expected to be $31,800 ($20,000 + ($8,000 – $2700 + $6,500)).
These are totally made up numbers, so don’t get hung up by the puny size of our pathetic fictional store.
Just to ground everyone (see what I did there?), this is what we’re going for:
First, create a table that looks like this one:
Just enter the numbers you see in the image above for now, we will worry about entering the correct formulas soon.
The highlighted cells in Columns D and E are values (they do not contain formulas), as is the value in Cell C5. You can change these to whatever you like, but I suggest you keep them the same as mine until we get through the tutorial. No need to give yourself a chance to get confused.
The values in Column C will create our Blue bars, while the values in Columns D and E will create our Green (growing) and Red (declining) bars, respectively.
As a teacher, this is where I’m going to cheat and give you all the answers to the questions. The image below shows the exact same table pictured above, but this time the live formulas are shown.
Hopefully you now see that you shouldn’t be intimidated by the formula piece of this puzzle; it’s really rather simple. The values for the Calculated Total (Column F) are created by taking the Calculated Total from the row above (or from 2015 Actual sales for Row 6) and adding the change captured in either Column D or E. The Blue bars (or “base”) are calculated by either reducing the Calculated Total (from the row above) by the amount of decline in Column E (in the same row) or keeping the same value as the Calculated Total in the row above (in the event that the category is growing or remaining flat).
The big secret here is that our chart will be a Stacked Column chart.
So think of the “base” as the bottom column for each category. The name “Blue Bars” may be misleading, so to alleviate some confusion you may have at this point, let me show you an outline of the base for each of our three categories:
Starting to get the picture?
The next step to actually creating the chart is to select Range B5:E9 and create a Stacked Column chart, as shown below:
After that, format the chart however you’d like and you’re finished! If you want to format your chart the way mine is, follow these steps:
1. Delete the Legend and the Gridlines
2. Individually select the base bars (may be blue by default) and change their fill to No Fill
3. Individually select the 2015 Actual blue bar and 2016 Plan blue bar, add Data Labels, change the Data Label color to white, and make them bold
4. Select all bars meant to be colored red, and change the fill to a Gradient Fill with settings that match these:
5. Repeat step 4 for the bars meant to be colored green
6. Add Data Labels for the Red bars and Green bars, edit the Number format for these labels, and use the following Format Code to hide all Data Labels with a value of 0: $#,#00;-$#,#00;””
7. Add a Chart Title that is Centered Overlay Title
8. Impress someone with your perfectly formatted Waterfall Chart
Well that’s it for now. Give it a shot and leave your questions and comments in the section below! Best of luck!