Number Formatting

If a cell is not formatted to a particular type, Excel will read that value and try to guess what format should it be. For example, if you enter the value 12 or 2.4, Excel will treat this value as a decimal number. If you enter 4 1/5 it will be treated as a simple fraction.

Excel treats entered value as a number, only if it doesn’t contain any additional characters. Otherwise, Excel will treat this value as text. For example, 5A will be treated as text, not as number 5.

CAUTION

Number formats

In the Format Cells Ctrl + Shift + F window, in the Number tab, you can change data formatting by selecting one of the available predefined formats.

General

When you create a new worksheet, each cell has a default cell format- General. All cells from a newly created worksheet will use this data type.

These are a few rules that characterize this type:

  1. Excel removes leading zeroes. Number 03.00 will be stored as 3. One exception to this rule is when the value is between –1 and 1 (e.g. .45 will be converted to 0.45).
  2. If the number is a decimal fraction, then Excel displays it in a way to fit it in the cell. If the fractional part does not fit in the cell, then it is rounded. In a similar way, Excel deals with integers. When the value is long, Excel displays it in scientific notation. When the value is even longer it uses ### instead of displaying the number.

Example 1:

The numbers in cells B2, C2, and D2 are the same as the numbers in cells B3, C3, and D3.

Number

As the name suggests, this format is used for formatting numbers. Here, you can specify the number of decimal places so that the fractional part always stays in the same place.

In this type, you can also use the 1000 separator, which is useful for very large numbers.

Example 2:

Look at the following example. Cell B2 is formatted without, and cell B3 with the thousands separator.

Currency

The currency format includes the currency symbol, and the value is displayed with commas.

Accounting

The accounting format is very similar to the currency format. The difference is that in the accounting format, the currency sign is placed just at the left edge of the cell and not, as in the currency format near the number. Besides, in the accounting format, the space between the value and the right edge is greater than in the currency format.

Percentage

The percentage format converts numbers to percentages, for example:

1 to 100%

0.321 to 32.1%

2.12 is 212%

In the percentage format, you can set the number of decimal places.

Fraction

With the fraction format, you can convert a number to a fraction. When you format cells to fractional type you can enter a fraction, such as 1/3. Otherwise, when the cell is of the general type it will be treated not as a fraction, but as a date (January 3, 2014 (current year)).  Therefore, in such cases, you should precede this type by using 0 at the beginning (0 1/3).

Scientific

Scientific notation displays the value in an abbreviated form. For example, the number 1234567890 will be displayed as 1.23E+09. You can also set the number of decimal places to increase precision.

Special

This is the formatting for data, such as Zip Code, Phone Number, or Social Security Number.

Excel 365 Update

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.