Lesson 13: Formatting Numbers

/en/excelxp/text-and-cell-alignments/content/

Introduction

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

  • Format the display of numbers
  • Format the date
  • Format the time
  • Format the display of percentages

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.

/en/excelxp/applying-font-color-and-borders-to-cells/content/