Formatting

Mike Alexander of DataPig Technologies is a Microsoft Excel MVP who has written about using Excel for data analysis. Mike and I collaborated last month on the Excel Dashboard and Visualization Boot Camp, which went so well that we plan to offer it a few times a year.

Mike has written Excel 2007 Dashboards and Reports For Dummies, which is among the most comprehensive Dummies books I’ve come across. The book is geared towards Excel 2007, but its principles and techniques are valid for any version of Excel. Mike takes his readers through dashboard principles, data preparation and chart creation, advanced data techniques including pivot tables and dynamic ranges, automation of dashboards using VBA, and designing interactive dashboards.

Like all Dummies books, Excel 2007 Dashboards and Reports For Dummies has a “Part of Tens” chapter, including Ten Chart Design Principles, which Mike will write about in this post. While Mike has written these principles, they are in agreement with principles that other graphing and visualization experts would have presented. Without further ado, Here’s Mike:

Ten Chart Design Principles by Mike Alexander

Excel makes charting so simple, it’s often tempting to accept the charts it creates no matter how bad the default colors or settings are. But I’m here to implore you to turn away from the glitzy lure of the default settings. You can easily avoid charting fiascos by following a few basic design principles:

1. Avoid Fancy Formatting
2. Skip the Unnecessary Chart Junk
3. Format Large numbers where possible
4. Use Data Tables instead of Data Labels
5. Make Effective Use of Chart Titles
6. Sort your data before Charting
7. Limit the use of Pie charts
8. Don’t be afraid to parse data into separate charts
9. Maintain Appropriate Aspect Ratios
10. Don’t be afraid to not use a chart!

1. Avoid Fancy Formatting

• Don’t apply background colors to the Chart or Plot Area.  Colors in general should be reserved for key data points in your chart.
• Don’t use 3D charts or 3D effects.  No one is going to give you an Oscar for special effects./p>
• Avoid applying fancy effects such as gradients, pattern fills, shadows, glow, soft edges, and other formatting.  Focus on the data and not shiny happy graphics.
• Don’t try to enhance your charts with clip art or pictures.  Not only do they do nothing to further data presentation, they often just look tacky.

2. Skip the Unnecessary Chart Junk

• Remove Gridlines
• Remove Borders
• Skip the Trend Lines
• Avoid Data Label Overload
• Don’t Show a Legend if you don’t have to
• Remove Axes that Don’t Provide Value

3. Format Large numbers where possible

When plotting very large numbers on a chart, you should consider formatting the values so that they are truncated for easy reading.

For instance, in this chart, I’ve formatted the values to be displayed as 10M and 17M instead of the hard-to-read 10,475,000 and 16,906,714.

4. Use Data Tables instead of Data Labels

A data table allows you to see the data values for each plotted data point, without overcrowding the chart itself. Although data tables increase the space your charts take up on your dashboard, they respond well to formatting and can be made to meld nicely into your charts. Data tables come in particularly handy if your clients are constantly asking to see the detailed information behind your charts.

5. Make Effective Use of Chart Titles

You can use chart titles to add an extra layer of information, presenting analysis derived from the data presented in the chart.

6. Sort Your Data Before Charting

Unless there is an obvious natural order such as age or time, it’s generally good practice to sort your data when charting. By sorting, I mean sort the source data that feeds your chart in Ascending or Descending order by data value.

7. Limit the use of Pie charts

• Pie Charts typically take up more space than their cousins the line and bar charts.
• Pie charts can’t clearly represent more than two or three data categories.
• Bar Charts are an ideal alternative to Pie Charts.

8. Don’t be afraid to parse data into separate charts

A single chart can lose its effectiveness if you try to plot too much data into it. Step back and try to boil down what exactly the chart needs to do. What is the ultimate purpose of the chart?

9. Maintain Appropriate Aspect Ratios

A skewed aspect ratio can distort your charts, exaggerating the trend in charts that are too tall, and flattening the trend in charts that are too wide.

Generally speaking, the most appropriate aspect ratio for a chart is one where the width of the chart is about twice as long as the height is tall.

10. Don’t be afraid to not use a chart!

You typically use a chart when there is some benefit to visually seeing, trends, relationships, or comparisons.

Ask yourself if there is a benefit to seeing your data in chart form. If the data is relayed better in a table, then that’s how it should be presented.

Jon’s comments on Mike’s top ten.

You didn’t think I could let this go, did you? As I noted in the introduction, I concur with all of Mike’s points, but there are a couple of things that would make Mike’s example charts even better.

2 and 3. The suggestions are spot-on, but the charts can be further clarified. Reduce confusion in the chart by maintaining a proportional axis scale (XY not Line chart), so the gaps between 2000 and 2003 and between 2003 and 2004 don’t look like the five year gaps earlier in the chart. This makes the upswing in the last few years even more striking.

6. This is a good practice. However, you should sort by category if it makes sense. The example in item 7 should be sorted in order of increasing age. In this kind of plot, it also is important that the bins (i.e., age ranges of each category) be of equal width. If this is age of drivers, then the first age range should be something like 16 to 24, which becomes by far the largest value in the chart.

7. Jorge Camoes showed the best collection of pie charts in The Best Pie Charts Come From Germany.

8. This is a nice example of the use of what Edward Tufte calls small multiples. Small multiples is part of the basis of panel charts.

9. Adjust the aspect ratio of the chart and its X and Y axis scales so the “average” slope is around 45°. This is described by Kelly O’Day in Banking to 45° to Enhance Visualization.

10. This was discussed by Tony Rose last week in Best Method for Illustrating a [Single] Data Point.

Among Mike’s other books are Pivot Table Data Crunching for Microsoft Office Excel 2007 (with Bill Jelen), Microsoft Excel and Access Integration: With Microsoft Office 2007, Microsoft Access 2007 Data Analysis, and Excel 2007 VBA Programmer’s Reference (with John Green, Stephen Bullen, and Rob Bovey).