Sssshhhhh…this is a charting secret

Awesome, let’s talk more about charts!

Now that we’ve discussed over-labeling, and you know I HIGHLY discourage it…

You’re ready for a cool…”shhhh”…charting secret.

And what this will allow you to do, is to replace the FAKE totals people often put on the top of their stacked column charts….

With REAL, automatic updating totals that will always be in the precise, perfect position on top of your stacked column chart…

With zero guesswork!

So right off the bat, I want to be clear that I’m definitely NOT TALKING ABOUT:

  • Manually adding textboxes or rectangles to your charts and typing in the totals (a horrible idea)
  • Manually moving data labels around in your charts and then typing in the total (another horrible idea)

Side Note: Anything you do manually to your charts like this, is a huge disaster on the Back End Of Your Deck…something I talk a lot about in PowerPoint 3X.

Anyway…

This charting trick destroys these bad charting habits and it also happens to be the first step to building waterfall charts and valuation ranges.

They are essentially the same thing, except one is a column chart and one is a bar chart.

So what’s the secret? Simply this:

  1. Add a total column to the Excel sheet and graph it as well (so sum of your other values).
  2. Add the data label to the total column you just added and put the label position at the bottom
  3. Hide the total column by filling it with nothing and removing its outline
  4. Adjust your vertical axis so that the chart fits in the graph, with your hidden total column running off of the chart.

Here’s why it works:

Step #1: By graphing the extra column, you immediately get access to its data label, which in a stacked column chart, happens to be the total that SHOULD be sitting on top of your chart.

That’s your real TRUE total value that will now update automatically in your spreadsheet, as long as you have it set up as a “sum()” function.

Step #2: Once you add the data label and choose “bottom alignment” so that it’s sitting on top of your column chart, you can simply take away the total column’s fill color and outline.

This leaves you with the data label without the graphed total column.

Step #3: And with your vertical axis adjusted to make the hidden total column run off of the space, no one would ever know there was an extra column there in the first place!

And that is the one downside of this trick. You do have to manually adjust the vertical axis as your data chances…but better having to adjust the vertical axis ONCE…

Than having to manually add or adjust the total values, which you’ll never be able to do as accurately as in this “shhh-charting” secret.

Good luck with your charts and see you at Happy Hour!