Introduction to Cells Formatting

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.

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.

1. Start Microsoft Excel and open the Allentown Car Sales1.xlsx workbook
2. In Sheet1, select Cells F3:F10
3. On the Ribbon, click Home. In the Number section, 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
4. Press Ctrl + Home to see the result
5. 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
6. To remove the decimal values, in the Number section of the Ribbon, click the Decrease Decimal button . That removes one decimal value. Click the Decrease Decimal button again
7. Click cell F1 to see the result
8. Select cells G3:G10
9. In the Number section of the Ribbon, click the Currency Style button . The numbers in column G are now treated as money values and a $ sign has been appended to them
10. Select cells H3:H10
11. In the Number section of the Ribbon, click the Percent Style button . Now the numbers in column H are treated as percentage values
12. 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, in the Number section of the Ribbon, click the Increase Decimal button twice. Notice that the numbers have changed and are now more precise:
     
    
13. Save the workbook

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:

• 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 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.

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
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
    
   
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
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
     
    
16. Save the workbook

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.

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 ##-###
     
    
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

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.

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 . Now the numbers display appropriately as sales values
22. Save the workbook

When reviewing cells, we found out that a cell doesn't have dimensions of its own. Its width is imposed by its parent column and its height is set on its parent row. All of the cells we have used so far were considered individually. Microsoft Excel allows you to combine various cells in a group. This is referred to as merging cells.

To merge cells, select them and:

• On the Ribbon, click Home. In Alignment section, click the Merge & Center button
• On the Ribbon, click Home. In Alignment section, click the button on the right side of Merge & Center and select from the list
   
  
• Right-click the selected cells and click Format Cells. In the Alignment property page, click the Merge Cells check box and click OK

1. Open the DAWN Report2.xlsx workbook and click Sheet2 to make it active
2. Select cells A3:D3
3. On the Ribbon, click the Merge & Center button
4. Select cells F3:I3
5. On the Ribbon, click the Merge & Center button
6. Press Ctrl + Home
7. To save the workbook, press Ctrl + S

We have already seen how Microsoft Excel differentiates data you enter into cells. Sometimes its default configurations will not suit your particular scenario, you should be able to control how text is aligned in cells.

Since a cell is really a rectangular box, you can completely control how text is displayed inside of it: left, center, right, top, middle, or bottom. As we move on, we will see various situations of aligning cells content.

1. Open the Cherry Pumpkin Day Care1 workbook
2. To control the alignment of one cell, click cell F4 to make it active
3. On the Ribbon, click the Home tab if necessary.
   In the Alignment section, click the Align Text Right button
4. To control the alignment of a group of cells, select cells C4:C15
5. On the Ribbon, in the Alignment section, click the Center button
6. Press Ctrl + Home and press Ctrl + S to save the workbook
7. Access the DAWN Report2 workbook from the taskbar
8. In Sheet1, randomly select cells C6, D5, D6, H6, I5, and I6 (press and hold Ctrl while you are clicking each cell)
9. To control the alignment of a group of randomly selected cell, on the Ribbon, click the Center button
10. Select cells A7:A16
11. In the Alignment section of the Ribbon, click the Align Text Right button
12. Select cell F7:F16 and, in the Alignment section of the Ribbon, click the Align Text Right button
13. Save the workbook

In the previous section, we used the Center button to center the content of a cell with regards to the width of the cell. In some circumstances, you may not want to center text but you would not like to keep it left or right aligned. Indentation consists of "pushing" text to the left or the right without centering it.

To indent the contents of a cell or of a group of cells, after making the selecting, on the Ribbon, click Home. In the Alignment section:

• Click the Increase Indent button to "push" the contents of a cell or a group of cells to the right
• Click the Decrease Indent button to "push" the contents of a cell or a group of cells to the left

1. Open the Grier Summer Camp2.xlsx workbook and, if necessary, click Sheet1
2. Click cell C5 to make it active
3. On the Ribbon, click the Home tab if necessary. In the Alignment section, click the Increase Indent button
4. Click cell D6 and repeat the previous action

Besides using the alignment buttons on the Ribbon, to be more precise or to perform various actions in one step, you can use the Alignment property page of the Format Cells property sheet. To access the Alignment property page:

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

• Right a cell or the selected cells and click Format Cells. In the Format Cells dialog box, click Alignment

To provide the same options as the Ribbon, the Alignment property page is equipped with the Horizontal combo box. Besides the left, center, and right alignments, this combo box goes further and allows text to be justified. This can be useful especially if the text is significantly long. If you select to indent text, you can use the Indent spin button to specify the number of units to indent by.

The Vertical combo box provides options not available on the Ribbon. It allows you to align the contents of a cell towards the top, the middle or the bottom area of a cell.

The Text Control section provides three options: Wrap Text, Shrink To Fit, and Marge Cells.

The Orientation section allows you to "bend" text by a fix angle. There are two main ways you can set an angle. If you drag the small red diamond, you can use it to specify the desired angle. You can also click one of the arrows of the Degrees spin button.

1. Sheet1 of the Grier Summer Camp2.xlsx workbook should still be selected
   Select cells B9:C10
2. Right-click the selection and click Format Cells...
3. In the Format Cells dialog box, click the Alignment property page if necessary.
   In the Text Control section, click the Merge Cells check box, and click OK
4. Select cells E9:H9 and press F4 to repeat the previous action
5. Select cells D8:H8
6. Right-click the selection and click Format Cells...
7. In the Alignment tab of the Format Cells dialog box, in the Horizontal combo box, select Center
8. In the Vertical combo box, select Center
9. In the Text Control section, click the Merge Cells check box
    
   
10. Click OK
11. Select cells A11:A26
12. Press Ctrl + 1 to call the Format Cells dialog box
13. In the Horizontal combo box, select Right
14. In the Text Control section, click the Merge Cells check box
15. In the Orientation section, click and hold the mouse on the small red diamond. Then drag it up until the spin button in the same section displays 90
     
    
16. Click OK
17. Type Time Period
18. Save the workbook

The alignment we have used so far is applied to the contents of a cell or of a group of selected cells. As we have mentioned already, a cell appears and behaves like a rectangular box. As such, it has borders and a background. Microsoft Excel provides a default appearance for a cell with regards to its background. For example, it surrounds the cell with a gray border and a white background. You can keep these defaults or you can change them as you see fit.

To control the borders of a cell or a group of cells, on the Ribbon, click Home. In the Font section, click the arrow of the Borders button and select one of the options:

1. The Grier Summer Camp2.xlsx workbook should still be opened with the Sheet1 selected
   Select cells B2:J2
2. On the Ribbon, click Home if necessary.
   In the Font section, click the arrow of the Borders button
3. Click the Top And Double Bottom Border option
4. Select cells F6 and G6
5. On the Ribbon, click the arrow of the Borders button and click the Bottom Border option 
6. Randomly select cells F5:G6 and I29
7. On the Ribbon, click the arrow of the Borders button and click the Bottom Border option  
8. Select cells I6:J6
9. On the Ribbon, click the arrow of the Borders button and click the Top And Bottom Border option
10. Select cells A9:A27
11. On the Ribbon, click the arrow of the Borders button and click the Right Border option
12. To see the result, on the Ribbon, click View
13. In the Show/Hide section, remove the check mark on the Gridlines check box
14. Save the workbook
15. Open the DAWN Report2 workbook
16. Click cell A3 to give it focus
17. On the Ribbon, click Home. Click the arrow of the Borders button. Click the Bottom Border
18. Click cell F3
19. Click the arrow of the Borders button. Click the Bottom Border
20. Select cells A6:I6
21. Since the bottom border is already selected on the Borders button, on the Ribbon, just click the Borders button
22. Save the workbook

To better control the borders of a cell or a group of cells, you use the Border property page of the Format Cells dialog box. To access it:

• On the Ribbon, click Home. In the Font section, the Alignment section, or the Number section, click the more options button
• Right-click the cell or the group of selected cells and click Format Cells...

Any of these actions would display the Format Cells dialog box where you would click the Border tab.

1. The DAWN Report2.xlsx workbook should still be opened.
   Select cells A17:I17
2. Right-click on the selected cells and click Format Cells...
3. From the Format Cells dialog box, click the Border property page
4. From the Line section, in the Style list box, click the 5th line in the right section of the list
5. Click the Color combo box and select Dark Red
6. From the Presets section, click the bottom border button
    
   
7. Click OK.
8. Select cells A2:I2
9. In the Font section of the Home tab of the Ribbon, click the more options button
10. In the Format Cells dialog box, click the Border property page.
    From the Line section, in the Style list box, click the 6th line on the right section of the list
11. Click the Color combo box and select Blue, Accent1, Lighter 40%
12. From the Border section, click the top border button
13. From the Border section, click the left border button
14. Click the Color combo box and select Dark Blue, Text 2, Darker 25%
15. From the Border section, click the bottom border
16. From the Border section, click the right border
     
    
17. Save the workbook