Custom cell formats are a very important steppingstone to eternal happiness in Excel. And among all Custom Formats, one takes precedence for both, its usability and versatility. We will get to understand it through two examples.
The following Custom format allows you to set four different formats at once. And the best part is, unknowingly, you’ve been using it since you’ve started using Excel. But now, you will understand it and be able to take it to the next level. Let’s start with a simple example. It’s a range of cells filled by positive and negative values, zeroes and text. It’s great if the positive and negative go above 1000 or bellow -1000, since this will better show the effects of our format. To create this I used =RANDBETWEEN(-10000,10000)*RAND() and if you preselected the range, give it a little Ctrl+Enter 🙂 Now the RANDBETWEEN function will only give you random numbers between -10000 and 10000. If you wish to have zeroes and text, you have to add them manually. Simply by selecting a few cells, writing zero and then Ctrl + Enter.
This is a very simple example of a range in excel that has positive values, negative values, zeroes and text. Now let’s try the following…
Select the range of cells and press Ctrl+1 to get the Format Cells dialog box (one of the best shortcuts in Excel, but the first time you use it can sometimes take a while). You can also right click and choose Format Cells…, or you can use any other method you would normally use.
Go to Custom and add type in 0.0;- #,##0.00;0;@
Press Ok and Voila…
Here’s what we see:
From this example we learned that it is possible to differentiate in Excel, how the positive, negative, zeroes and text will be formatted! The custom format looks like this.
Positive;negative;zeroes;text or sometimes it will be referred to as +;-;0;@
Here is a far more advanced example.
One more time we select the range and go to Format Cells… (Hopefully by pressing Ctrl+1) 🙂
This time the format will be
[Green] #,##0.00;[Red] #,##0.00 (-);;@
Here’s what we get
Now once you know this, you can do miracles and better yet, now you can understand the formats of the cells far better.
For example if you choose the simple Accounting format in Excel (which I guess you do every day), and then go to the Format Cells… and custom, you can see that what you really got is something like
_-* #,##0.00 $_-;-* #,##0.00 $_-;_-* “-“?? $_-;_-@_-
And there you have it. Four distinct formats and now we know why the zeroes show as -. There are some additional signs used to align the cells in the format above, but I will not get into that. My intention was to lay the groundwork here. If this topic interests you further, here is a great place to start.