Thursday, August 6, 2020

Available number formats in excel

In Excel, you can format numbers in cells for things like currency, percentages, decimals, dates, phone numbers, or social security numbers.

Formula bar and a related cell
Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.
  1. Select a cell or a cell range.

  2. On the Home tab, select Number from the drop-down.
    General
    Or, you can choose one of these options:

    • Press CTRL + 1 and select Number.

    • Right-click the cell or cell range, select Format Cells… , and select Number.

    • Select the dialog box launcher Alignment Settings next to Number Dialog box launcher and then select Number.

  3. Select the format you want.

Number formats

To see all available number formats, click the Dialog Box Launcher next to Number on the Home tab in the Number group.

Dialog Box Launcher in Number group

Format

Description

General

The default number format that Excel applies when you type a number. For the most part, numbers that are formatted with the General format are displayed just the way you type them. However, if the cell is not wide enough to show the entire number, the General format rounds the numbers with decimals. The General number format also uses scientific (exponential) notation for large numbers (12 or more digits).

Number

Used for the general display of numbers. You can specify the number of decimal places that you want to use, whether you want to use a thousands separator, and how you want to display negative numbers.

Currency

Used for general monetary values and displays the default currency symbol with numbers. You can specify the number of decimal places that you want to use, whether you want to use a thousands separator, and how you want to display negative numbers.

Accounting

Also used for monetary values, but it aligns the currency symbols and decimal points of numbers in a column.

Date

Displays date and time serial numbers as date values, according to the type and locale (location) that you specify. Date formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in Control Panel. Formats without an asterisk are not affected by Control Panel settings.

Time

Displays date and time serial numbers as time values, according to the type and locale (location) that you specify. Time formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in Control Panel. Formats without an asterisk are not affected by Control Panel settings.

Percentage

Multiplies the cell value by 100 and displays the result with a percent (%) symbol. You can specify the number of decimal places that you want to use.

Fraction

Displays a number as a fraction, according to the type of fraction that you specify.

Scientific

Displays a number in exponential notation, replacing part of the number with E+n, where E (which stands for Exponent) multiplies the preceding number by 10 to the nth power. For example, a 2-decimal Scientific format displays 12345678901 as 1.23E+10, which is 1.23 times 10 to the 10th power. You can specify the number of decimal places that you want to use.

Text

Treats the content of a cell as text and displays the content exactly as you type it, even when you type numbers.

Special

Displays a number as a postal code (ZIP Code), phone number, or Social Security number.

Custom

Allows you to modify a copy of an existing number format code. Use this format to create a custom number format that is added to the list of number format codes. You can add between 200 and 250 custom number formats, depending on the language version of Excel that is installed on your computer. For more information about custom formats, see Create or delete a custom number format.

You can apply different formats to numbers to change how they appear. The formats only change how the numbers are displayed and don't affect the values. For example, if you want a number to show as currency, you'd click the cell with the number value > Currency.

Available number formats

Applying a number format only changes how the number is displayed and doesn't affect cell values that's used to perform calculations. You can see the actual value in the formula bar.

View of a number value in the function bar

Here's a list of available number formats and how you can use them in Excel for the web:

Number format

Description

General

Default number format. If the cell isn't wide enough to show the entire number, this format rounds the number. For example, 25.76 shows as 26.

Also, if the number is 12 or more digits, General format displays the value with scientific (exponential) notation.

a number value shows as exponential when it is twelve digits or more

Number

Works very much like the General format but varies how it shows numbers with decimal place separators and negative numbers. Here are some examples of how both formats display numbers:

sample of how numbers appear with different formats like Number and General formats.

Currency

Shows a monetary symbol with numbers. You can specify the number of decimal places with Increase Decimal or Decrease Decimal.

increase or decrease decimal places on number formatting

Accounting

Also used for monetary values, but aligns the currency symbols and decimal points of numbers in a column.

Short Date

Shows date in this format:

short date format

Long Date

Shows month, day and year in this format:

Long date format

Time

Shows number date and time serial numbers as time values.

Percentage

Multiplies the cell value by 100 and displays the result with a percent (%) symbol.

Use Increase Decimal or Decrease Decimal to specify the number of decimal places you want.

increase or decrease decimal places on number formatting

Fraction

Shows the number as a fraction. For example, 0.5 displays as ½.

Scientific

Displays numbers in exponential notation, replacing part of the number with E+n, where E (Exponent) multiplies the preceding number by 10 to the nth power. For example, a 2-decimal Scientific format displays 12345678901 as 1.23E+10, which is 1.23 times 10 to the 10th power. To specify the number of decimal places you want to use, apply Increase Decimal or Decrease Decimal.

Text

Treats the cell value as text and displays it exactly as you type it, even when you type numbers. Learn more about formatting numbers as text.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Format numbers as currency in Excel

Format numbers

No comments:

Post a Comment