Custom format large numbers in Excel

The more I learn and use Excel the more I use it to work smarter.  This tip will be very useful for those of you who work with and display large numbers (thousands and millions).  Large numbers can be difficult to read, especially if there are many of them.  The example below shows numbers in millions and in thousands; you decide which is easiest to read – normal or customised?  

To improve the readability of large numbers you can “custom format” them, but be careful you might get some unexpected results! 

Large numbers displayed in normal and custom formats

Custom large numbers excel

How to custom format numbers

  1. Select the data range and press [Ctrl]+1 to display the Format Cells dialog, OR right click and select Format Cells.
  2. From the Category list, choose Custom.
  3. In the Type control, enter the format string, examples displayed under Format Type section above.
  4. Click OK.

Additional notes

The “m” or “k” component displays the literal m or k character, to denote millions or thousands. 

Use one comma (,) to display thousands and use two commas (,,) to display millions or thousands.

To indent numbers from right, enter _) after the format string, see example above.

 

 

Comments are closed.