How to Change a Cell’s Number Format in Excel 2010

By Greg Harvey

Excel 2010 provides a variety of number formats that you can apply to the values (numbers) you enter in a worksheet to make the data easier to interpret. These number formats include currency, accounting, percentage, date, time, fraction, and scientific, as well as a few special formats.

When you enter a value into a cell, Excel takes a guess at what type of number it is and how it should be formatted. How you enter values into an Excel 2010 worksheet determines the type of number format that Excel assigns to it. Here are some examples:

  • Currency: If you enter a financial value complete with the dollar sign and two decimal places, Excel assigns a Currency number format to the cell along with the entry.

  • Percentages: If you enter a value representing a percentage as a whole number followed by the percent sign without any decimal places, Excel assigns to the cell the Percentage number format.

  • Dates: If you enter a date (dates are values, too) that follows one of the built-in Excel number formats, such as 11/10/09 or 10-Nov-09, the program assigns a Date number format that follows the pattern of the date.

Even if you’re a really good typist and prefer to enter each value exactly as you want it to appear in the worksheet, you still use number formats to make the values that are calculated by formulas match the others you enter. Excel applies a General number format to all the values it calculates as well as any you enter that don’t follow one of the other Excel number formats. The General format drops all leading and trailing zeros from entries. This makes it very hard to line up numbers in a column on their decimal points. The only cure is to format the values with another number format.

Numbers with decimals don't align when you choose General formatting.

Numbers with decimals don’t align when you choose General formatting.

You can assign a number format to a group of values before or after you enter them. Formatting numbers after you enter them is often the most efficient way to go because it’s just a two-step procedure:

  1. Select the cell(s) containing the numbers you want to format.

  2. Select the desired number format using either of these methods:

    • Choose a format from the drop-down list in the Number group on the Home tab.

    • Click the Number dialog box launcher in the bottom-right corner of the Number group on the Home tab and select the desired format on the Number tab of the Format Cells dialog box.

      Apply a number format via the Number group on the Home tab or the Format Cells dialog box.

      Apply a number format via the Number group on the Home tab or the Format Cells dialog box.

Use the keystroke shortcut Ctrl+1 to open the Format Cells dialog box. Just keep in mind that the keyboard shortcut is pressing the Ctrl key plus the number 1 key, and not the function key F1.