Fun with Bar Charts – Part 1 – Waterfall Charts

Only because my soon-to-be-former coworker Monica asked about this topic, I decided to dust off this blog (finding that login was no easy task!).  It’s hard to believe but the date stamp on the most recent post was… six years ago!  It’s not that I’ve forgotten about this blog, but I’m borderline at a point in life where you can’t teach an old dog new tricks.  And when I don’t learn new tricks, it’s hard to blog about them.

Well, I did learn one thing more recently than 2009: bar charts.  Remember when we all first learned about these fundamental expressions of statistics, like, in fourth grade?  Drawing the axes with a pencil and measuring out the height of each bar with a ruler was among the most painful part of any math class that I can remember… and yet, this two-post series is about those.  A steroids-injected version of those boring bar charts.

In its first part, we will examine how to create waterfall charts with Excel’s native capabilities.  A waterfall chart is one where floating bars are used to illustrate the progression of a number.  For example, I used one in my recent guest blog entry on Hungry for Points, to outline how my points & miles balance changed between two points in time:

0823bars1_4
Added some, subtracted some, and a new balance!

 

On the surface, it seems impossible to draw floating bars with Excel’s charting tool.  That may be true, but the trick is to craft an optical illusion with white bars.  The same idea can be applied to both overlapping clustered bars and stacked bars.  Let’s see how the former works:

Step 1 – Determine the step-wise cumulative value, and the increment/decrement amount:

0823bars1_0

Step 2 – Create a clustered bar chart where each step has two bars: for increments, bar 1 should be the previous cumulative amount and bar 2 should be the new cumulative amount; for decrements, it’s the opposite.  For the first and last steps representing the balances, bar 1 should be 0:

0823bars1_1
“Bar 1” is orange here.

Step 3 – Make sure bar 1 is in front and set the overlap to 100%:

0823bars1_2

Step 4 – Fill bar 1 white, or whatever your background color is:

0823bars1_5

Step 5 – Color the individual bars:

0823bars1_3

Step 6 – Add data labels to explain your chart.  These can easily be formulaic and linked to your numbers:

0823bars1_4

 

Voila! This may look exotic, but it really isn’t!

Stay tuned for the next article in this series where we take this technique to the next level!

 

Reply