Making Changes to Your Workbook

Core Training for Microsoft Office XP

Making Workbooks Easier to Work With

1	To rename the current worksheet, in the lower-left corner of the workbook window, double-click the Sheet2 sheet tab.
2	Type January, and press the ENTER key on your keyboard.
3	To move the January sheet tab to the left of the Sheet 1 tab, point to the January sheet tab, press the left mouse button down, drag it to the left of Sheet1, and release the mouse button.
4	To select columns A through M in order to change their width, click the column head at the top of column A and then click the right arrow on the horizontal scroll bar to scroll to the right.
5	Hold down the SHIFT key on your keyboard, click the column head for column M, and then release the SHIFT key.
6	Position the mouse pointer over the right edge of the heading for column L, hold down the left mouse button, drag the edge to the right until the ScreenTip above it says Width: 10.00 (75 pixels), and release the mouse button.
7	To increase the height of rows 3 through 35, click the row selector to the left of Row 3, click once beneath the scroll box in the vertical scroll bar, hold down the SHIFT key, click the row selector for row 35, and release the SHIFT key.
8	Position the mouse pointer over the bottom edge of the row selector for row 35, hold down the left mouse button, drag the edge down until the ScreenTip says Height: 25.50 (34 pixels), and release the mouse button.
9	To return to the upper-left corner of the page, hold down the CONTROL key while pressing the HOME key on your keyboard.
10	To merge and center the title across cells E2, F2, and G2, point to cell E2, press and hold down the left mouse button, drag to cell G2, and release the mouse button.
11	On the Formatting toolbar, click the Merge and Center button.
12	To insert a new, blank row below the title row, click cell A3.
13	Click the Insert menu, and then click Rows.
14	To insert a new column, labeled column A, to the left of the column previously labeled column A, click the Insert menu and then click Columns.
15	To temporarily hide rows 13 through 36 so that only data for the first week is visible, click the row selector for row 13 and then click three times below the scroll box in the vertical scroll bar.
16	Hold down the SHIFT key, click the row selector for row 36, and release the SHIFT key.
17	Click the Format menu, point to Row, and then click Hide.
18	To reveal the hidden rows again, click the Format menu, point to Row, and then click Unhide.

Making Data Easier to Read

1	To make the text in cell B34 wrap to fit within the original borders of the cell, click cell B34.
2	Click the Format menu, and click Cells.
3	In the Format Cells dialog box, click the Alignment tab.
4	In the Text control section, click the Wrap Text option to insert a check mark into its check box and then click OK.
5	To move to the upper-left corner of the worksheet, hold down the CONTROL key while pressing the HOME key on your keyboard.
6	To center the labels in cells B1 through E1 within each of those cells, point to cell B1, hold down the left mouse button, drag the pointer to cell E1, and release the mouse button.
7	On the Formatting toolbar, click the Center button.
8	To freeze row 1 and column A so that they stay in place when the remaining rows and columns scroll normally, click cell B2.
9	Click the Window menu, and then click Freeze Panes.
10	Notice the split bar that appears between rows 1 and 2 and between columns A and B. To see how the labels in row 1 and column A remain in place when you scroll, click once beneath the scroll box in the vertical scroll bar.
11	Click once to the right of the scroll box in the horizontal scroll bar.
12	To get rid of the split bars and allow the labels to scroll normally, click the Window menu and click Unfreeze Panes.
13	To freeze only the column labels in row 1 but not the row labels in column A, click cell A2, click the Window menu, and click Freeze Panes again.

Changing the Appearance of Data

1	To increase the font size of the text used as a title, click cell G2.
2	On the Formatting toolbar, click the Font Size down arrow and, on the list that appears, click 14.
3	To further emphasize the title by making this text bold, on the Formatting toolbar, click the Bold button.
4	To draw a border around this title, on the Formatting toolbar, click the down arrow at the right of the Borders button and then, on the list that appears, click Draw Borders.
5	Point to cell E2, hold down the left mouse button, drag to cell I2, and release the mouse button.
6	On the Borders toolbar, click the Close button.
7	To give the title cell a yellow background, on the Formatting toolbar, click the down arrow to the right of the Fill Color button.
8	On the Fill Color palette that appears, click the yellow square (fourth row, third square from left).
9	To center the text for data labels within each cell in row 5, click the row head for row 5.
10	On the Formatting toolbar, click the Center button.

Applying an Existing Format to Data

1	To create a new style and apply it to the contents of cell C3, click cell C3.
2	Click the Format menu, and click Style.
3	In the Style dialog box, in the Style name box, type Emphasis.
4	Click Modify.
5	In the Format Cells dialog box, on the Alignment tab, in the Horizontal box, click the down arrow and, from the list that appears, click Center.
6	To change the font style, click the Font tab.
7	In the Font style list, click Bold Italic.
8	To apply your changes to the new style, click OK.
9	To apply the Emphasis style to the text in cell C3, in the Style dialog box, click OK.
10	To copy the style in C3 to the content of another cell, on the Standard toolbar, click the Format Painter button.
11	Click cell B5.

Making Numbers Easier to Read

1	To give cell B4 a specific date format, click cell B4, click the Format menu, and click Cells.
2	In the Format Cells dialog box, click the Number tab.
3	In the Category pane, click Date.
4	To use 3/14/2001 (already selected in the Type* pane) as the date format, click OK.
5	To test the new formatting in cell B4, type January 25, 2003, and press the ENTER key on your keyboard.
6	To copy the date format from cell B4 to cells B5:B23, click cell B4, and then, on the Standard toolbar, click the Format Painter button.
7	Point to cell B5, hold down the left mouse button, drag the pointer down to cell B23, and release the mouse button.
8	To format a group of cells that will contain telephone numbers, click once to the right of the scroll box in the horizontal scroll bar and then click cell J4.
9	Click the Format menu, and then click Cells.
10	In the Format Cells dialog box, on the Number tab in the Category pane, click Special.
11	In the Type pane, click Phone Number and then click OK.
12	To test out the formatting you've added to cell J4, type 3255550102 and press ENTER.
13	To copy this formatting to the cells below, click cell J4 and then on the Standard toolbar, click the Format Painter button.
14	Point to cell J5, hold down the left mouse button, drag to cell J23, and release the mouse button.
15	To create a custom format to apply to the cells in column K, click cell K4, click the Format menu, and click Cells.
16	In the Format Cells dialog box, in the Category pane, click Custom.
17	To display values with a comma separating thousands and two decimal places, in the Type list, click #,##0.00.
18	To display values as currency, in the Type box, click to the left of the existing format, and type $.
19	To add the word total to the right of each formatted value, click to the right of the last zero in the Type box, type a quotation mark ("), press the SPACEBAR, type the word total, and type a final quotation mark (").
20	Click OK.
21	To see your custom format applied, in cell K4, type 2400 and press ENTER.
22	To delete the word total from this formatting, click cell K4, click the Format menu, and click Cells.
23	Position the mouse pointer in the Type box, between the 0 and the first quote mark, hold down the left mouse button, move the pointer to the right until " total" has been highlighted, and release the mouse button.
24	Press the DELETE key on your keyboard, and then click OK.
25	To copy the custom format from cell K4 to additional cells, click the Format Painter button on the Standard toolbar.
26	Point to cell K5, hold down the left mouse button, drag to cell K23, and release the mouse button.

Changing Data's Appearance Based on Its Value

1	To create a conditional format so that all values between 1,000 and 2,000 display in blue text, click the Format menu and click Conditional Formatting.
2	In the Conditional Formatting dialog box, click in the third box from left and type 1000.
3	Click in the last box (at far right), and type 2000.
4	Click the Format button.
5	On the Font tab of the Format Cells dialog box, click the down arrow for the Color box.
6	On the color palette that appears, click the blue square (second row, third from right) and then click OK.
7	To add a second condition to your format so that values between 2,000 and 2,500 display in green text, click the Add button.
8	In the Condition 2 section, click in the third box from left and type 2000.
9	Click in the last box (at far right), type 2500, and then click the Format button.
10	On the Font tab of the Format Cells dialog box, click the down arrow of the Color box.
11	On the color palette, click the green square (second row, fourth from left) and then click OK.
12	In the Conditional Formatting dialog box, click OK.
13	To copy this conditional format to cells K5 and K6, in cell K4, use your mouse to point to the Fill Handle (the black square at the lower-right corner of the selected cell), press and hold down the left mouse button, drag to cell K6, and release the mouse button.
14	Notice that the contents of cells K5 and K6 have changed to $2,400.00. To change them back to their original values but retain the copied formatting, click the Auto Fill Options button at the bottom of cell K6.
15	In the list of Auto Fill options, click Fill Formatting Only.