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 318kYou 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.65mMore digestible numbers are easy in Excel, once you know these little tricks.