Microsoft Excel has some pretty powerful charting tools, but I’ve seen many examples over the years of business graphics malpractice.  If you follow these 5 simple rules, you will be a charting nerd.  If you violate any of them, you will immediately lose respect from the powerful nerds around you.

I can pinpoint the time when I learned these rules.  It was sophomore year at Boston College when I took a class called “Computers for Business” (or something similar) – I remember this because it was 1984, the year they launched the Macintosh computer and we had a computer lab filled with about 100 brand new Macs loaded up with a diskette drive and 128MB of memory.

My favorite part of the class was the business graphics section – and it is where I learned most of these rules.  Since that time (more than 30 years – wow, I am old…) I have been able to see these rules violated time and again by a new batch of young marketing managers.

These are my favorite rules because they are constantly violated – there are a lot more, but I will save that for a future post.

Rule #1: Choose the right chart style.

The following two charts show a pretty obvious example of what happens when you use the wrong chart type.  Whenever you want to show how data is changing over time, you should use a chart with time in the x axis.  Column charts are best for measures without a lot of data points (like monthly or quarterly results) and line charts are best for data sets that include more granular information (like hourly and daily web traffic trends).

The charts below show Sales Qualified Leads (SQLs) by month and you can see that a pie chart is a ridiculous choice for this data because it obscures the trend, while the column chart clearly shows how leads and growing from month to month.

Rule #2: Provide some context with a target.

You may look at the chart above and conclude that things are going great!  Well, some additional context may tell a different story.  The chart below illustrates the point by adding a target for leads by month and you will see that data now tells a very different story.

My other favorite measure that I see all the time is “percentage of pipeline that comes from marketing activities” – because that measurement is useless by itself.  What if 100% of the pipeline came from marketing, but the pipeline is nowhere near the target?  Context is everything.

Rule #3: Choose the right scale to tell your story.

I learned a lot about this rule back in my class at BC – you can tell a very different story with the same data by manipulating the scale.  The next two charts are built with the exact same data.  If you glance at the first chart, you will conclude that homepage visits are growing briskly.  When you look at the scale or review the data in the second chart, it tells a very different story.

There are times when it really makes sense to zoom in on a scale – when changes are important, but obscured, by the large relative values.  But you need to be thoughtful when choosing your scale – and you need to be very careful because Excel often chooses this scale for you.

 

Rule #4: Use a second axis to compare related data on a different scale.

The following two charts highlight another problem with scale – they are built with the exact same data.  Where is the SQL data on the first chart?  It is so small relative to the budget numbers that it doesn’t show up on the chart.

The second chart moves the budget to a separate axis, allowing you to see the relationship between these two important data points over time. By trending the second set of data (spending), you can see that there is a direct correlation between spending and leads – which comes in handy when you are trying to justify additional spending!

 

Rule #5:  Never use 3D bar charts.  Ever. Never. Really.  Never.

This last rule is a bonus – and it is the most important one because it drives me CRAZY!  People love to add 3D effect to their bar charts for some reason.  In most cases, the 3D effect adds no additional information – and it makes it harder to read the data.  And it’s ugly!

I’m sure some smarty-pants nerd will bring me an example of a useful 3D chart.  Bring it on.

If I missed any of your favorite rules, feel free to add them to the comments below.