Some of the tables I encounter in my daily operations are....how can I put it....uglier than a bag of lips.
Seriously man, with all the buzz around visualizations today, it's almost impossible to find anyone talking about table design. Yes - One of the most underestimated endeavors in Excel is table design.
I've always felt that how a table is designed has a direct effect on how well an audience absorbs and interprets the data in that table. Unfortunately, the act of putting a table of data together for consumption is treated benignly by most. Maybe it's because it's such a common task, often done in a hurry, that we don't spend the extra time to apply some sound design principles.
In this post, I'll show you how easy it is to apply a handful of table design best practices.
For this endeavor, we'll turn this ugly table, into something useful.
Step 1: Remove Colors
In a table, color is primarily used to separate the various sections of the table. The problem is that colors often distract and could draw attention away from the important data. Colors in general should be used sparingly, reserved for providing information about key data points. In this example, I've removed all traces of background color, reserving red and blue for the Margin measures. Now at I can immediately see the problem areas without burning out my eyeballs.
Step 2: De-emphasize Borders
De-emphasize borders, backgrounds and other elements that define table areas. Try to use the natural white space between the columns to partition sections.
If borders are necessary, format them to lighter hues than your data. Light grays are typically ideal for borders. The idea is to indicate sections without distracting from the information displayed.
Step 3: Adjust Font and Alignment
As a general rule, aligning your data left or right makes your table easier to read. Not only does text look better when aligned properly, but number values are more easily discernable.
Also, reining in your font size to reasonable standards will help in drawing attention to the data within your table.
In this example, I've left the title and totals bold to demonstrate how something as simple as Bold text can affect where your attention is drawn.
As you look at the data, you can't help but to see the two bold strings of text above and below the table.
You'll note that the table headings have a line separating them from the data. This is actually a "Single Accounting" underline. I frequently use this as an alternative to borders. It helps give your headers a partitioned feel while indicating column boundaries as well. To apply the "Single Accounting" underline, go to the format cells dialog box and choose "Single Accounting" from the Underline dropdown.
Step 4: Sorting and Reducing Clutter
Every piece of information in your table should have a reason for being there. In an effort to clarify, tables often inundate you with surplurfuous ink that doesn't add value to the information.
Here are some guidelines to keep in mind when applying the final touches to your tables.
Only use decimal places if that level of precision is required. For instance, there is rarely benefit for showing the decimal places in a dollar amount such as $123.45. Likewise in percentages, use only the minimum number of decimals required to represent the data effectively. For example instead of 43.21%, you may be able to get away with 43%.
Only use the dollar symbol when you need to clarify that you are referring to monetary values. If you have a chart or table that contains all revenue values, and there is a label clearly stating this, you can save rooms and pixels by leaving out the dollar symbol.
Format very large numbers to thousands or millions place. For instance, instead of displaying 16,906,714, you can format the number to read 16,906.
Try to apply a sorting that makes sense to your table. In this case, I've sorted by Revenue $.
Here's a before and after look at our example.