Nội dung

7 Chapter Manipulating Excel Worksheets An Excel worksheet is where you enter your headings and data and build your formulas. You will spend most of your time in Excel operating within a worksheet, so you need to know how to navigate a worksheet as well as how to perform useful worksheet tasks, such as renaming worksheets; creating new worksheets; and moving, copying, and deleting worksheets. In this chapter you also learn how to change the gridline color, and how to toggle gridlines and headings on and off. 09_577639-ch07.indd 130 3/15/10 2:44 PM Navigate a Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . 132 Rename a Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . 133 Create a New Worksheet . . . . . . . . . . . . . . . . . . . . . . . 134 Move a Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 Copy a Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . . 138 Delete a Worksheet . . . . . . . . . . . . . . . . . . . . . . . . . . 140 Change the Gridline Color. . . . . . . . . . . . . . . . . . . . . . . 142 Toggle Worksheet Gridlines On and Off . . . . . . . . . . . . . 144 Toggle Worksheet Headings On and Off . . . . . . . . . . . . . 145 09_577639-ch07.indd 131 3/15/10 2:44 PM Navigate a Worksheet You can use a few keyboard techniques that make it easier to navigate data after it is entered in a worksheet. After you enter data into a cell, it is usually easiest to use your mouse to click in the next cell you want to work with. However, if you are entering data and your hands are already on the keyboard, then it is often faster to use the keyboard to navigate to the next cell. Keyboard Techniques for Navigating a Worksheet Press To Move Left one cell Right one cell Up one cell Down one cell Home To the beginning of the current row Down one screen Up one screen One screen to the right + One screen to the left + + Home To the beginning of the worksheet + To the bottom right corner of the used portion of the worksheet +arrow keys In the direction of the arrow to the next non-blank cell if the current cell is blank, or to the last non-blank cell if the current cell is not blank 132 09_577639-ch07.indd 132 3/15/10 2:44 PM 7 CHAPTER Rename a Worksheet You can make your Excel workbooks easier to understand and navigate by providing each worksheet with a name that reflects the contents of the sheet. Excel provides worksheets with generic names such as Sheet1 and Sheet2, but you can change these to more descriptive names such as Sales 2010, Amortization, or Budget Data. You can use any combination of letters, numbers, and symbols in a worksheet name, but the maximum number of characters you can use in the name is 31. Rename a Worksheet 1 Display the worksheet you 2 3 4 1 want to rename. 2 Click the Home tab. 3 Click Format. 4 Click Rename Sheet. • • You can also double-click the worksheet’s tab. Excel opens the worksheet name for editing and selects the text. 5 If you want to edit the existing name, press either deselect the text. or to 6 Type the new worksheet name. 7 Press . Excel assigns the new name to the worksheet. 6 09_577639-ch07.indd 133 133 3/15/10 2:44 PM Create a New Worksheet Excel supports multiple worksheets in a single workbook, so you can add as many worksheets as you need for your project or model. Each new Excel workbook comes with three worksheets, but it is not uncommon to require four or more worksheets in a workbook. In most cases, you will add a blank worksheet, but Excel also comes with several predefined worksheet templates that you can use. Note that there is no practical limit to the number of worksheets you can add to a workbook. Create a New Worksheet Insert a Blank Worksheet 2 1 Open the workbook to which 3 you want to add the worksheet. 4 2 Click the Home tab. 3 Click the Insert . 4 Click Insert Sheet. 1 • Excel inserts the worksheet. Note: You can also insert a blank worksheet by pressing + . Note: You can also create a new worksheet by clicking the Insert Worksheet button ( ). 134 09_577639-ch07.indd 134 3/15/10 2:44 PM 7 CHAPTER Manipulating Excel Worksheets Insert a Worksheet from a Template 3 1 Open the workbook to which 1 you want to add the worksheet. 2 Right-click a worksheet tab. 3 Click Insert. 2 The Insert dialog box appears. 4 5 4 Click the Spreadsheet Solutions tab. 5 Click the type of worksheet you want to add. • 6 You can also click Templates on Office Online to download worksheet templates from the Web. 6 Click OK. • How do I navigate from one worksheet to another? Click the tab of the worksheet you want to use. Press + to move to the next worksheet, and + to move to the previous worksheet. Excel inserts the worksheet. Move to the first worksheet. Move to the previous worksheet. Move to the next worksheet. Move to the last worksheet. You can also click the following controls: 135 09_577639-ch07.indd 135 3/15/10 2:44 PM Move a Worksheet You can organize an Excel workbook and make it easier to navigate by moving your worksheets to different positions within the workbook. Although you will most often move a worksheet to a different position within the same workbook, it is also possible to move a worksheet to another workbook. Move a Worksheet 1 If you want to move the worksheet to another workbook, open that workbook and then return to the current workbook. 2 Click the tab of the worksheet you want to move. 3 Click the Home tab. 4 Click Format. 5 Click Move or Copy Sheet. • 2 3 4 You can also right-click the tab and then click Move or Copy Sheet. The Move or Copy dialog box appears. 5 136 09_577639-ch07.indd 136 3/15/10 2:44 PM 7 CHAPTER Manipulating Excel Worksheets 6 If you want to move the sheet 6 to another workbook, click the and then click the To book workbook. 7 7 Use the Before sheet list to 8 click a destination worksheet. When Excel moves the worksheet, it will appear to the left of the sheet you selected in Step 7. 8 Click OK. • Excel moves the worksheet. Is there an easier way to move a worksheet within the same workbook? Yes. It is usually much easier to use your mouse to move a worksheet within the same workbook: 1 Move over the tab of the worksheet you want to move. 3 2 Click and drag the worksheet tab left or right to the new position within the workbook. changes to . • 2 1 As you drag, an arrow shows the position of the worksheet. 3 When you have the worksheet positioned where you want it, drop the worksheet tab. Excel moves the worksheet. 137 09_577639-ch07.indd 137 3/15/10 2:44 PM Copy a Worksheet Excel enables you to make a copy of a worksheet, which is a useful technique if you require a new worksheet that is similar to an existing worksheet. Rather than re-creating the new worksheet from scratch, you can make a copy of an existing worksheet and then edit the copy as needed. Although you will most often copy a worksheet within the same workbook, it is also possible to copy the worksheet to another workbook. Copy a Worksheet 1 If you want to copy the worksheet to another workbook, open that workbook and then return to the current workbook. 2 Click the tab of the worksheet you want to copy. 3 Click the Home tab. 4 Click Format. 5 Click Move or Copy Sheet. • 2 3 4 You can also right-click the tab and then click Move or Copy Sheet. The Move or Copy dialog box appears. 5 138 09_577639-ch07.indd 138 3/15/10 2:44 PM 7 CHAPTER Manipulating Excel Worksheets 6 6 If you want to copy the sheet to another workbook, click the and then click the To book workbook. 7 9 8 7 Use the Before sheet list to click a destination worksheet. When Excel copies the worksheet, the copy will appear to the left of the sheet you selected in Step 7. 8 Click the Create a copy check box ( changes to ). 9 Click OK. • • Excel copies the worksheet. Excel gives the new worksheet the same name as the original, but with (2) appended. Note: See "Rename a Worksheet" earlier in this chapter to learn how to edit the name of the copied worksheet. Is there an easier way to copy a worksheet within the same workbook? Yes. It is usually much easier to use your mouse to copy a worksheet within the same workbook: 1 Move over the tab of the worksheet you want to copy. 2 Hold down . 3 Click and drag the worksheet tab left or right. 1 3 changes to . • 4 4 As you drag, an arrow shows the position of the worksheet. When you have the worksheet positioned where you want it, drop the worksheet tab. Excel copies the worksheet. 139 09_577639-ch07.indd 139 3/15/10 2:44 PM