How to change number, currency, or accounting format

Excel 365
International accounting and currency formats in non-current locale can become a problem in Microsoft Excel. You can apply a number, accounting, or currency format to some cells in Excel, but the default format may not be suitable for large ranges.

You won't find the Excel settings for each used format, as Excel uses the format configured in the operational system (Windows 10).

According to the U.S. standards, the Currency format and the Accounting format can be used to display monetary values:

  • In the Currency format, the currency symbol appears right next to the first digit in the cell, and the decimal points appear aligned in the column
  • In the Accounting format:
    • The currency symbol and the decimal points appear aligned in the column,
    • Zeros are indicated by a hyphen,
    • Negative numbers are indicated in brackets:
Number, Currency, Accounting formats in Excel 365

Automatic number formatting

When you type a value in a cell, Excel can automatically apply a predefined number format to a cell:

  • If a number contains commas, Excel applies comma formatting to separate thousands (such as 123,456,789).
  • If a number contains a percent symbol (%), it may be converted to a percentage format.

    Note: See below how to change the way to apply the percentage format in Excel.

  • If a number contains a slash (/), it may be converted to a date or fraction format.
  • If a number contains a hyphen (-), it may be converted to a date format.
  • If a number contains a colon (:) or is followed by a space and the uppercase or lowercase letter A or P, it may be converted to a time format.
  • If a number contains the letter E (uppercase or lowercase), it may be converted to scientific notation, also known as the exponential format.

Change format

To change the format for the selected cell or cells, do the following:

  • Using the keyboard:
  • On the Home tab, in the Number group, open the Number Format dropdown list:
    Number Format dropdown list in Excel 365

    From the Number Formats dropdown list:

    Number Formats in Excel 365

    Choose the format you need, see How to change number cells format easily for more details.

  • Do one of the following:
    • Right-click on the selection and choose Format Cells... in the popup menu:
      Format cells in popup menu Excel 365
    • On the Home tab, in the Number group, click the dialog box launcher:
      Format cells in Excel 365
    • Press Ctrl+1.

    In the Format Cells dialog box, on the Number tab, in the Category list, choose the format you need:

    Number formats in Format Cells dialog box Excel 365
  • Using the mouse: Number-Formatting Keyboard Shortcuts
    Key Combination Formatting Applied
    Ctrl+Shift+$ Currency format with two decimal places (negative numbers appear in parentheses) – see how to change the currency format
    Ctrl+Shift+^ Scientific notation number format, with two decimal places
    Ctrl+Shift+! Two decimal places, thousands separator, and a hyphen for negative values

See How to change number cells format easily for more details.

Note: If a cell displays a series of hashes such as #########, this usually means that the column width is not wide enough to display the value in the selected number format. To display the value correctly, change the column width (see How to change width of gridlines in Excel) or change the number format.


Change currency symbol

To change the currency symbol, do one of the following:

  • On the Home tab, in the Number group, click the Accounting Number Format button:

    From the Accounting Number Format dropdown list:

    • Choose the format you prefer:
      Accounting Number Format dropdown list in Excel 365
    • Click More Accounting Formats... to open the Format Cells dialog box.
  • Click Ctrl+1, then in the Format Cells dialog box, choose the appropriate currency symbol from the Symbol dropdown list:
Currency symbol in Format Cells dialog box Excel 365

See how to change the currency symbol used by default.


Two ways to apply the percentage format

There are two ways how Excel can convert the entered number with a percent sign, entered in a cell formatted as a percentage, to a percentage format:

  • Without any changes (by default). For example, enter 12 to display 12.00% or enter .12 to display 0.12%.
  • Using decimal format. For example, enter 12 to display 1200%, or enter .12 to display 12.00%.

To change the option for percentage format, do the following:

   1.   On the File tab, click the Options button:

Excel 365 Options

   2.   On the Advanced tab, under Editing options, select or deselect the Enable automatic percent entry checkbox:

Enable automatic percent entry in Excel 365
  • If the Enable automatic percent entry checkbox is selected (used by default), the entered normal value will be displayed as a percent.
  • If the Enable automatic percent entry check box isn't selected, you need to enter the value as a decimal to be displayed as a percent.

Numeric limitations in Excel

Excel allows numbers up to 15 digits. For example, if you enter a large value such as 123,456,789,123,456,789 (18 digits), Excel will store it with only 15 digits. So, this 18-digit number it displays as 123,456,789,123,456,000.

To store large numbers, such as credit card numbers, which are usually 16 digits long, use the text format:

  • The easiest way is to format the cell as text. To format the selected cell or cells, do one of the following:
    • On the Home tab, in the Number group, open the Number Format dropdown list, then choose Text:
      Text in Number Format dropdown list in Excel 365
    • Click Ctrl+1 to open the Format Cells dialog box and choose Text from the Category list:
      Text in Format Cells dialog box Excel 365
  • Put an apostrophe (') before the number:
Text form numbers example in Excel 365

Both methods will force Excel to interpret the cell value as text instead of a number.

Here are some of the other numeric limits:

  • The largest positive number is 9.9E+307
  • The smallest negative number is -9.9E+307
  • The smallest positive number is 1E–307
  • The largest negative number is –1E–307

All these numbers have only 15 digits of precisions.

See also How to avoid misleading numbers.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more