Number Formatting Pt. 2: Customizing Your Numbers

Nnumber1ow that your boss is thoroughly impressed with your number formatting skills, let’s blow her mind with some more!

In this post we will teach you how to customize numbers and dates. With that knowledge, you will be able to customize all of your other values. Let’s start with numbers.

By default, Excel does not separate thousands with a comma. Instead, number are ugly and look like this:


But wait, you want them to look prettier! You would like them comma separated, negatives to have parentheses, and negatives to also be red. How do we do this?

First, select your numbers. On the Home tab, under Number, click on the drop down arrow shown below:


The following menu will appear. Under Category, select Number.


From here, you can check the “Use 1000 Separator” box and then select how you would like negatives presented. After you are done, click OK. Don’t your numbers look so much better?


Now on to dates. In the accounting profession, we like our dates formated like this: 04/2013. This, however, is not a default date format. Here is how you can change the date format (or any other value for that matter).

First, select the cell you want to format and click on that same drop down arrow mentioned above. Once the form appears, select Custom.


You will notice a whole host of options. You will notice that as you select each Type, Excel will show you exactly how it would look under the Sample section on the form. However, our date format is not listed. It looks like we will have to create our own. It is simple. Under Type, tell Excel how you would like it formatted. For instance, I want 4/15/2013 to be formatted as 04/2013. So I put “mm/yyyy” under Type. Once you are done, click OK.



And there you have it! Now it is your turn. Mess with each of the categories, and if you are feeling really adventurous, create a custom number format. Ready for your boss to promote you on the spot?