Format Cells - Number

Cell numbers, alignment, or background color can be formatted through the Format Cells dialog. To format a cell, select the cells to be formatted, then select Data | Format | Format Cells. Use the Number page to change the numeric data display in the worksheet. This includes setting the numeric format for numbers and the date/time entries.

 

Number formatting has no effect on a numeric text string (numbers entered as text). For example, an ASCII data file might contain the numbers '8123 (numbers preceded by single quote) which are read as text and not as a number. The Data | Data | Transform command can be used to perform a mathematical function, such as ATOI(X), to convert a text string to an integer value.

 

Number Page
Use the
Number page to change the numeric
data display in the worksheet.

 

Type

The Type section contains the numeric format for the selected cells. Available options are General, Fixed, Exponential, Currency, Percent, and Date/Time. Click on the desired option.

 

Decimal Digits

The Decimal Digits controls the number of digits to the right of the decimal when the Type is set to Fixed, Exponential, Currency, or Percent. To change the Decimal Digits, highlight the existing value and type a new value. Alternatively, click the to increase or decrease the value.

 

Thousands Separator

The Thousands separator option controls whether a comma appears in the number, indicating thousands. When checked, a comma appears every three digits to the left of the decimal point. When unchecked, the number appears without the comma. Do not type a comma when entering data as this causes the number to be read as text.

 

If the File | Options Decimal separator is set to Comma or System default when comma is the system default, a period (.) will be displayed for the Thousands separator.

 

Sample

The Sample box displays the current number format.

 

Date/Time Format

When the Type is set to Date/Time, the Date/Time format option becomes available.

 

To create a Date/Time format, type the desired format in the Date/Time format (edit to change) box. Available entries are made of combinations of year, month, day, hours, minutes, seconds, AM/PM designation, BC/AD/BCE/CE designation, and/or a locale ID. Only the desired options need to be set. For instance, MMMM dd, yyyy can be entered to display month name date and year. The hours, minutes, seconds, AM/PM, and BC/AD/BCE/CE designation do not need to be specified. Other text can also be entered.

 

Entering this in the Date/Time format box results in:

 

 

being displayed in the Sample and in the worksheet cell.

 

Click the button to select a predefined format or build a custom format in the Date/Time Format Builder dialog.

 

OK or Cancel

Click OK to make the change to the cell format. Click Cancel to return to the worksheet without making the change.

 

 

See Also

Format Cells

Format Cells - Background

Format Cells - Alignment

Column Width

Row Height