Excel Custom Number Formats A Second Time

Last week I published an article about Excel auto sums and custom number formats, but I feel the number formats need a more detailed explanation than I gave in that tutorial. Therefore I have now recorded another Excel demo only about custom number formats.

Sort converts number into text
A common mistake when you are not very used to Excel, is to add a sort after a number. In most cases that will make any formulas useless, as Excel will consider the number with the sort as text and not a numerical value. You can see when this happens, as the content of the cell will become left-aligned.

Use number formats instead
It is of course useful to have a sort in the numerical cell, to make sure you are not comparing apples and pears, but to be able to use a sort for your numbers, you must use a number format. Then Excel will understand that this is not text but still numbers, and you can do all calculations just as if there was no sort at all.

If you want to use a currency, Excel gives that possibility in a dropdown, but for other sorts you need to create a custom number format.

Hash and zero
When you choose to create a custom number format, Excel shows a lot of examples on different ways to write a number. You can use any of the formats with a hash mark and a zero. Select it, and an example result is shown above in the same dialog.

What I add in the demo and what is NOT given by Excel is the sort. After the number format you wish to use for the numericals, add the sort within quotation marks. If you like a space before the sort, like I do, enter that also. Then the sort addition to the number format will be like
" tons". That's it!

Now Excel will treat all cells that have this format and has a number followed by tons as a number and not as text, and you can use all the Excel calculation options on those cells and still see what sort you are counting with.



More tips on kalmstrom.com
In the Tips section of the kalmstrom.com website, you can find more info about the Custom Number Formats Tip and many other tips on SharePoint, Excel and Outlook. There are also a few articles on other subjects. You are very welcome to browse around! I hope you will find something useful.

By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions

.