Leading the way in Microsoft Office Development

What are Number Formats ?

You can use number formats to change the way data is displayed.

A number format is a set of codes that Excel uses to determine how to display a value.

Formatting your data does not affect the actual values in the cells but just changes how the values are displayed.

The actual value stored in a cell is the value used in your formulas and is not the value that is displayed.

A lot of the time the actual value stored in a cell will have more significant figures than the value that is displayed.

When performing calculations, Excel will always use the actual value as opposed to the value that is displayed.

The number that is actually displayed in a cell can be different from the number that is actually in the cell. A cell could display the value 13, although the actual cell contains the number 12.76. The number has been formatted with no decimal places.

The cells C2, C4, C6 and C8 all contain the number 5.7550 although they have all been formatted differently.

The same is true for cells D2, D4, D6 and D8, they all contain the number 3.7500.

The formula in cells G2, G4, G6 and G8 all display the same result since the underlying values used in the formulas are identical.

Built-in Number Formats

There are a large number of built-in formats that can be applied to your values and they are separated into several categories.

You can use one of the many built-in number formats or create your own custom number format.

You can change the format of any cell (or range of cells) by selecting (Format > Cells)(Number tab) to display the Format Cells dialog box.

A custom number format can not only be used to format numbers, but cal also format dates, times as well as text.

This dialog box allows you to determine how numerical values (as well as dates, times and text) are displayed in your cells.

The Date and Time formats are discussed in detail in the Dates & Times section.

Custom Number Formats

You can change the number format of a cell to one of the other built-in number formats or you can create your own custom number format.

When you select "Custom" from the Category list all the available formats are displayed on the left.

You can format a cell to display any number or text without actually changing its real value by applying a Custom Format.

The sample box displays a sample of the format you have selected.

In addition to be able to choose from a large selection of pre-defined number formats you can also create your own.

You can also change the number of decimal places to show.

Formatting as you Type

Some number formatting is done automatically for you depending on the ??

Worksheet Functions also can apply a specific number format if the cell has the General number format.

If you precede the cell entry with currency symbols ??

If you enter numbers with commas, the commas appear in cell but not in the formula bar. This is exactly the same as applying a comma separated number format.

Format Painter

Although this is usually used more for formatting it can also be used to copy number formats.

Things to Remember

Using number formats does not affect the actual values but just the values that are displayed.

You may need to widen the column if your numbers are displayed as "#####".

You cannot delete any built-in number formats.

To make sure that your fractions are not interpreted as dates you should precede them with a zero and a space.

You can hide the values in cells by applying the null format "; ; ;". The values will still be visible in the formula bar.

It is important to remember that date formats take precedence over fraction formats. This is illustrated by typing "3/8" = "8-Mar"

All cells in a new worksheet are formatted with the General format by default.