Formatting Numbers

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

Introduction

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

  • Format numbers

Formatting numbers

By default, values are formatted as general numbers. This means Excel doesn't display zeroes that don't affect the actual numeric value of the number. For example, Excel will display 250 but not $250.00 unless you format the number.

Values can be formatted as currency, percentages, fractions, dates, and many other formats. Remember, when you format a number, you're not changing the numeric value.

You can format selected cells using the Formatting toolbar, the Format dialog box, or the shortcut menu.

To format cells using the Formatting toolbar:

  • Select the cell or range you want to format.
  • On the Formatting toolbar, click one of the buttons explained in the table below. Your number is now formatted.
  • Click anywhere outside the cell to turn off the formatting.
Currency ButtonCurrencyDisplays and aligns dollar signs, comma separators, and decimal points. Ex: 89.5 as $89.50.
Comma ButtonCommaSame as currency, but without dollar signs. Ex: 120.5 as 120,50.
Percentage ButtonPercentageDisplays numbers as percentages. Ex: 0.82 as 82%.
Increase and Decrease ButtonIncrease DecimalEx: .12 as .120.
Decrease and Decrease ButtonDecrease DecimalEx: 0.5670 as 0.567.

Important Point Formatting only affects the look of a cell, not the cell's contents. To view the contents of a cell, click the cell and look at the Formula bar.

Important PointExcel has several number formats to choose from in the Format Cells dialog box, which are discussed on the next page.

To format using the Format Cells dialog box:

  • Select the cells you want to format.
  • Choose FormatChoose lesson actionCells from the menu bar. The Format Cells dialog box opens. Or right-click the cells to open the shortcut menu and choose Format Cells.
  • The Format Cells dialog box has several categories to choose from, each one on a separate tab: Number, Alignment, Font, Border, Patterns, and Protection.
  • Click the Number tab if it is not already displayed.
  • Click the different categories to preview results.
  • Set other options if you want (font, borders, etc).
  • Click OK.

Format Cells Dialog Box

Important Point If you hover your mouse pointer over the dollar sign button on the Formatting toolbar, the tool tip reads currency style. When you apply this button's format, Excel applies the accountant format (dashes for zeroes and dollar signs lined up next to the numbers).

Challenge!

  • Open Excel.
  • Experiment with and type a few numbers into cells. Select the cells.
  • Using the Formatting toolbar, convert cells to currency, commas, and percentages.
  • Open the Format Cells dialog box. Click the Numbers tab.
  • Preview the various categories.
  • Click OK.