Excel Table Design Best Practices

July 24th, 2009 by datapig Leave a reply »

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.

Advertisement

23 comments

  1. T says:

    Another example of ‘less is more’.
    One remark, though: I prefer to have numbers right aligned. This gives me a better ‘feel’ for the magnitude of the numbers. If we look at the margin column, I find it easier to do a rough calculation by adding up anything left from the ‘thousand separator’ if the numbers are right aligned.
    T

  2. Rob says:

    I agree with T…all numbers must be right aligned to easily see the magnitude differences.

    I also use Single Accounting underline, but I use a button bar shortcut to apply it. Click on the “accounting” formatting button (picture of a comma), then the underline button. You must do it in that order or it won’t work.

  3. DataPig says:

    Do the pictures in this post look grainy to anyone else? I can’t decide if it’s my cheap East German equipment causing the issue or wordpress.

  4. Jayson says:

    The pictures looked fine in Google Reader, but seem blurry here.

    Another vote for right aligned numbers.

  5. govi says:

    Superb post!
    Always struggeling with my tables

  6. Jayson says:

    I took another look at the tables and I have a question. How do people feel about rounding to the thousands for some number columns but not for others?

  7. Raph says:

    @ Jayson:

    I think rounding some columns is fine as long as it’s identified (as it is in the example).

    Right-aligned numbers a must.

  8. jamescox says:

    Not sure I follow the logic for not having a ‘$’ with each monitary value but having a ‘%’ with every percentage.

    Would there be any merit to having a supertitle for the last two coumns of ‘Per passenger’ then column titles of ‘Revenue, $’ and ‘Margin, $’ ?

    Would changing the supertitle ‘Revenue (000s)’ to just ‘Revenue’ and the current column title from ‘$’ to ‘$ (000s)’ remove the possible confusion of reading the second column’s implied heading of ‘thousand percent’ ? OK, I know that isn’t remotely likely in this case – but there might be some confusion in other tables of non-financial data. Maybe there is a general rule of keeping the units on the bottom-most line of the column title?

  9. DataPig says:

    jamescox:

    Percentages are difficult to read if formated to .08, .43, .97, etc. I suppose you could convert them to integers and just show them as 8, 43, 97. But this, I think, would only serve to confuse.

    I can’t imagine anyone thinking that the figures shown would be in ‘thousand percent’. But there is that possibility, I suppose I would qualify it with some extra text.

  10. In accounting, we prefix the first number with a dollar sign, any subtotals get one, and the bottom total gets one. Same for percentages, just not prefixed. So in your revenue column, it would be $12,180 and $44,534, and none of the others would have them. Similarly it would be 27% and nothing else. And the $ and % would be removed from the column header. I know everyone wants to be like an accountant, so here’s your chance.

    +1 for right aligned numbers.
    +1 for great post – my tables are horribly ugly so I need all the help I can get.

    And can you make this comment box bigger, I feel like I’m writing on the head of a pin.

  11. DataPig says:

    DK: Interesting. I didn’t know about that standard Accounting practice.

    I would love to be an account – stamp collection and everything.

    But I would think outside the accounting world, having a $ on only two of the numbers in my revenue column would look like a goof.

    I’ll have to diddle with the CSS files to make this comment box bigger.

  12. numbers should be right-aligned !

  13. Martin says:

    How do you get the unbroken line between $ and % under the Revenue heading? It’s driving me nuts.

  14. DataPig says:

    Martin: Alt+Enter to get them under the label. Just added a few Space characters between $ and %.

  15. Michael Pierce says:

    Thanks for the good article. Another great example of increasing data per pixel. Are we getting rid of Table Junk now? :-)

    I definitely agree with right-aligning numbers. The accounting format lets you simplify the headings further; I just spread “Revenue (000)” and “Margin (000)” across the columns and let the symbol in the first row inform as to the content. I also put “per passenger” as a super heading to the right-most columns to reduce the amount of text.

    As for the conditionally formatted numbers, I prefer to only format the true exceptions. In this case, I just highlighted the “bad” numbers in red and left the others standard black. All of that blue text causes too much distraction, in my opinion.

  16. Martin says:

    Thanks Mike – you are a star.

  17. Martin says:

    Just out of curiosity, what font is being used in the final table?

  18. datapig says:

    Martin: The final font is Calibri.

  19. Meghan says:

    I am not sure where you are getting your information, but great
    topic. I needs to spend some time learning much more or understanding more.
    Thanks for magnificent info I was looking for this information for my mission.

  20. Deepak says:

    good one Datapig, thank you.

  21. Lisha says:

    Personally, I find it easier to read the before table than the after table. In the before table, the right elements grab my attention (the bold labels “Revenue”, “Margin”, and “Per Passenger”), and the colors help focus my attention as well. I do think sorting the routes is a good idea.

Leave a Reply