Software & Apps MS Office Formatting Negative, Long, and Special Numbers in Excel Share Pin Email Print Pexels MS Office Excel Word Powerpoint Outlook By Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. our editorial process Ted French Updated January 30, 2020 Number formatting in Excel changes the appearance of a number or value in a cell in the worksheet and is attached to the cell and not to the value in the cell. It doesn't change the number in the cell, 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. When the cell is selected, the plain, unformatted number displays in the formula bar above the worksheet. Instructions in this article apply to Excel 2019, 2016, 2013, 2010, Excel for Mac, Excel for Office 365, and Excel Online. 01 of 04 Format Numbers in Excel Overview 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. 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 to display negative numbers located in the Format Cells dialog box. These include: Red textRound bracketsRound brackets and red text Display negative numbers in red to make it easier to find them; especially if they are the result of a formula. This may be difficult to track in a large worksheet. Brackets are often used to make negative numbers easier to identify for data that will be printed in black and white. Change Negative Number Formatting in the Format Cells Dialog BoxHighlight the data to be formatted.On the ribbon, go to Home. In the Number group, select the dialog box launcher. It's the small downward pointing arrow in the bottom right corner of group. The Format Cells dialog box opens.In the Category section, select Number.Choose an option to display negative numbers. Choose either negative sign, red text, brackets around text, or red text and brackets.Select OK to close the dialog box and return to the worksheet.Negative values in the selected data are formatted with the chosen option. 02 of 04 Format Numbers as Fractions in Excel 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: Display decimals as one, two, or three-digit fractions in the denominator portion of the number.Display decimals as commonly used fractions, such as one half and one quarter. Format First, Enter Data Second Apply the fraction format to cells before entering the data to avoid unexpected results. If fractions with numerators between 1 and 12 (such as 1/2 or 12/64) are entered into cells with the General format, the numbers are changed into dates. For example: 1/2 is returned as 2-Jan12/64 is returned as Dec-64 (December 1964) Fractions with numerators greater than 12 are converted into text and may cause problems if used in calculations. Format Numbers as Fractions in the Format Cells Dialog BoxHighlight the cells to be formatted as fractions.Go to Home.In the Number group, select the dialog box launcher to open the Format Cells dialog box.In the Category section, select Fraction to display the list of available fraction formats.In the Type list, choose the format you want to use to display decimal numbers as fractions.Select OK to close the dialog box and return to the worksheet.Decimal numbers entered into the formatted range display as fractions. 03 of 04 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 it may display unexpected results. By default, all cells in an Excel worksheet use the General format and have these characteristics: 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, use one of two options depending on the type of number stored in a worksheet: Use the Special format category in the Format Cells dialog box.Format numbers as text. To ensure that special numbers display correctly when entered, format the cell or cells using one of the two formats 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.Zipcodes: Retains leading zeros in numbers, which are removed with regular number formats. For example, 00987.Zipcode + 4 numbers: Separates nine-digit numbers into groups of five digits and four separated by a hyphen. It also retains any leading zeros. For example, 00987-5555. Locale-Sensitive The Locale dropdown list contains 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. Use Special Formatting for Numbers in the Format Cells Dialog BoxHighlight the cells to be formatted as fractions.Go to Home. In the Number group, select the dialog box launcher to open the Format Cells dialog box.In the Category section, select Special to display the list of available special formats.If necessary, select the Locale dropdown menu to change locations.In the Type list, choose one of the format options to display special numbers.Select OK to close the dialog box and return to the worksheet.Appropriate numbers entered into the formatted range are displayed with the selected special format. 04 of 04 Format Numbers as Text in Excel 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. Apply this formatting before you enter 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. Use Text Data in Formulas and Functions When text formatting is used (see cell A4 above), the same number displays correctly, and, since the character limit per cell for the text format is 1,024, irrational numbers such as Pi ( Π ) and Phi ( Φ ) cannot be displayed in their entirety. In addition to keeping the number identical to the way it was entered, numbers formatted as text can be used in formulas using basic mathematical operations, such as adding and subtracting as shown in cell A8 above. Numbers formatted as text cannot be used 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: A result of zero for SUM in cell A9.The error value #DIV/0! for AVERAGE in 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 the number. Otherwise, it will be affected by the current cell formatting. Select a cell or a range of cells that you want to convert to text format.Go to Home.Select the Number Format dropdown arrow to display a menu of format options.Scroll to the bottom of the menu and choose Text. There are no additional options for text format. Text to the Left, Numbers to the RightA visual clue to help you determine the format of a cell 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 display 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 also display a small green triangle in the top left corner of the cell indicating that the data may be formatted incorrectly.