Formatting Numbers

Formatting Excel XP numbers can improve how your spreadsheet looks. Use the number function in Excel XP to also better organize content.

Introduction

By the end of this lesson, you should be able to:

Formatting numbers in the Format Cells dialog box

Numbers in Excel can assume many different formats: date, time, percentage, and decimals.

To format the appearance of numbers in a cell:

  • Select a cell or range of cells.
  • Choose FormatCells from the menu bar.

    Format and Cells Menu Selections

    (You could also right-click and choose Format Cells from the shortcut menu.)
  • The Format Cells dialog box opens.
  • Click the Number tab.

    The Number Tab on the Format Cells Dialog Box
  • Click Number in the Category drop-down list.

    The Number Tab on the Format Cells Dialog Box
  • Use the Decimal places scroll bar to select the number of decimal places (2 would display 13.50, 3 would display 13.500, and so on).
  • Click the Use 1000 Separator box if you want commas (1,000) inserted in the number.
  • Use the Negative numbers drop-down list to indicate how numbers less than zero are to be displayed.
  • Click OK.

Formatting dates in the Format Cells dialog box

The date can be formatted in many different ways in Excel XP. Here are a few ways it can appear:

October 6, 2003
10/06/03
10-Oct-03

To format the appearance of a date in a cell:

  • Select a cell or range of cells.
  • Choose FormatCells from the menu bar.
  • The Format Cells dialog box opens.
  • Click the Number tab.
  • Click Date in the Category drop-down list.

    The Number Tab on the Format Cells Dialog Box
  • Select the desired date format from the Type drop-down list.
  • Click OK.

Formatting time in the Format Cells dialog box

The time can be formatted in many different ways in Excel XP. Here are a few ways it can appear:

13:30
1:30 PM

To format the appearance of time in a cell:

  • Select the range of cells you want to format.
  • Choose FormatCells from the menu bar.
  • The Format Cells dialog box opens.
  • Click the Number tab.
  • Click Time in the Category drop-down list.

    The Number Tab on the Format Cells Dialog Box
  • Select the desired time format from the Type drop-down list.
  • Click OK.

Formatting percentages in the Format Cells dialog box

There may be times when you want to display certain numbers as a percentage. For example, what percentage of credit cards bills account for your total monthly expenses?

To express numbers as a percentage in a spreadsheet:

  • Select a cell or range of cells.

    Define Cell Range for Percentage Function
  • Choose FormatCells from the menu bar.
  • The Format Cells dialog box opens.
  • Click the Number tab.
  • Click Percentage in the Category drop-down list.

    The Number Tab on the Format Cells Dialog Box
  • Define the Decimal Places that will appear to the right of each number.
  • Click OK.

    Percentage Applied to Cell Range

Challenge!

  • Type a number in cell B2 and format it to have four decimal places.
  • Type a date in cell B3 and format it to look like October 6, 2003.
  • Type a time in cell B4 and format it to look like 1:30:00 AM.
  • Type a large number in cell B5 and a smaller number in cell C5. In cell D5, define a formula to divide cell B5 by cell C5. Format the answer in cell D5 to be a percentage with one decimal place.