Defining Number Formats

Instructions: You will use the Number tab in the Format Cells dialogue box and write your own format descriptor.

Comments: Being able to display numbers differently depending on what they are can be a great help to error check data as you enter it or spot anomalous figures.

Activity 7.2 The Format dialogue box appears as before. Select Number from the left hand list box, and then choose the 6th format down:

#,##0_);[Red](#,##0)

Note If you are writing your formats it is always a good idea to start with one that is close to what you want already.

Activity 7.3 Move the mouse over the Code box at the bottom. Click at the start of the format description.

The Excel format descriptors are not limited to formatting postive numbers, negative numbers and zeros differently. You can define your own boundary conditions using the syntax [<number] at the start of the description. In this way you can define everything formatted by the first descriptor as being numbers above a certain boundary, everything formatted by the second descriptor as being numbers below a certain boundary, and everything else is formatted by the third descriptor.

Activity 7.4 Edit the format description so that it looks like the one below:

[Blue][>500] "DM "#,##0;[Red][<-500] "DM "-#,##0;[Green] "DM " #,##0; "Wrong number"

What this format descriptor says is:

Format every number above 500 as blue, with the text DM in front and no decimal places.

Format every number below -500 as red, with the text DM in front and no decimal places.

Format every other number as green, with the text DM in front and no decimal places.

Format any piece of text as the text "Wrong Number" in black.

Activity 7.5 Click on <OK>. Now enter the cell F3 the following values to see the effect of the format descriptor on each value:

1000 <Enter>

-1000 <Enter>

100 <Enter>

Text <Enter>

Activity 7.6 When you have finished experimenting, select the cell F3 and press the <Del> key. This will clear the cell of all its contents.

OSU | CoF | Community | Search | Forestry Computing Helpdesk