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 overlarge numbers. 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.

image0.jpg

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

#,##0,

After confirming your changes, your numbers will automatically appear in the thousands place!

The beautiful thing here is that this technique doesn’t change the integrity or truncate your numeric values in any way. Excel is simply applying a cosmetic effect to the number. To see what this means, take a look at this figure.

image1.jpg

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 cosmetically formatted 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 number dramatically.

When you perform a mathematical operation into a cell, you are literally changing the value represented in that cell. This forces you to carefully keep track of and maintain the formulas you introduced to simply achieve a cosmetic effect. Using custom number formatting avoids that by changing only how the number looks, keeping the actual number intact.

If needed, 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)

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.65 m
blog comments powered by Disqus
Advertisement

Inside Dummies.com