Excel's 12 Number Format Options and How They Affect Your Data

Quick Links

• Things to Know Before You Start

• General

• Number

• Currency

• Accounting

• Date

• Time

• Percentage

• Fraction

• Scientific

• Text

• Special

• Custom

Using Excel's number format tool means you can ensure your spreadsheet both looks the part and handles your data exactly how you want it to. In this article, I'll explain what each number format is, how to use it, and how it affects your data.

Things to Know Before You Start

Each cell has its own number format, which you can see by selecting a cell and seeing the Number group in the Home tab on the ribbon.

For some data types, such as a date or a percentage, Excel will change a cell's number format automatically as soon as you type it into a cell. Otherwise, there are various ways to change a number format manually. First, expand the drop-down menu in the Number group of the Home tab, and select the option that best suits the data in the active cell.

Another way to change a number format is by clicking one of the icon shortcuts in the same group—Accounting, Percentage, or a number with a comma to separate the thousands.

Alternatively, for more options, you can launch the Format Cells dialog box by clicking the icon in the bottom right corner of the Number group or pressing Ctrl 1.

Providing there is already some data in the active cell, the Number Format dialog box shows you a preview of how each number format will affect your data if selected.

General

Every cell in a new workbook has the General number format by default. Since Excel's primary function is to handle numerical data, the General number format displays numbers exactly as they are typed without any formatting.

One exception to this rule is if you type a decimalized number that doesn't fit into the cell's width. In this case, Excel's General number format will round the number to the nearest value the cell's width will allow.

The General number format also converts lengthy numbers to scientific—or exponential—values (more on this later).

Number

Even though the General number format supports numerical values in Excel, the Number number format gives you more flexibility with how numbers are displayed.

By selecting the relevant cell or cells and pressing Ctrl 1, you can define:

1. The number of decimal places (the default is two),
2. Whether you want to use a comma in numbers that are 1,000 or greater (deactivated by default), and
3. How negative values are displayed.

Currency

There are two ways to format monetary values in Excel, the first being the Currency number format. Choose this number format instead of the Accounting number format if you want to choose how the value is aligned in the cell and how negative values are displayed.

More specifically, the Currency number format lets you:

1. Choose the number of decimal places (you can choose as many as you like, but zero or two decimal places tend to be the preferred options for monetary values),
2. Decide which currency symbol to display, and
3. Choose how negative values appear in your spreadsheet.

Accounting

The second way to format financial values is by using the Accounting number format. This is preferable for large and formal accounting spreadsheets, as it aligns the currency symbol and decimal place in each cell, making reading and comparing values in nearby cells more straightforward.

Similar to when you use the Currency number format, you can choose how many decimal places to display and which currency symbol to use.

However, unlike the Currency number format, the Accounting number format doesn't give you alternative ways to display negative values.

Remember, you can quickly choose the Accounting number format by clicking the dollar bill symbol in the Number group of the Home tab.

Date

Since different countries have different ways to display dates, the key to using the Date number format is first choosing the correct locale. The region should default to your system's settings, but it's always a good starting point to make sure it aligns with your expectations.

To do this, select the cell or cells where you want the date to go, and press Ctrl 1. Then, click "Date" in the category menu on the left, and click the "Locale" drop-down menu to choose the correct region. You'll then see the different Date number format options from which to choose.

All Date number formats containing an asterisk (*) change automatically if the spreadsheet is shared with someone in a different region. Those without an asterisk don't have this adaptability.

Time

As with the Date number format, make sure you select the correct locale in the Number tab of the Format Cells dialog box (Ctrl 1). Then, choose a time type. All times displayed with asterisks (*) change according to regional operating time settings.

Percentage

Excel's Percentage number format multiplies the cell value by 100 and displays the % symbol. So, for example, if you apply the Percentage number format to a cell containing 0.54, the cell will display 54%.

By default, Excel displays percentages as integers (whole numbers with no decimal places), meaning if you type 0.549, the cell will display 55%.

You can increase or decrease the number of decimal places either by clicking the decimalization icons in the Number group of the Home tab on the ribbon (see number 1 in the screenshot below) or pressing Ctrl 1 and using the spin button next to the Decimal Places option (see number 2 in the screenshot below).

Remember, you can quickly choose the Percentage number format by clicking the "%" symbol in the Number group of the Home tab.

Fraction

As its name suggests, the Fraction number format displays a number as a fraction. In this example, 0.75 converts to ¾.

When you press Ctrl 1 to open the Format Cells dialog box, the various options in the Type list let you choose the number of digits in the fraction's numerator and denominator, and you can also round the fraction to the nearest half, quarter, eighth, or sixteenth if you so wish.

Scientific

The Scientific number format displays a number in exponential notation. It is typically used in formal scientific or mathematical papers, as not everyone will understand what the numbers and letters represent.

In the example below, 987,654,321 is displayed as 9.88E 08, where

• 9.88 is the base number (rounded to two decimal places by default),
• E indicates that the number displayed is an exponent, and
• 08 means that the base number needs to be multiplied by 10 eight times.

To change the number of decimal places, press Ctrl 1, and use the spin button where you see the words "Decimal Places."

Text

The Text number format displays exactly what you type into a cell, even if it's a number or a formula.

This number format is great when you want to use and display extra long numbers (like credit card or mobile phone numbers), because it stops Excel from rounding them or turning them into exponents.

If you later decide you want to use the values in Text-formatted cells in calculations, you'll need to convert them to Number or General formats by selecting the relevant cells, pressing Ctrl 1, and choosing the appropriate number format.

Special

The Special number format lets you add unique numbers, like phone numbers, social security numbers, and zip codes, with pre-defined structures.

The use of this number format is limited to people entering data for a select group of nations. For example, after pressing Ctrl 1 and selecting "Special" in the left-hand menu, select "English (United States)" in the "Locale" drop-down menu to see various number formats available for that country.

On the other hand, if you select "English (United Kingdom)," you'll see that Excel doesn't offer any Special number formats for this nation.

If a Special number format for your region is not supported, use the Custom number format instead (see below).

Custom

The Custom number format offers a list of codes that you can either use as they are or adjust to create a customized number format.

For example, I want to add the letter H to a list of numbers in a spreadsheet. To do this, I will select all the cells containing the numbers I want to affect, and press Ctrl 1 to open the Format Cells dialog box. Then, I will click "Custom" in the left-hand menu.

Now, I will select the "0" Custom number type, and type "H" after the 0, before pressing "OK."

Now, all my numbers are followed by the letter H.

---

If a cell displays a series of hashtags (#####) rather than the value you typed, this is likely either because the column width is too narrow to fully display the contents, or the number formatting you have applied does not match the value you have typed. To rectify this issue, first try widening the column, and if this doesn't work, change the cell's number format to match the type of data it contains.

The above is the detailed content of Excel's 12 Number Format Options and How They Affect Your Data. For more information, please follow other related articles on the PHP Chinese website!