Format Numbers in Thousands and Millions in Excel Reports

In Microsoft Excel, you can improve the readability of your dashboards and reports by formatting your revenue numbers to appear in thousands. This allows you to present cleaner numbers and avoid inundating your audience with gigantic numbers.

Here's how it works

To show your numbers in thousands, highlight them, right-click, and select Format Cells.

After the Format Cells dialog box opens, click the Custom option to get to the screen shown in this figure.

In the Type input box, add a comma after the format syntax.

#,##0,

After you confirm your changes, your numbers will automatically appear in the thousands!

The beauty part

The beautiful thing is that this technique doesn’t change your numeric values, or truncate them, in any way. Excel is simply making them look cleaner. Take a look at this figure:

The selected cell has been formatted to show in thousands; you see 118. But if you look in the formula bar above it, you’ll see the real unformatted number (117943.605787004). The 118 you are seeing in the cell is a simpler version of the real number shown in the formula bar.

Custom number formatting has obvious advantages over using other techniques to format numbers to thousands. For instance, many beginning analysts would convert numbers to thousands by dividing them by 1,000 in a formula. But that changes the integrity of the numbers dramatically.

When you perform a mathematical operation in a cell, you are changing the value represented in that cell. This forces you to carefully keep track of and maintain the formulas you introduced to make the numbers easier to read. Using custom number formatting avoids that by changing only how the number looks, keeping the actual number intact.

Making numbers even clearer

If you like, you can even indicate that the number is in thousands by adding to the number syntax.

#,##0,"k"

This would show your numbers like this:

118k
318k

You can use this technique on both positive and negative numbers.

#,##0,"k"; (#,##0,"k")

After applying this syntax, your negative numbers also appear in thousands.

118k 
(318k)

What about millions?

Need to show numbers in millions? Easy. Simply add two commas to the number format syntax in the Type input box.

#,##0.00,,"m"

Note the use of the extra decimal places (.00). When converting numbers to millions, it’s often useful to show additional precision points, as in

24.65m

More digestible numbers are easy in Excel, once you know these little tricks.