Home

Introduction to Cells Formatting
 

Introduction to Cells Formatting
 

Overview

From the previous lessons, you are already familiar with the way Microsoft Excel displays text (left aligned) and numbers (right aligned). In some situations, you will want to treat numbers as text.

To display a number as text, append a single quote (') to its left. For example, to display 908 as text, type '908 in the cell where you want to display 908. The single quote will not display and will not print, but the number, treated as text, will be left aligned.

Although Microsoft Excel displays all numbers right aligned, as a smart financial and business application, it can distinguish between different types of numbers. It can recognize a date, a currency, or a percentage values. But the computer wants you to specify the way numbers should be displayed, giving you the ability to decide what a particular number represents, not because the software cannot recognize a number, but because a value can represent different things to different people in different scenarios. For example 1.5 might represent a half teaspoon in one spreadsheet while the same 1.5 would represent somebody's age, another spreadsheet's percentage, or etc.

Microsoft Excel can recognize some numbers by default and would not need much configuration, if any. For example, if you type 12/05/1998 in a cell, Microsoft Excel will recognize it as a date and act accordingly.

Introduction to Numbers Formatting

When it comes to displaying items, Microsoft Excel uses various default configurations, ranging from the font used by your application to the actual display of numbers in cells. The computer's Regional Options or Regional Settings govern how dates, numbers, and time, etc get displayed on your computer.

Microsoft Excel recognizes numbers in various formats: accounting, scientific, fractions, and currency. As the software product can recognize a number, you still have the ability to display the number with a format that suits a particular scenario.

Before finding out how to display numbers in different situations, you should be familiar with the way the application treats your numbers. That's why once again we will review some of the things we have already learned.

Practical Learning: Exploring Cells Number Formats
 
  1. Start Microsoft Excel if necessary and open the Allentown Car Sales2 workbook
  2. In Sheet1, click cell B2 and type Item Code
  3. Click cell B3 and type '218
  4. Click cell C2 and type Make
  5. Click cell H1 and type Finance
  6. Press the down arrow key
  7. In cell H2, type Rate
  8. Complete the rest of the Allentown Car Sales2 Sheet1 as follows:
     
  9. To save the workbook, on the main menu, click File -> Save
  10. Click Sheet2
  11. Select cells F3:F10
  12. On the Formatting toolbar, click the Comma Style button Comma Style (if you don't see the Comma Style button, click the >> button on right border of the Formatting toolbar to display the other Formatting toolbar buttons, then click the Comma Style button). Now these thousand numbers display with a comma sign which makes it easier to read. Microsoft Excel also allows you to be more precise with these numbers, that's why it added two decimal values
  13. Press Ctrl + Home to see the result
  14. The values in the F column represent car mileage values; we don't need to display these numbers with decimal places
    Select cells F3:F10 again
  15. To remove the decimal values, on the Formatting toolbar, click the Decrease Decimal button . That removes one decimal value. Click the Decrease Decimal button again
  16. Click cell F1 to see the result
  17. Select cells G3:G10
  18. In the Formatting toolbar, click the Currency Style button . The numbers in column G are now treated as money values and a $ sign has been appended to them
  19. Select cells H3:H10. On the Formatting toolbar, click the Percent Style button . Now the numbers in column H are treated as percentage values
  20. Our percentage numbers need a little more precision; therefore, we will display them with at least two decimal places. While cells H3:H10 are still selected, on the Formatting toolbar, click the Increase Decimal button twice. Notice that the numbers have changed and are now more precise:
     
  21. Save the workbook

Introduction to the Format Cells
 

Overview

Although you can do most of cells configurations using the Formatting toolbar, Microsoft Excel provides the Format Cells dialog box. This dialog box presents more options and prec ision than the toolbar.

To display the Format Cells dialog box, you can click Format -> Cells on the main menu. You can also right-click the cell or group of cells whose format you want to change and click Format Cells. The shortcut to access the Format Cells dialog box is Ctrl + 1

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:

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. On the main menu, click Format -> Cells...
  3. On the Format Cells dialog box, click the Number property page. In the Category list box, click Currency. Make sure that the Decimal Places spin button displays 2
     
  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:
     
  8. Click OK. Again, notice how numbers on column F are displayed
  9. Select cells H11:H17
  10. Press Ctrl + 1 which is a shortcut for Format -> Cells
  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, 1998. Click OK
     
  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 New button on the Standard toolbar.
  2. In cell H1, type Age Structure
  3. In cell J1, type Life Expectancy at Birth
  4. In cell A2, type Country
  5. In cell B2, type Area
  6. In cell C2, type Population
  7. In cell D2, type Government
  8. In cell E2, type Independence
  9. In cell F2, type US Contact
  10. In cell G2, type Teens
  11. In cell H2, type Adults
  12. In cell I2, type Seniors
  13. In cell J2, type Total
  14. In cell K2, type Male
  15. In cell L2, type Female
  16. Save the workbook as World Statistics1 in your exercises folder
  17. 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.
  18. Right-click column F and click Format Cells... From the Number property sheet, click Special. In the Type list, click Phone Number
     
  19. Click OK.
  20. Select columns G, H, and I. 
  21. Press Ctrl + 1. From the Number property page, in the Category list, click Percentage. Set the Decimal Places to 0. Click OK.
  22. Complete the World Statistics worksheet.
  23. To save the workbook, press Ctrl + S.
  24. To open an existing workbook, press Ctrl + O.
  25. In the Open dialog box, double-click GSC Employees1. Notice that the cells in column B have been to configured to display employees numbers with a period (.) after the first 2 number digits.
  26. To clear a format that is set on some cells, right-click column B and click Format Cells...
  27. In the Format Cells dialog box, in the Category list box of the Number tab, click General and click OK. Now, the numbers display regularly.
  28. To use a special preset format, right-click column H and click Format Cells...
  29. From the Number property sheet, in the Category list, click Special. In the Type list, click Phone Number. Click OK.
  30. To use a custom format, we will change the way dates in column G display. Instead of 04/14/89, we prefer 14-Apr-89.
    Right-click column G, and click Format Cells...
  31. In the Category list, click Custom. In the Type list, click d-mmm-yy
     
  32. Click OK. Notice how dates display now.
  33. Now we will configure how employees numbers display. Instead of 38002, we will display 38-002. To achieve that, we will create our own format.
    To create a custom format, right-click column B and click Format Cells... 
  34. In the Category list, click Custom. In the Type list, click #,##0.
  35. In the text box under Type:, click and edit the content to display
    0#-##0
  36. Click OK. Notice how the employees numbers display now
     
  37. Save the workbook.

 

The Computer's Regional Settings

Many settings that control how your computer displays dates, time, and other numbers are configured in the system Registry and controlled by the computer itself. This makes sure that your computer is in tune with the language you use in your computer experience and certainly keeps you synchronized with your computer neighbors.

The computer's Regional Settings can be viewed and/or changed from the Control Panel by double-clicking the Regional Settings icon.

Practical Learning: Checking the Computer's Regional Settings
  1. From the Taskbar, click Start -> Settings -> Control Panel
  2. In Control Panel, double-click Regional Options or Regional Settings
  3. Click the General property page. Check the language that your computer is using
  4. Click the Number property sheet. Examine how the numbers are configured for your computer. Here is an example:
     
  5. Click the Currency property sheet. Check how the money value is set to display in your computer.
  6. Click the Date property page. Check how dates are set to display in your computer:
     
  7. Click the Time property sheet. Review how the numbers are set to display in your computer.
  8. When you have finished examining the property sheets, click Cancel (unless you want to save or apply the changes you might have made, in which case you would click OK).

 

Text And Cells Formatting: AutoFormat

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 table 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 use the AutoFormat, first select the cells you want to format; then on the main menu, click Format -> AutoFormat... From the AutoFormat dialog box, select the style you want and click OK. You can change your style any time and many times until you are satisfied.

Practical Learning: Using AutoFormat
  1. From the Taskbar, click Microsoft Excel and start a new workbook
  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:H9.
  12. On the main menu, click Format -> AutoFormat...
  13. From the AutoFormat dialog box, click Classic 2
     
  14. Click OK.
  15. Press Ctrl + Home see the result.
  16. Click Sheet3
  17. Select cells B4:H9
  18. On the main menu again, click Format -> AutoFormat...
  19. From the AutoFormat dialog box, click List 1
  20. Click the Options... button
  21. Click the Width/Height check box to remove the check mark
     
  22. Click OK and press Ctrl + Home to see the result
  23. Select cells C5:H9.
  24. On the Formatting toolbar, click the Currency Style button . Now the numbers display appropriately as sales values.
  25. On the Formatting toolbar, click the Decrease Decimal button twice to remove the decimal places on the sales numbers.
  26. On the main menu, click Tools -> Options...
  27. From the Options dialog box, click the View property page.
  28. In the Window Options section, remove the check mark of the Gridlines check box
     
  29. Click OK.
  30. Save your workbook.
 
Previous Copyright © 2002-2007 FunctionX, Inc. Next