Excel Tutorial: Changing Number Formats in Excel

Changing number formats in Excel allows numbers to display as currency, percentage, or other formats. You can format numeric entries in Excel using a variety of predefined formats. You can also create and apply custom numeric formats for values such as phone numbers and zip codes. In this first lesson, you will learn to format a range of values using a variety of number formats.

Applying a number format in Excel

Follow these steps to apply a number format in Excel:

1. Launch Excel and open the file named excel03 from the Excel03lessons folder.
2. Select range B9:B20.
3. In the Number section of the Home tab, click the arrow to display the Format drop-down menu.
   
4. First select the range of cells and then assign a format to your values.
5. Choose Number.
   Excel then adds a decimal point and two digits (two decimal places) after each value in the range.

Changing the number of decimal places in Excel

By default, Excel rounds values with the Number format to two decimal places. You can increase or decrease the decimal place value accordingly. The following steps to change the number of decimal places in Excel.

1. Select range D24:D25. Notice that all numbers in this range have four decimal places.
2. In the Number section of the Home tab, click the Decrease Decimal button three times.
3. Excel rounds down the value and displays the result with two decimal places instead of the original four. The value in cell D25 now reads 7782.03.
4. Perform steps 1–3 for ranges H24:H25 and range L24:L25.

Adding the thousands separator to numbers in Excel

Excel automatically adds the thousands separator when the Currency or Accounting format is applied. The Comma Style format can be used when you want the thousands separator but not a currency symbol. Follow these steps to add the thousands separator to numbers in Excel:

1. Select range D9:D20.
2. Press and hold the Ctrl key and select ranges H9:H20 and L9:L20.
3. In the Number section of the Home tab, click the Comma Style button. Excel adds the thousands separator to the values and adds two decimal places.
4. Click the Decrease Decimal button on the Home tab twice to remove the decimal place setting.
   
5. Click the Decrease Decimal button to remove the two decimal spaces.

Assigning a currency symbol in Excel

Follow these steps to assign a currency sumbol in Excel:

1. Select range D21:D25.
2. Press and hold the Ctrl key and select ranges H21:H25 and L21:L25.
3. From the Home tab, choose Number Format.
4. From the Number Format list, choose Currency to apply this style to the selected cells.
   
5. Assign the Currency format to a range of cells.

Assigning the accounting format to numbers in Excel

These steps show how to assign the accounting format in Excel:

1. Select range D21:D25.
2. Press and hold the Ctrl key and select ranges H21:H25 and L21:L25.
3. From the Home tab, choose Number Format.
4. From the Number Format list, choose Accounting. Notice how the Accounting format looks slightly different than the Currency format but the dollar signs, commas and decimals remain
   
5. Values assigned the Accounting format look slightly different than the Currency format.