Data in the cells is divided into two types: text (alphanu- meric) and numeric data. Both numeric and text data can be aligned left, aligned right, or centered, and the font can be changed. The data is aligned left by selecting the desired cells, selecting the Home menu tab, and selecting the Align
Text Left button in the Alignment group. The Align Text Left
button is shown in Figure 3-30.
The data is centered by selecting the desired cells, se- lecting the Home menu tab, selecting the Center button in
the Alignment group. The Center button is shown in Figure 3-31.
The data is aligned right by selecting the desired cells, selecting the Home menu tab, and selecting the Align Text
Right button in the Alignment group. The Align Text Right
button is shown in Figure 3-32.
The font style for the data is changed by selecting the desired cells, selecting the Home menu tab, and then select- ing the desired font from the Font dropdown box in the Font group. The Font dropdown box is shown in Figure 3-33.
The font size for the data is changed by selecting the de- sired cells, selecting the Home menu tab, and then selecting the desired font size from the Font Size dropdown box in
FIGURE 3-28 Home: Format Button
FIGURE 3-29 Format Popup Menu
FIGURE 3-30 Home: Align Text Left Button
FIGURE 3-31 Home: Center Button
FIGURE 3-32 Home: Align Text Right Button
the Font group. The Font Size dropdown box is shown in Figure 3-34.
The font is bolded by selecting the desired cells and (1) selecting the Home menu tab, and clicking on the Bold but- ton in the Font group or (2) by typing Ctrl+B (typing the B key while holding down the Ctrl key). The Bold button is shown in Figure 3-35.
Alternatively, both the font style and font size can be changed and the font bolded from the Format Cells dialogue box, shown in Figure 3-36, which is accessed by selecting the desired cells and performing one of the following actions: (1) by typing Ctrl+1 (typing the 1 key while holding down the Ctrl key) and selecting the Font tab, (2) right clicking on one of the selected cells, selecting Format Cells... from the popup menu (shown in Figure 3-17), and selecting the Font tab, or (3) selecting the Home menu tab and selecting the
Format Cells: Font Dialogue Box Launcher (shown in
Figure 3-37) located at the lower-right corner of the Font group. From the Font: combo box, the user selects the font; from the Font style: combo box, the user can bold the font; and from the Size: combo box, the user selects the font size. The user must complete the changes by clicking on the OK box to close the Format Cells dialogue box.
FIGURE 3-34 Home: Font Size Dropdown Box
FIGURE 3-35 Home: Bold Button
FIGURE 3-36 Font Tab of the Format Cells Dialogue Box
FIGURE 3-37 Home: Format Cells: Font Dialogue Box Launcher
the thousands and hundreds. An easy way to do this is to se- lect the desired cells, select the Home menu tab, and select the Comma Style button in the Number group. The Comma Style button is shown in Figure 3-41. The Comma Style but- ton will format the data to an accounting style with two dec- imal places shown, negative numbers will be placed in parentheses, and zeros will be shown as a dash.
Numbers can be expressed as a percentage by selecting the desired cells, selecting the Home menu tab, and selecting the Percentage Style button in the Number group. The Percentage Style button is shown in Figure 3-42.
The number of decimal places is increased by selecting the desired cells, selecting the Home menu tab, and selecting the Increase Decimal button in the Number group. The Increase Decimal button is shown in Figure 3-43. The number of decimal places is decreased by selecting the desired cells, For very wide text, the text may be wrapped to allow
the text to occupy more than one line. This is done by se- lecting the cells in which the text is to be wrapped, selecting the Home menu tab, and selecting the Wrap Text button in the Alignment group. The Wrap Text button is shown in Figure 3-38.
At times the user may want to have text cover a number of rows or columns, as is the case when creating headings. This is done by selecting the cells to be combined, selecting the Home menu tab, and selecting the Merge and Center button in the Alignment group. The Merge and Center but- ton is shown in Figure 3-39. In addition to merging the cells, the Merge and Center button centers the data. For the merge and center to be successful, the selected cells must be in the shape of a rectangle. If more than one of the cells contains data, only data from the upper-leftmost cell is re- tained and the remaining data is lost. If data is to be lost, Excel will provide the user with the warning dialogue box shown in Figure 3-40 before proceeding. If the user wants to continue, he or she must click on the OK button.
To unmerge cells, select the merged cells and click the
Merge and Center button. The data from the cells will be
placed in the upper-left cell.
With numeric data, the user often wants to control how the data is displayed. For estimating the user often wants to display the numbers as dollars and cents with commas between
FIGURE 3-38 Home: Wrap Text Button FIGURE 3-39 Home: Merge and Center Button
FIGURE 3-40 Warning Dialogue Box
FIGURE 3-41 Home: Comma Style Button
FIGURE 3-42 Home: Percentage Style Button
selecting the Home menu tab, and selecting the Decrease
Decimal button in the Number group. The Decrease Decimal
button is shown in Figure 3-44.
Other formatting styles, such as date and time, are se- lected from the Number tab of the Format Cells dialogue box (shown in Figure 3-45), which is open by (1) right click- ing on one of the selected cells, selecting Format Cells... from the popup menu (shown in Figure 3-17), and selecting the Number tab or (2) selecting the Home menu tab and se- lecting the Format Cells: Number Dialogue Box Launcher at the lower-right corner of the Number group.
Borders
Borders are drawn around cells or groups of cells using the Borders button, located in the Font group on the Home menu tab. The Borders button is shown in Figure 3-46. The user may use the most recently used border by clicking on the left side of the button.
FIGURE 3-44 Home: Decrease Decimal Button
FIGURE 3-45 Number Tab of the Format Cells Dialogue Box
FIGURE 3-46 Home: Borders Button
The user can select from the twelve preset borders shown in Figure 3-47 by selecting the small arrow on the right side of the Borders button to bring up the Borders popup menu. After selecting a preset border type, the popup menu will disappear and the Border button will change to show the most recently used preset border. To erase all bor- ders associated with a cell, select the No Border from the Borders popup menu shown in Figure 3-47.
Zooming
The extent of the spreadsheet shown on the computer screen is controlled by the Zoom slider located in the lower-right hand corner. The user may zoom in and out by sliding the control knob as shown in Figure 3-48 or by clicking on the Plus or Minus buttons located to the right and left of the slider. When clicking on the Plus or Minus buttons the zoom progresses in increments of 10%. From the Zoom dialogue box (shown in Figure 3-49), the user can select from one of
the Zoom button (shown in Figure 3-51). By selecting a num- ber of rows or columns and selecting Fit selection from the Zoom dialogue box the spreadsheet will zoom to match the width of the selected columns or the height of the selected rows. This may also be done by selecting the rows or columns, selecting the View menu tab, and selecting the Zoom to
Selection button (shown in Figure 3-52). It is important to
understand that changing the zoom will not change the scale at which the spreadsheet will be printed. This is done by set- ting the print scale, which is discussed later in this chapter.
Exercise 3-4
In this exercise you will modify the file from Exercise 3-3 by changing the column widths, merging and formatting cells, and adding borders. Begin by changing the column widths using the following steps:
1. Make sure that Chapter 3, the workbook modified in Exercise 3-3, is open.
2. Change the width of Column A to 25 by right clicking on the letter A at the top of Column A, selecting
Column Width... from the popup menu (shown in
Figure 3-27) to bring up the Column Width dialogue box (shown in Figure 3-26), enter 25 in the Column Width: text box, and click the OK button.
3. Change the width of Column B to 6, that of Column C to 4, and that of Columns D through L to 10 by using the
FIGURE 3-47 Borders Popup Menus
FIGURE 3-48 Zoom Slider
FIGURE 3-49 Zoom Dialogue Box
the standard zoom percentages, select a custom zoom per- centage, or zoom to fit the width or height of a selected num- ber of cells. The Zoom dialogue box is opened by clicking on the Zoom level located to the right of the Status bar (shown in Figure 3-50) or by selecting the View menu tab and selecting
FIGURE 3-50 Zoom Level
FIGURE 3-51 View: Zoom Button
same procedures. Hint: Columns D through L can be changed at once by selecting Columns D through L using the Shift key, right clicking on any one of the highlighted columns, and selecting Column Width... from the popup menu to bring up the Column Width dialogue box. You can now change the width of all of the columns at once. Next, format the cells using the following steps:
4. Center the text in Cells D2 through L2 by selecting Cells D2 through L2 using the Shift key and clicking the
Center button (shown in Figure 3-31) in the Alignment
group on the Home menu tab.
5. Center the text in Column C by selecting Column C and clicking the Center button.
6. Change the formatting for columns E, I, K, and L to the accounting style by selecting Column E and holding down the Ctrl key while selecting Columns I, K, and L to highlight these columns. With the columns highlighted, click the Comma Style button (shown in Figure 3-41) in the Number group on the Home menu tab to change the formatting to the accounting style and then reduce the number of decimal points to zero by clicking on the
Decrease Decimal button (shown in Figure 3-44) in the
Number group on the Home menu tab twice.
7. Change the formatting for columns D, F, G, H, and J to the comma style as done previously; however, do not change the number of decimal points.
8. Merge Cells A1 and A2 by highlighting Cells A1 and A2 and clicking on the Merge and Center button (shown in Figure 3-39) in the Alignment group on the Home menu tab.
9. Left justify the text in the merged cells by clicking the
Align Text Left button (shown in Figure 3-30) in the
Alignment group on the Home menu tab.
10. Merge Cells B1 through C2 by highlighting Cells B1 through C2 and clicking on the Merge and Center but- ton (shown in Figure 3-39) in the Alignment group on the Home menu tab.
11. Merge Cells D1 and E1 in the same manner. 12. Merge Cells F1 through I1.
13. Merge Cells J1 and K1. 14. Merge Cells L1 and L2.
Next, you will underline the headings you have created using the following steps:
15. Underline Row 2 by highlighting Cells A1 through L2 and clicking the Bottom Border button (shown in Figure 3-46) in the Font group on the Home menu tab. If another border is shown in the Borders button, select the correct border by clicking on the small arrow to the right of the Borders button and selecting the Bottom Border from the Borders popup menu (shown in Figure 3-47). Next, set the zoom so you can see Columns A through L using the following steps:
16. Select Columns A through L by clicking on Column A and hold down the Shift key while clicking on Column L.
17. Click on the Zoom level (shown in Figure 3-50) at the left of the Status bar to bring up the Zoom dialogue box (shown in Figure 3-49), select Fit selection, and click the OK button to close the Zoom dialogue box. Columns A through L should cover the entire width of the spreadsheet.
18. Save the workbook by clicking on the Save button on the Quick Access toolbar or typing Ctrl+S.
The first two rows of your spreadsheet should be the same as the first two rows of the spreadsheet in Figure 3-24.