Home >Tips >Excel custom number formats kalmstrom.com site map icon Site map  

Excel Custom Number Formats with Sort

An Excel tutorial by Peter Kalmstrom

Excel iconIn the demo below Peter Kalmstrom expands the custom number format information given in the article about Excel Auto Sums.

When you add a sort to your cells with numerical values, Excel often starts treating the content of that cell as text, and you can no longer use the Excel calculation features.

The possibility to tell Excel to calculate is a crucial feature and the most important reason to use it, but in some contexts it is also very important to show the sort of a numerical value.

That is when a custom number format is useful. With a custom number format you can have both the calculations and the sort!

These are the steps to create a custom number format with a sort:
  1. Select the cells where you want the custom number format to be applied
  2. Select Number Formats and then the Custom Category
Excel custom number format
  1. Write in or select a number format from the list
  2. Add the sort within quotation marks. Also add a space before the sort if you want to have that in the cells.
  3. Click OK
To define a custom number format, Peter opens the dialog box launcher of the Number ribbon group and enters a code with hash marks (=display if a value), zero (= numbers should always be displayed) and the value "miles" he wants to add.

Peter uses Excel 2013 for his demo, but the Excel custom number formats are the same for earlier versions of Excel.  Also refer to the kalmstrom.com Blog post about Custom Number Formats in Excel.