Excel Unplugged

Custom Cell Format You Must Know in Excel


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.

Four for the Price of One

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.

Custom Cell Format

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;@

Custom Cell Format

Press Ok and Voila…

Custom Cell Format

Here’s what we see:

  • The positive numbers are all shown with a one decimal point precision and no 1000 separator. (0.0)
  • The negative numbers are shown with a minus sign, two decimal points precision and a 1000 separator. (-#,##0,00)
  • The zero values are shown with no decimal places and no 1000 separator. (0) This is something we wish to do on numerous occasions, to show zeroes differently that other numbers. Also, this is something that can be easily observed if you’re using the accounting format where the null values are shown as -.
  • And finally, the text is shown. (@)

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 (-);;@

Custom Cell Format

Here’s what we get

Custom Cell Format

  • So the positive numbers are green (if you chose a different language for your Office UI, then you must write the colors in that language), have a 1000 separator (if necessary) and two decimal places. ([Green] #,##0.00)
  • The negative are Red, the number itself is formatted in the same manner as the positive numbers are, but with the addition of a minus sign in brackets at the end. Yes, you can do that to, if you wanted to, you could leave the minus sign out completely.
  • Now here’s a shocker and one of the most useful things this format does. You can see that no format was set for the zero values and therefore, they are not visible. So if a format is not set, the values are hidden 🙂 Simply brilliant.
  • Nothing special to say about the Text format, but if we omitted the @, no text would show. It would still be in the cell, but it would not show.

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.

Comments 5

  1. Hryshko says:

    Awesome post!

    How did you get Text and 0 into your cells using the equation you provided “=RANDBETWEEN(-10000,10000)*RAND()”?

    Or did you just randomly select cells and type in Text and 0?

    1. Thanks,
      the text and the zeroes were entered manually… By Ctrl+Enter.

  2. Carol says:

    Using Excel 2013 and selecting a range of A1:Z30, I did not get the zeros nor text when I used Ctrl+Enter. I wonder if this is just for Excel 2010?

    1. You are right of course, the randbetween only gives you numbers in that range. Now some zeroes might be there, but only by chance… I added the zeroes and text manually and forgot to mention it in the post… Sorry and thank you for the heads up!

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: