Excel Custom Number Formats with Sort
An Excel tutorial by Peter Kalmstrom
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
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:
- Select the cells where you want the custom number format to be applied
- Select Number Formats and then the Custom Category
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
- Write in or select a number format from the list
- Add the sort within quotation marks. Also add a space before the sort
if you want to have that in the cells.
- Click OK
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.