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.
 Formatting numbers in Excel Overview
 Negative number format
 Change the format of negative numbers in the Format Cells dialog box
 Format numbers as fractions in Excel
 Format first, then data
 Format numbers as fractions in the Format Cells dialog box
 Format special numbers in Excel
 Special format category
 Country setting sensitive
 Use special formatting for numbers in the Format Cells dialog box
 Format numbers as text in Excel
 Using text data in formulas and functions
 Steps to Format a Cell for Text
 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

Select data to format

Click on the tab At home on the tape.

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 †

Click Number in section Category dialog box.

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

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

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 Jan64 (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

Select cells to be formatted as fractions

Click on the tab At home on the tape.

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 †

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

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

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

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 10digit number in parentheses and separate the remaining seven digits into two groups, separated by a hyphen. For example: (800) 5551212
 Citizen service number – divides ninedigit numbers into groups of three, two, and four, separated by hyphens. For example: 555009999
 Postal Code – preserves leading zeros in numbers stripped in normal number formats. For example: 00987
 Postal code + 4 : Divides ninedigit numbers into groups of five digits and four digits separated by a hyphen. Any leading zeros are also preserved. Example: 009875555
Country setting sensitive
dropdown 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

Select cells to be formatted as fractions

Click on the tab At home on the tape.

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 †

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

If necessary, press Language to change the location.

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

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

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 16digit 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.

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

Click on the tab At home on the tape.

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

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 leftaligned in a cell and numeric data is rightaligned. 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 A4A7, numbers formatted as text also display a small green triangle in the topleft corner of the cell, indicating that the data may not be formatted correctly.