Understanding Number Formats in Excel

Numbers in Excel can be formatted in different ways to represent different types of information. For instance, there are currencies, percentages, decimals, dates, and phone numbers, and we can usually recognize these numbers right away based on how they are formatted or displayed. Excel acknowledges these and other formats by allowing you to change number formats.

What is an Excel number format?

Excel has built-in number formats to control how cells with numeric data appear in Excel. Numeric data can mean dates, time, money, or anything else that looks like a number. The most important thing to understand about number formats is that they only affect how the number looks - they don't change the actual value of the number that Excel uses in calculations.

Where to find and change number formats

To find out what format has been applied to a number, click on the cell in question and go to the Home tab on the Excel ribbon. Within the Number command group is a Number Format dropdown field, which displays the number format of the active cell.

Clicking on the dropdown menu will reveal additional built-in number formats. 

You can click More Number Formats… or use the Ctrl+1 shortcut to open the Format Cells dialog box (or Command+1 using a Mac). The Number tab of this dialog box gives a complete list of number formats, organized by category.

12 Excel number formats

Let’s explore some of the number formats available in Excel.

1. General format

Numbers entered in Excel are given the General format by default. The General number format has the following features:

• Integers (whole numbers) with 12 or more digits are represented in Excel scientific notation (x10).

• Large numbers with decimals are rounded to fit the column width.

2. Number format

Yes, there’s a number format called “Number”. With this format, you can specify the number of decimal places you would like to be displayed in a cell. Note that if a cell carries the General format and the “Increase Decimal” or “Decrease Decimal” options are used, the cell format automatically changes to Number.

3. Currency format

The currency format is used for monetary values in general and will display the default currency based on your regional settings. Other currency symbols are available from the Format Cells dialog box, using the Symbol dropdown menu. You can also use this window to change the number of decimal places you would like to be displayed.

4. Accounting format

The Accounting number format in Excel is very similar to the Currency format, with the difference being that in Accounting, currency symbols are aligned to the left, whereas with Currency, values are aligned to the right.

With the Currency format, negative values can be formatted to display with a minus sign, in red color, with parentheses, or in red color with parentheses. The currency format is therefore more flexible for formatting, but for alignment purposes, the Accounting format may be preferable.

5. Date format

The Date format in Excel displays numbers as date values, with the assumption that the number 1 represents January 1, 1900. The number 400 would represent 400 days since that date (which would be February 3, 1901), and so on. The date is displayed based on the regional settings of your computer. You can choose from a large variety of date formats in the Format Cells dialog box, and you can also change the location format by using the Locale (location): dropdown menu.

6. Time format

The time format displays numbers as time values, with the assumption that the number 1 represents 12:00 AM on January 1, 1900. The number 1.5 would represent half of a day, or 12:00 PM on January 1, 1900, and the number 2 would represent January 2, 1900, etc. You can choose from a large variety of time formats in the Format Cells dialog box.

7. Percentage format

The Percentage format multiplies the cell value by 100 and displays the result with a percent (%) symbol.

 

8. Fraction format

This format displays numbers as a fraction. You can go to More Number Formats… in the Format Cells dialog box to specify how you want the fraction to be displayed.

9. Scientific format

The Scientific format displays numbers with an E+n notation. This means the number is an exponent (hence, E) raised to the power of the number that follows. For example, 789 in Scientific format is 7.89E+02, that is, 7.89 times 10 raised to the 2nd power.

10. Text format

Text format treats values as text. What this means is that even if you type numbers, Excel treats them as text. This is useful when you want to put 0 in Excel in front of numbers. For other number formats, leading zeros are ignored and not displayed. Cells formatted as text are aligned to the left by default, and are treated as “abc” text for the purposes of Excel formulas.

11. Special format

The Special format is accessible by selecting More Number Formats... from the Number command dropdown menu. Formats such as zip codes, phone numbers, and social security numbers are available if the United States location is selected from the Format Cells dialog box. 

12. Custom format

The Custom number format can be accessed by selecting More Number Formats... from the Number command dropdown menu. This format allows you to customize number format codes. Once you have created a custom format, it becomes available from the Format Cells dialog box using the Custom category.

Learn more about Excel Custom Number Formats.

Shortcuts for each number format

Below are the keyboard shortcuts for common Excel number formats, using the numbers above the letters on a Windows or Mac keyboard.

Summary

Probably the most important piece of advice as far as Excel number formats is concerned is to not panic if a number format doesn’t look the way you expect. Start your troubleshooting by checking on the number format and adjusting it if needed. Remember that the value hasn’t changed – just Excel’s way of representing it.

Level up your Excel skills

Become a certified Excel ninja with GoSkills bite-sized courses

Start free trial