If you have a nervous friend, or a work colleague who is just starting to learn Excel, this article is for you! You can use Excel’s number formatting rules to control exactly how numbers appear: including strangely!
How Excel Number Formatting Works
To apply a custom number format to an Excel cell, simply right-click on it and choose FORMAT CELLS… (you can also press CTRL + 1 to do the same thing), then go to the Number tab on the dialog box which appears. Finally, scroll down to the bottom of the list of categories on the left-hand side and choose Custom, then click in the Code box.
You can type any valid number format in this box, provided that it obeys certain rules. The basic syntax is:
– anything before the first semi-colon tells Excel how to display positive numbers
– anything before the second semi-colon shows how to format negative numbers
– anything before the third semi-colon shows how to format zeros; and
– anything after the third semi-colon controls how text will appear.
For those who don’t know what a semi-colon looks like, here’s one;.
Typical Normal Number Formats
Thus a typical number format might be:
This would mean that positive numbers would be displayed with a leading pound sign, negative numbers in red, in brackets; zeros as the digit 0; and text without any additional formatting (that’s what the @ sign means).
Reversing the Sign of Numbers
One trick is simply to display positive numbers as negative and negative as positive – for example:
This would make all arithmetic appear strange.
If you omit part of a number format, it will have the effect of hiding a number. So use:
to hide the positive, negative, zero and text part of any input: ie to hide everything!
How about the following format to scare nervous users?
“Sorry, no positive numbers allowed in this cell”;”Sorry, no negative numbers allowed in this cell”;”Sorry, no zeros”;”No text”
Then it wouldn’t matter what you typed in, you’d get a suitably scary error message. Enough to put anyone off Excel for life!
Or for even scarier message, how about:
“I was hoping you’d type “0” into the cell”
Now that you can see what’s possible, have fun making up your own ideas (and feel free to add them as comments to this article!).