Formatting Negative, Long, and Special Numbers in Excel

 Number formatting in Excel is used to change the appearance of a number or value in a cell in the worksheet.

Number formatting is attached to the cell and not to the value in the cell. In other words, number formatting does not change the actual number in the cell, but just the way it appears.

For example, applying currency, percent, or number formatting to data is only visible in the cell where the number is located. Clicking on that cell will show the plain, unformatted number in the formula bar above the worksheet.

01
of 04

Formatting Numbers in Excel Overview

Negative Number Format Options in the Excel Format Cells Dialog Box
Negative Number Format Options. © Ted French

The default format for cells containing all data is the General style. This style has no specific format and, by default, displays numbers without dollar signs or commas and mixed numbers - numbers containing a fractional component - are not limited to a specific number of decimal places.

Number formatting can be applied to a single cell, entire columns or rows, a select range of cells, or the entire worksheet.

Negative Number Formatting

By default, negative numbers are identified using the negative sign or dash ( - ) to the left of the number. Excel has several other format options for displaying negative numbers located in the Format Cells dialog box. These include:

  • red text
  • round brackets
  • round brackets and red text

Displaying negative numbers in red can make it easier to find them - especially if they are the results of formulas which may be difficult to track in a large worksheet.

Brackets are often used to make negative numbers easier to identify for data that is to be printed in black and white.

Changing Negative Number Formatting in the Format Cells Dialog Box

  1. Highlight the data to be formatted
  2. Click on the Home tab of the ribbon
  3.  Click on the dialog box launcher - the small downward pointing arrow in the bottom right corner of the Number icon group on the ribbon to open the Format Cells dialog box
  4. Click on Number under the Category section of the dialog box
  5. Choose an option for displaying negative numbers - red, brackets, or red and brackets
  6. Click OK to close the dialog box and return to the worksheet
  7. Negative values in the selected data should now be formatted  with the chosen options
02
of 04

Formatting Numbers as Fractions in Excel

Formatting Numbers as Fractions in Excel
Formatting Numbers as Fractions in Excel. © Ted French

Use the Fraction format to display numbers as actual fractions, rather than decimals. As listed under the Description column in the image above, the available options for fractions include:

  • displaying decimals as one, two, or three digit fractions in the denominator portion of the number;
  • displaying decimals as commonly used fractions - such as one half and one quarter.

Format First, Data Second

Usually, it is best to apply the fraction format to cells before entering the data to avoid unexpected results.

For example, if fractions with numerators between one and 12 - such as 1/2 or 12/64 - are entered into cells with the General format, the numbers will be changed into dates such as:

  • 1/2 will be returned as 2-Jan
  • 12/64 will be returned as Jan-64 (January, 1964)

As well, fractions with numerators greater than 12 will be converted into text and may cause problems if used in calculations.​

Format Numbers as Fractions in the Format Cells Dialog Box

  1. Highlight the cells to be formatted as fractions
  2. Click on the Home tab of the ribbon
  3.  Click on the dialog box launcher - the small downward pointing arrow in the bottom right corner of the Number icon group on the ribbon to open the Format Cells dialog box
  4. Click on Fraction under the Category section of the dialog box to display the list of available fractions formats on the right-hand side of the dialog box
  5. Choose a format for displaying decimal numbers as fractions from the list
  6. Click OK to close the dialog box and return to the worksheet
  7. Decimal numbers entered into the formatted range should be displayed as fractions
03
of 04

Formatting Special Numbers in Excel

Special Number Format Options in the Excel Format Cells Dialog Box
Special Number Format Options. © Ted French

If you use Excel to store identification numbers - such as zip codes or phone numbers - you might find the number being changed or displayed with unexpected results.

By default, all cells in an Excel worksheet use the General format, and the characteristics of this format include:

  • leading zeros in numbers are removed,
  • numbers with more than 11 digits are converted to scientific (or exponential) notation.

Similarly, the Number format is limited to displaying numbers of 15 digits in length. Any digits beyond this limit are rounded down to zeros

To avoid problems with special numbers, two options can be used depending on what type of number is being stored in a worksheet:

  • the special format category in the Format Cells dialog box (see below);
  • formatting numbers as text (next page).

To ensure that special numbers are displayed correctly when entered, format the cell or cells using one of the two formats below before entering the number.

Special Format Category

The Special category in the Format Cells dialog box automatically applies special formatting to such numbers as:

  • phone numbers - encloses the first three digits of a 10 digit number in parentheses and separates the remaining seven digits into two groups separated by a hyphen. For example: (800) 555-1212
  • Social Security numbers - separates nine-digit numbers into groups of three, two, and four separated by hyphens. For example: 555-00-9999
  • zip codes - retains leading zeros in numbers - which are removed with regular number formats. For example: 00987

  • zip code + 4: separates nine-digit numbers into groups of five digits and four separated by a hyphen. Also retains any leading zeros. Example: 00987-5555

Locale Sensitive

The drop down list under the Locale gives options to format special numbers appropriate to specific countries. For example, if the Locale is changed to English (Canada) the available options are Phone Number and Social Insurance Number - which are commonly used special numbers for that country.

Using Special Formatting for Numbers in the Format Cells Dialog Box

  1. Highlight the cells to be formatted as fractions
  2. Click on the Home tab of the ribbon
  3.  Click on the dialog box launcher - the small downward pointing arrow in the bottom right corner of the Number icon group on the ribbon to open the Format Cells dialog box
  4. Click on Special under the Category section of the dialog box to display the list of available special formats on the right-hand side of the dialog box
  5. If necessary, click on the Locale option to change locations
  6. Choose one of the format options for displaying special numbers from the list
  7. Click OK to close the dialog box and return to the worksheet
  8. Appropriate numbers entered into the formatted range should be displayed as with the selected special format
04
of 04

Formatting Numbers as Text in Excel

Format Long Numbers As Text in Excel
Format Long Numbers As Text in Excel. © Ted French

To ensure that long numbers - such as 16 digit credit card and bank card numbers - are displayed correctly when entered, format the cell or cells using the Text format - preferably before entering the data.

By default, all cells in an Excel worksheet use the General format, and one of the characteristics of this format is that numbers with more than 11 digits are converted to scientific (or exponential) notation - as shown in cell A2 in the image above.

Similarly, the Number format is limited to displaying numbers of 15 digits in length. Any digits beyond this limit are rounded down to zeros.

In cell A3 above, the number 1234567891234567 is changed to 123456789123450 when the cell is set for number formatting.

Using Text Data in Formulas and Functions

Conversely, when text formatting is used - cell A4 above - the same number displays correctly, and, since the character limit per cell for the text format is 1,024, it is probably only irrational numbers such as Pi ( Π ) and Phi ( Φ ) that cannot be displayed in their entirety.

In addition to keeping the number identical to the way it was entered, numbers formatted as text can still be used in formulas using basic mathematical operations - such as adding and subtracting as shown in cell A8 above.

They cannot, however, be used it in calculations with some of Excel's functions - such as SUM and AVERAGE, as the cells containing the data are treated as empty and return:

Steps to Formatting a Cell for Text

As with other formats, it is important to format the cell for text data before entering the number - otherwise, it will be affected by the current cell formatting.

  1. Click on the cell or select a range of cells that you want to convert to text format
  2. Click on the Home tab of the ribbon
  3. Click on the down arrow next to the Number Format box - displays General by default - to open the drop down menu of format options
  4. Scroll to the bottom of the menu and click on the Text option - there are no additional options for text format

Text to the Left, Numbers to the Right

A visual clue to help you determine a cell's format is to look at the alignment of the data.

By default in Excel, text data is aligned on the left in a cell and number data on the right. If the default alignment for a range formatted as text has not been changed, numbers entered into that range should be displayed on the left hand side of the cells as shown in cell C5 in the image above.

In addition, as shown in cells A4 to A7, numbers formatted as text will also display a small green triangle in the top left corner of the cell indicating that the data may be formatted incorrectly.