Number Formats

Cells in a spreadsheet can contain many different kinds of data.  Number formatting allows you to define a specific appearance and format for number values entered into cells. 

For example, if you are creating an invoice for services, it would likely contain a columns of dates, text, and money amounts.  It may look something like this:

Date Description Hours Rate Amount
6/20/98 Technical consulting services 5.0 $50.00 $250.00
6/21/98 Installation of new computer 3.0 $25.00 $75.00
  Totals     $325.00

This table uses at least three different number formats: date, simple decimal, and currency formats.  Number formatting provides the ability to pre-define how numbers or text appear in a cell.  This saves you from having to type each number in the exact same format.

Number Format Conventions

Number formats for cells are set using the Number Format dialog box (shown below).   In this dialog box, the formats are displayed using a standard format notation.  The following table describes these conventions.

Character Description
# Displays only significant digits.  Insignificant zeros are omitted. For example, in the format #.###, 0.010 would be displayed .01.
0 Displays digits regardless of significance.  For example, in the format 0.000, 0.010 would be displayed as 0.010
? Adds a space for insignificant zeros on either side of a decimal point.  This is used to ensure decimal points align correctly and that fractions appear correctly formatted. For example, the format # ???/??? would display 1.5 as 1--1/2-- where the dashes represent hard-spaces.
$ Dollar sign.  Used for currency values.
% Percentage sign.  Used for percentage formats. 
(  ) Parenthesis.  Used in place of negative sign.  In the format (0.00), -1.55 would appear as (1.55)
[Red] Indicates that the value is red when negative. In the format [Red](#.##), -1.50 would appear as (1.5).
E Scientific notation indicator. In the format 0.00E-00, 0.000000000005 would appear as 5.00E-12.

To Apply a Number Format

  1. Select the cells, rows, or columns you want to format.

  2. Click the Number Formatting tool PBnumber.gif (925 bytes).  The Number Format dialog box is displayed. 


    numberformat.gif (10870 bytes)

  3. In the Categories list, select the type of number format you wish to apply.  The Description box displays a brief description of each category when selected. 

  4. In the Types list, select the proper format for the number.  Use the standard notation conventions table above to determine which format is appropriate for the cells you selected.  The Sample box displays an example of how that type of number looks.

  5. Enter the number of Decimal Places for the number.  The spreadsheet automatically rounds numbers to the configured number of decimal places.  For example, if you set a cell to have 2 decimal places, 2.995 would round to 3.00.  .

  6. Check Use 1000 Separator (,) to place a comma between every three whole digits.  When checked, the display in the Types list changes to indicate how the commas are applied to the format.  Uncheck the box to omit commas between thousands.

  7. When finished, click OK.  The selected cells are formatted appropriately.

Number Truncations

If a number value is too large for a cell, the cell displays pound signs (####).   To display the entire number, the column must be widened.  A truncated value remains in the cell and continues to participate in any formulas or references.  The display of pound signs merely indicates that the column is not large enough to display the number. 


Copyright © 1999, Gobe Software, Inc