Thursday, January 13, 2005

Custom Formats in Excel

Excel allows you to format cells with a wide vareity of standard formats. However there is often a time when you need to customise a format. Examples can include adding leading zeros to numbers for a consistent result, special date formats, hiding data etc.

How to customise a format.
Select the cells you want and then click Format Cells and choose the Number Tab. In the bottom of the left section of format categories select Custom.
A wide range of pre generated custom formats will appear. You can actually modify one of these or add your own. The good news is that the custome formats you create stay with the spreadsheet on any pc. The bad news is that custom formats made in one spreadsheet won't be available in this list in another spreadhseet.

Three examples

Adding leading zeros to a range of 3 and 4 digit numbers.
Type 0000 in the box under the word Type on the right hand side of the format dialog box. Now 397 will display as 0397.

Hiding data in a cell.
Type ;;; in the Type box. No matter what colour font or cell background you cannot see the data in this cell. The data or formula can still be edited in the formula bar.

Special Numbers.
Red bracketed negative numbers, two decimal places, leading spaces before the numbers and the $ at the far left (with a space), two spaces at the right edge and commas at thousands.
$* #,##0.00 ;[Red]($* #,##0.00) ;

Note I have added an extra space to the non negative numbers so that the digits will line up with the negative ones. Also make sure you get the space at the beginning before the first $.

As you are fiddling with the custom format it displays a sample of the result.


Posted by: Jethro | 9:06 AM |