Improve Your Excel Reports with Effective Number Formatting - dummies

Improve Your Excel Reports with Effective Number Formatting

By Michael Alexander

How a table is designed has a direct effect on how well an audience absorbs and interprets the data in that table. In Excel reporting, every piece of information in your table should have a reason for being there. In an effort to clarify, tables often inundate the audience with superfluous ink that doesn’t add value to the information.

For example, you’ll often see tables that show a number like $145.57 when a simple 145 would be just fine.

Why include the extra decimal places, which serve only to add to the mass of numbers that your audience has to plow through?

Here are some guidelines to keep in mind when applying formats to the numbers in your table:

  • Use decimal places only if that level of precision is required.

  • In percentages, use only the minimum number of decimals required to represent the data effectively.

  • Instead of using currency symbols (like $ or £), let your labels clarify that you are referring to monetary values.

  • Format very large numbers to the thousands or millions place.

  • Right-align numbers so that they are easier to read and compare.

This figure shows a poorly designed table.


This figure shows the same table with appropriate number formatting applied. Notice that the large revenue and margin dollar amounts have been converted to the thousands place. In addition, the labels above the numbers now clearly indicate such.


The percentages have been truncated to show no decimal places. Also, the color coding draws attention to the Margin % column, the key metric in this table.

Amazingly, all of these improvements have been made simply with number formatting. That’s right; no formulas were used to convert large numbers to the thousands place, no conditional formatting was used to color code the Margin % field, and there were no other peripheral tricks of any kind.