Number Format Options

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

Number Format Options

Introduction

Although you can do most of cells configurations using the Ribbon, Microsoft Excel provides the Format Cells dialog box. This dialog box presents more options and more precision.

To display the Format Cells dialog box:

  • On the Ribbon, click Home. In the Number section, click the more options button:

Number

  • Right-click the cell or group of cells whose format you want to change and click Format Cells...
  • Press Ctrl + 1 as a shortcut

The Number Property Page

The object used to manage the value of a cell or the values of a group of cells on the Format Cells dialog box is the Number property page:

Format Cells

The left section of the Format Cells dialog box displays the Category list box. This allows you to select the type of value that the cell or group of cells contains. After selecting a category, the right section of the property page changes, depending on the category that was selected. We are going to see various options available.

 Practical Learning: Introducing the Format Cells

  1. Select cells G11:G17. We will now use the Format Cells dialog box
  2. In the Number section of the Ribbon, click the Format Cells: Number button Format Cells: Number
  3. On the Format Cells dialog box, make sure the Number property page is active.
    In the Category list box, click Currency. Make sure that the Decimal Places spin button displays 2
     
    Format Cells
  4. Click OK. Although all cells in column G display currency values, notice some differences with the way the first category (G3:G10) displays them as compared to the rest
  5. Select cells F11:F17
  6. Right-click in the selected cells and choose Format Cells...
  7. From the Category, click Number. Decrease the Decimal Places spin button to 0. Click the Use 1000 Separator (,) check box:
     
    Format Cells
  8. Click OK
  9. Again, notice how numbers on column H are displayed.
    Select cells H11:H17
  10. Press Ctrl + 1 which is a shortcut to call the Format Cells dialog box
  11. For this section, we are going to exclusively use the keyboard.
    If the Number proper page is not selected, press Ctrl + Tab a few times to switch from different property pages until the Number tab receives focus.
    Press Tab once to give focus to the Category list box. Press the down arrow key a few times until Percentage is selected. Press Tab to give focus to the Decimal Places spin button; then, either using the up and down arrow keys or just typing, set the spin button to 2 (the Decimal Places spin button should already be set to 2)
  12. Press Enter
  13. Select cells I9:I13.
  14. The dates can also be displayed in various formats. You can make your selection from the Format Cells dialog box in the Date category.
    Press Alt, press o, press E.
  15. In the Category list, make sure Date is selected, in the type list, using either the keyboard or the mouse, select March 14, 2001. Click OK
     
    Cells Formatted
  16. Save the workbook

Special and Custom Formats

Although Microsoft Excel can recognize number formats of various kinds, sometimes none of the preset formats will suit a particular need you have for a certain cell or group of cells. You can use either one of the Special Formats or create your own.

The Special formats can be accessed from the Format Cells dialog box. These formats allow you to specify a cell or column for US ZIP Codes (5 or 5+4 number digits), phone numbers, or Social Security Numbers.

To create your own custom format, from the Format Cells dialog box, click the Custom category, select one of the existing formats, then proceed to create your own by adding appropriate or subtracting undesired characters.

If you get to using a worksheet that was prepared by someone else (or you) and find out that the format used by a cell or some cells is not appropriate, you can change it using the Format Cells dialog. Right-click the appropriate cell (s) or column (s) and choose Format Cells. In the Category list, select one and in the right list, select the format you like.

 Practical Learning: Using Special and Custom Formats

  1. To start another workbook, click the Office Button and click New
  2. In the New Workbook dialog box, click Create
  3. In cell H1, type Age Structure
  4. In cell J1, type Life Expectancy at Birth
  5. In cell A2, type Country
  6. In cell B2, type Area
  7. In cell C2, type Population
  8. In cell D2, type Government
  9. In cell E2, type Independence
  10. In cell F2, type US Contact
  11. In cell G2, type Teens
  12. In cell H2, type Adults
  13. In cell I2, type Seniors
  14. In cell J2, type Total
  15. In cell K2, type Male
  16. In cell L2, type Female
  17. Save the workbook as World Statistics1
  18. Select columns B and C. Right-click column B (on the selection) and click Format Cells... In the Format Cells dialog box, click the Number property page. In the Category list, click Number. Set the Decimal Places to 0. Click the Use 1000 Separator (,) check box to check it. Click OK
  19. Right-click column F and click Format Cells... From the Number property page, click Special. In the Type list, click Phone Number
     
  20. Click OK.
  21. Select columns G, H, and I. 
  22. Press Ctrl + 1. From the Number property page, in the Category list, click Percentage. Keep the Decimal Places to 2 and click OK
  23. Do the same for Columns J, K, and L
  24. Complete the World Statistics worksheet
  25. To save the workbook, press Ctrl + S.
  26. Open the Grier Summer Camp5 workbook
  27. Click the Request For Time Off tab if necessary.
    Click on the right side of Employee # and type 62481.
    Notice that the cell is configured to display an employee number with a period (.) after the first 2 number digits.
  28. To clear a format that is set on some cells, right-click the cell on the right side of Employee # and click Format Cells...
  29. In the Number property page of the Format Cells dialog box, in the Category list box of the Number tab, click Custom
  30. Under Type, delete General and type ##-###
     
    Format Cells
  31. Click OK
  32. Click the right side of Employee #, type 62481, and press Enter. Notice that it now displays 62-481
  33. Save the workbook

 

Automatic Aesthetic Cell Formatting 

A good worksheet is not simply determined by its functionality, its look plays a great deal and reflects your tastes in fonts, styles, and design. Before we experiment with its design capabilities, we will first find out how Microsoft Excel can help with choosing fonts, font styles, sizes, and colors.

Cell formatting allows you to control how text displays in your cells, how much room various columns and rows need in order to display their content. Microsoft Excel ships with sample tables with each configured to suit a specific scenario. Whenever possible, or you are simply in a hurry, use one of these readily available samples; one of the sample AutoFormat tables can make your table or part of your worksheet look professional.

To automatically apply a design on a cell or a group of cells, first select the cells you want to format. Then on the Ribbon, click Home. In the Styles section, if one of the 6 pre-selected formats suits your needs, you can click it

If none of those designs suits you, click the More buttons to display many more options:

As a technique you can use, you can select a group of cells on the same row, apply a style, then select another group of cells on another row, and apply a different style.

 Practical Learning: Using AutoFormat

  1. To create a new workbook, press Ctrl + N
  2. In Sheet1, click cell B5 and type Robert
  3. Click cell C4 and type January
  4. Complete the worksheet as follows:
     
       January February March April May June
    Robert 2600 3450 2860 3840 3250 3480
    Lucy 3580 3420 3550 2860 3640 3520
    Annette 460 1240 1850 2380 2650 2870
    Josiane 840 650 520 720 550 480
    Salif 620 580 610 560 820 520
  5. Save the workbook as Alexandria Furniture1
  6. Press Ctrl + A to select all cells
  7. Press Ctrl + C to copy
  8. Click Sheet2 and press Ctrl + V to paste the selection
  9. Click Sheet 3 and press Ctrl + V to paste again
  10. Click Sheet1 and press Esc
  11. In Sheet2, select cells B4:H4
  12. On the Ribbon, click Home if necessary.
    In the Style section, click the More button and click Accent2
  13. Select cells B5:H9
  14. In the Styles section of the Ribbon, click the More button and click Accent6
  15. Select cells B6:H6
  16. In the Styles section of the Ribbon, click the More button and click 20% Accent6
  17. Select cells B8:H8
  18. In the Styles section of the Ribbon, click the More button and click 20% Accent6
  19. Press Ctrl + Home to see the result
  20. Select cells C5:H9
  21. In the Number section of the Ribbon, click the Currency Style button Currency. Now the numbers display appropriately as sales values
  22. Save the workbook