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 (see Selecting Worksheet Cells), and then click the Data | Format | Format Cells command. Use the Number page to change the numeric data display in the worksheet. 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 surrounded by quotes) which are read as text and not as a number. The Transform command can be used to perform a mathematical function, such as ATOI(X), to convert a text string to an integer value. Alternatively, the cell can be converted to numbers with the Text to Number command.

Select the number format options on the Number page of the Format Cells dialog.

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.

  • General displays numbers as fixed or exponential, whichever is shorter.
  • Fixed displays numbers as d.ddd. The number to the left of the decimal can vary. Set the number to the right of the decimal in theDecimal Digitsbox.
  • Exponential displays numbers as d.ddde+dd. Set the number of digits to the right of the decimal in the Decimal Digits box.
  • Currency displays fixed numbers with a currency symbol such as the dollar sign ($).
  • Percent displays numeric values (such as 0.13) as percentages with a percent symbol suffix (13%).
  • Date/Time formats the cells as date and/or time. Select Date/Time and then type or select the Date/Time format.

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.

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. Type the desired format into the Date/Time format field, or click the button to insert a date/time format with the Date/Time Format Builder dialog. Available formats are made of combinations of day, month, year, AD/BC or CE/BCE designation, hours, minutes, seconds, and AM/PM designation. Days are shown as d or dd. Months are shown as M, MM, MMM, MMMM, or MMMMM. Years are shown as yy or yyyy. Hours are shown as h or hh. Minutes are shown as m or mm. Seconds are shown as ss. Sub-seconds are displayed as ss.0 or ss.000. AM/PM designation are shown as tt or TT. BC/AD designation is shown as gg or GG. BCE/CE designation is shown as g, ggg, G, or GGG. Total elapsed time is shown as [h]. After clicking on the Date/Time format, the Sample updates to show a value in the selected format.

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