Custom Formatting: it's magic!

Formatting cells is no big deal? OK, we can see why you might think that. After all, what's so smart about typing in a number like this 1234.5689753641 only to find that automatically it looks like this £1,234.57? Well, nothing really. How about this, though? You type into your spreadsheet, say, -1234.5689753641 and automatically is looks like this: (1234.57) Adverse Variance ... automatically.

Getting more interesting now? Yes, much more interesting: read on!

Flexibility

For some reason, I had completely forgotten about this page but here we are now. The point made in the introduction to this page is that Custom Formatting is very powerful ... let's go straight there now.

Right Clicking a Cell

 

Everything we are talking about here starts with the right clicking of a cell or range of cells. Having right clicked a cell or range of cells, a dialogue box opens and you have to click the link Format Cells ...

 

cust_format_menu

 

As soon as you click on this link, another dialogue box opens which is the gold mine of formatting that we will plunder here!

 

cust_format_2

 

We can use Excel's built in formats under any one of the headings on the left hand side of this dialogue box and it will do the job for us. We can leave the format as General ... essentially no formatting at all. We can use Number formatting which provides thousands separators, decimal places and negative numbers as, for example, red, red with a minus sign ... we can ask Excel to format our numbers as a percentage ... as a fraction ...

The Gold Mine

 

What we are about to consider really gets us into the gold mine but even so we will leave a great deal of treasure behind: not because we are not so smart or can't be bothered to do the work but because the number of possible combinations of formatting features we can develop is vast. What we will do here is to suggest some simple ideas and then open your eyes to things that you have probably never imagined before!

Basic Formatting

 

Please note: to make this work, you need you to enter code into the Type box, as highlighted below:

 

cust_format_3

 

The first thing to notice is that there are many pre programmed formats available to us. Having clicked on the pre programmed 0% you can see highlighted above, move down the list by using the down arrow or click with the mouse on any of the other pre programmed formats: you should watch the Type box now and see what happens there because we will emulate that!

Dates and Times

 

Dates and times cause novice Excel users a lot of problems but we are about to take control and walk away with our first nugget of gold (OK! I won't mention gold mines and nuggets again!!)

 

Select one cell or range of cells, right click the cell and select Format Cells... and then type or copy the following into the Type box:

 

dd mmm yy ... click OK and then enter 12/11/2010 in the cell you have just formatted

 

Select another cell and right click it, select Format Cells... and then enter or copy this

 

ddd dd mmmm yyyy ... click OK and then enter 12/11/2010 in the cell you have just formatted

 

Compare the results of the two cells you have just formatted now! You should see

 

12 11 10 in the first cell

Fri 12 November 2010 in the second cell

 

Try these formats now and use 12/11/2010 or any other dates you prefer:

 

dd/mmm/yyyy

ddd/mmm/yy

dddd_dd_mmmm_yyyy

 

now create your own formats!

 

Times are formatted in a similar way to the way we have just formatted dates. Select a cell ... Format Cells ...

 

enter the format hh:mm and in your chosen cell type a time in this format 9:35 then over type with 14:42 and any other time you like ... you have now discovered the format for the 24 hour clock as 9:35 became 09:35 and 14:42 stayed as 14:42.

 

Change the format to h:mm and enter the times in the formatted cell: 9:35 and 14:42 ... 9:35 will stay as 9:35 and 14:42 stays as 14:42 ... you can now program both the 12 hour and 24 hour clock!

 

How about this? Format a cell like this hh:mm:ss AM/PM then enter our two times 9:35 and 14:42. What does the addition of ss and AM/PM do for us? Try this:

 

9:35 becomes 9:35:00 AM

14:42 becomes 2:42:00 PM

11:35:29 becomes 11:35:29 AM

Adding Times

 

Adding times together is possible in Excel but you need to know one specific thing to help you otherwise you might believe that Excel cannot add times at all!

 

Format the range of cells A1:A4 as hh:mm

Enter 9:35 in cell A1 and 14:42 in cell A2 and 11:35 in cell A3

In cell A4 enter =sum(A1:A3)

 

What answer did you get? You should get 11:52 ... but that's not right is it?

 

Now format cell A4 as [h]:mm and your SUM function should show you 35:52 ... that's better isn't it? Notice, it's always [h]:mm and [hh]:mm adds no value or functionality!

Colour Changing

The default colour of text in Excel is black: that means that everything you type should be in black font. If that's not the case then someone has changed your settings! In any case, you can take control of the colour of any cell and range of cells you wish.

 

Select the range A1:A10

Right click ...

enter [Blue];[Red] click OK

 

In cell A1 now enter 123 and it becomes 123

In cell A2 now enter -123 and it becomes -123

In cell A3 now enter 456 and it becomes ... ?

In cell A4 now enter -456 and it becomes ... ?

In cell A5 now enter 0.123 and it becomes ... ?

In cell A6 now enter -0.123 and it becomes ... ?

 

Now format cells A1:A10 ... as you open the formatting dialogue box you should see that Excel has changed [Blue];[Red] to [Blue]General;[Red]General

Format the cells as [Red];[Blue] and see what happens to the numbers you entered in cells A1:A6 and what general rule can we see being applied?

 

You should see that positive numbers turn from blue to red and negative red numbers become negative blue numbers.

 

The general rule is that the first part of formatting numbers is ALWAYS for the positive numbers, the second part of formatting numbers is ALWAYS for the negative numbers ... there are two more elements to formatting numbers and the syntax of the entire format is as follows:

 

positive; negative;zero;text

 

All elements of this format are separated by a semi colon.

 

Format the range A1:A10 as follows:

 

[Blue]#,##0.00;[Red]#,##0.00;0.00;""

 

Now enter the following

 

In cell A1 now enter 1230.38 and it becomes 1,230.38

In cell A2 now enter -1230.38 and it becomes -1,230.38

In cell A3 now enter 4567.89 and it becomes ... ?

In cell A4 now enter -4567.89 and it becomes ... ?

In cell A5 now enter 0 and it becomes ... 0.00

In cell A6 now enter the two words "a number" and it becomes ... it should show absolutely nothing!! That is, the text you entered in the cell is stored in the cell but it shows as if it's empty.

 

The following screenshot shows what's happened ... look at the formula bar as well as the range A1:A10

 

cust_format_4

 

By the way, if you want your positive numbers to show as orange or green or purple ... use [Orange] ... and the same applies to the colours of your negative numbers and the zero value.

 

How would you program cells to show FOUR decimal places as opposed to the TWO decimal places we have just demonstrated? Try it!

Words AND Numbers in Cells

 

Accountants often prepare reports that show values and words related to those numbers on a regular basis. Exel can do this in a fascinating way and in a way that will astonish your friends!

 

Let's imagine we are reporting our end of period budget figures, as follows:

 

cust_format_5

 

We already know how to format cells B2:C6 but what about D2:D6? Here it is:

 

Program the formatting for cells B2:B6 yourself

For cells D2:D6 format as follows: #,##0" Fav";[Red](#,##0)" Adv";0

 

All we do is to type double inverted commas within the positive, negative ranges and inside those inverted commas we can type anything we like. Fav means Favourable and Adv means Adverse. Some people use Unfavourable instead of Adverse so you might want to use Unf instead of Adv ... as you wish!

 

In case you need it, the formatting underlying the budget report itself is as follows:

 

cust_format_6

 

As a matter of interest, just check on this words in cells feature ... select, for example, cells D2:D3 and notice that the result is that Excel adds them together and shows the sum of 50. Select D3:D4 and Excel shows the sum of 0. That is, Excel treats cells formatted in this way as cells with values and not cells with text.

 

A very useful feature.

 

Think of other ways in which you might use text in cells.

Zero Suppression

 

One formatting feature that is often used in reporting tables of numbers is that of suppressing zero: that is, in some tables where there might be many zero values, we can tell Excel to show nothing in a zero value cell, in the same way that we have already programmed text to show as if text based cells were empty.

 

Here is the before and after:

 

cust_format_7

 

With the table title for the table on the left being in cell A1, there is no custom formatting of the range B4:D13 but in the range G4:I13 the formatting is:

 

0;0;""

 

So simple but so effective!!

The Formatting is Saved

 

It's worth noting that every custom format you have programmed is stored by Excel: just go to the custom formatting dialogue box and scroll down to the bottom of the custom formatting listing and our own, new, styles are there. This means that you can go down there, click on them and either use them again or edit them in some way to save some programming time. Look at the screenshot that follows and you will see the formats we have been working on here are shown as promised!

 

cust_format_8

Conclusions

 

Custom Formatting is something that intermediate and advanced Excel users must use. Accountants and other financial reporters need to use custom formatting. Engineers, scientists and many others need custom formatting too.

 

This page has shown the entrance to the gold mine and you are encouraged to play with it now and make it work for you!

 

 

Duncan Williamson

26th November 2011