28 November 2013

Excel Custom Number Formats A Second Time

Excel icon 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
Excel left aligned cell contentA 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
Excel right aligned cell contentWhen 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

In the Tips section of the 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 Business Solutions