Format 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 on a worksheet.

Number format is associated with the cell, not the value in the cell. In other words, number formatting doesn’t change the actual number in the cell, only how it looks.

For example, applying currency, percentage, or number formats to data appears only in the cell that contains the number. By clicking this cell, you will see a simple unformatted number in the formula bar above the worksheet.

The instructions in this article apply to Excel 2016, 2013, 2010, Excel for Mac, and Excel 365/Online.

Contents
  1. Formatting numbers in Excel Overview
  2. Negative number format
  3. Change the format of negative numbers in the Format Cells dialog box
  4. Format numbers as fractions in Excel
  5. Format first, then data
  6. Format numbers as fractions in the Format Cells dialog box
  7. Format special numbers in Excel
  8. Special format category
  9. Country setting sensitive
  10. Use special formatting for numbers in the Format Cells dialog box
  11. Format numbers as text in Excel
  12. Using text data in formulas and functions
  13. Steps to Format a Cell for Text
  14. Text on the left, numbers on the right

Formatting numbers in Excel Overview

The default format for cells containing all data is style General † This style has no specific format and by default displays numbers without dollar signs or commas, and mixed numbers – numbers that contain a fraction – are not limited to a certain number of decimal places.

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

Negative number format

Negative numbers are identified by default with a minus sign or dash (-) to the left of the number. Excel has several other formatting options for displaying negative numbers in the dialog box Cell format † They contain:

  • red text

  • round brackets

  • brackets and red text

Displaying negative numbers in red makes them easier to find, especially if they result from formulas that are difficult to follow on a large sheet.

Parentheses are often used to identify negative numbers for data to be printed in black and white.

Change the format of negative numbers in the Format Cells dialog box

  1. Select data to format

  2. Click on the tab At home on the tape.

  3. Click the start button for the dialog box – the small downward pointing arrow in the lower right corner of the icon group Number on the ribbon to open the dialog box Cell format

  4. Click Number in section Category dialog box.

  5. Select an option to display negative numbers – red and parentheses or red and parentheses

  6. Click Okay to close the dialog box and return to the sheet

  7. Negative values ​​in the selected data should now be formatted with the selected options

Format numbers as fractions in Excel

Use the format Fraction to represent numbers as real fractions instead of decimals. As indicated in the column Description in the image above, the following fracture options are available:

  • Display decimals as a fraction with one, two, or three digits in the significant part of the number.

  • Display decimals as commonly used fractions, such as half and quarter.

Format first, then data

It is usually best to apply fraction format to cells before entering data to avoid unexpected results.

For example, if fractions with numerators from one to 12, such as 1/2 or 12/64, are entered into cells in the format General numbers are replaced by dates, for example:

  • 1/2 will be returned on January 2nd

  • 12/64 is returned as Jan-64 (January 1964)

Fractions with numerators greater than 12 are also converted to text and can cause problems when used in calculations.

Format numbers as fractions in the Format Cells dialog box

  1. Select cells to be formatted as fractions

  2. Click on the tab At home on the tape.

  3. Click the start button for the dialog box – the small downward pointing arrow in the lower right corner of the icon group Number on the ribbon to open the dialog box Cell format

  4. Click Fraction in section Category dialog box to display a list of available fraction formats on the right side of the dialog box.

  5. Select a format for displaying decimal numbers as fractions from the list

  6. Click Okay to close the dialog box and return to the sheet

  7. Decimal numbers entered in a formatted range must be displayed as a fraction

Format special numbers in Excel

If you use Excel to store identification numbers, such as zip codes or phone numbers, the number may change or display with unexpected results.

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

  • leading zeros in numbers are removed,

  • numbers with more than 11 digits are converted to scientific (or exponential) notation.

Likewise, the size number limited to displaying numbers of up to 15 digits. All numbers exceeding this limit are rounded down to zero.

To avoid problems with special numbers, two options can be used, depending on the type of number stored on the worksheet:

  • Category format Special in the dialog box Cell format

  • Format numbers as text.

To ensure that special numbers are displayed correctly as you type, format the cell or cells with one of the two formats below before entering the number.

Special format category

Category Special in the dialog box Cell format automatically applies special formatting to numbers such as:

  • phone number – put the first three digits of a 10-digit number in parentheses and separate the remaining seven digits into two groups, separated by a hyphen. For example: (800) 555-1212
  • Citizen service number – divides nine-digit numbers into groups of three, two, and four, separated by hyphens. For example: 555-00-9999
  • Postal Code – preserves leading zeros in numbers stripped in normal number formats. For example: 00987
  • Postal code + 4 : Divides nine-digit numbers into groups of five digits and four digits separated by a hyphen. Any leading zeros are also preserved. Example: 00987-5555

Country setting sensitive

drop-down list below Local you can customize the format of special numbers to match specific countries. For example, if Language changed to English (Canada), the following options are available: phone number And The social security number usually used are special numbers for this country.

Using special formatting for numbers in a dialog box Cell format

  1. Select cells to be formatted as fractions

  2. Click on the tab At home on the tape.

  3. Click the start button for the dialog box – the small downward pointing arrow in the lower right corner of the icon group Number on the ribbon to open the dialog box Cell format

  4. Click Special in section Category dialog box to display a list of available special formats on the right side of the dialog box.

  5. If necessary, press Language to change the location.

  6. Select one of the format options to display special songs from the list

  7. Click Okay to close the dialog box and return to the sheet

  8. The corresponding numbers entered in the formatted range should be displayed according to the selected custom format.

Format numbers as text in Excel

To ensure that long numbers, such as 16-digit credit and debit card numbers, are displayed correctly, format the cell or cells as you type Text preferably before data entry.

By default, all cells in an Excel worksheet use the format General and one of the features 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.

Likewise, the size number limited to displaying numbers of up to 15 digits. All numbers exceeding this limit are rounded down to zero.

In cell A3 above, the number 1234567891234567 changes to 123456789123450 if the cell is set to number format.

Using text data in formulas and functions

Conversely, when text formatting is used – cell A4 above – the same number is displayed correctly, and since the character limit per cell for text formatting is 1024, these are probably just irrational numbers like Pi (Π) and Phi (Φ). it cannot be displayed in its entirety.

In addition to keeping the number identical to how it was entered, numbers formatted as text can still be used in formulas using basic math operations such as addition and subtraction, as shown in cell A8 above.

However, they cannot be used in calculations with some Excel functions such as: SUM and AVERAGE because cells with data are treated as empty and return:

  • The result of zero for SUM is cell A9.

  • Error value # DIV/0! for AVERAGE – cell A10.

Steps to Format a Cell for Text

As with other formats, it is important to format the cell for text data before entering a number, otherwise it will depend on the current format of the cell.

  1. Click a cell or select the range of cells you want to convert to text format

  2. Click on the tab At home on the tape.

  3. Click the down arrow next to the field Numeric format which is displayed by default General to open the layout options drop-down menu.

  4. Scroll to the bottom of the menu and select an option Text † No additional text formatting options

Text on the left, numbers on the right

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

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

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

Leave a Reply

Your email address will not be published.