You can move or copy sheets using one of two methods: • Drag and drop
• Shortcut menu
Drag and drop
The easiest way to rearrange sheets is to click the tab of any sheet to be moved, then drag it along the row of tabs. A small black inverted triangle indicates where the sheet is to be inserted. This method can only be used to move a sheet, not to copy it.
Shortcut menu
Right-click the tab of the sheet to be moved. Choose Move or Copy. The Move or Copy dialog box appears (see Exhibit 5-9). From the Before sheet list, select the point of insertion. If you want to make a copy, select Create a copy. To move or copy a sheet to another workbook, select the destination workbook in the To book box.
EXHIBIT 5-9
Move or Copy dialog box
EXERCISE 5-5
Moving sheets within a workbook
This exercise demonstrates how to move sheets within the same workbook. It also shows how to avoid an error when moving sheets in workbooks where formulas reference a range of sheets.
2. Select the Consolidated sheet by clicking its tab. Select cell B6 and study the formula: =SUM(Computers:Music!B6). This formula sums cell B6 from the sheets Computers, Stereo, TV, and Music. Click each sheet tab in turn and verify that this formula adds up the Jan-00 revenue amounts in the four divisions represented by the four sheets; the total is $42,500.00.
3. Click the tab for Computers and drag it so the black inverted triangle is between TV and Music. Release the mouse button and the Computers sheet is moved between TV and Music.
4. Select Consolidated and click cell B6. The formula in this cell is unchanged, but the total now shows $21,500.00. By moving the Computers sheet to follow Stereo and TV, the formula in cell B6 in Consolidated no longer includes the January revenue from Stereo and TV.
5. To further illustrate, right-click Computers. Choose Move or Copy. In the Before sheet box, select (move to end) and click OK. The Computers sheet is now moved to behind Music. Select Consolidated and study the formula in cell B6. The result may surprise you. Because you moved the Computers sheet behind Music, Excel changed the formula in cell B6 to =SUM(Music!B6).
The lesson to learn here is that before you move sheets around, you must check the workbook model carefully for potential pitfalls. In workbook models with formulas referencing a range of sheets, the sheets can only be moved correctly if you also remember to adjust the relevant formulas. You cannot undo sheet moves. 6. Now drag Computers to its original position (between Consolidated and Stereo).
Select Consolidated and click cell B6. Notice that this move does not reverse the change to the formulas in the Consolidated sheet.
7. Close the file and do not save the changes. EXERCISE 5-6
Copying sheets
This exercise demonstrates how to make duplicate copies of a sheet to build a multiple-sheet workbook. Suppose you want to construct a workbook that will contain the income statement for the first quarter for Pacific Coast Limited’s four divisions. You want each division’s income statement to be on a separate sheet; however, you know that each statement will have the same items (revenues and expenses). In fact, these sheets would be identical except for the actual revenue and expenses amounts.
1. Open the workbook CT2L5P2.XLS. This workbook contains the income statement for the Computers division. Your task is to duplicate this sheet to create the income statements for the other three divisions: Stereo, TV, and Music.
2. Right-click the Computers tab. Choose Move or Copy to display the Move or Copy dialog box. Drag this dialog box to the centre of the worksheet. Click Create a copy. 3. In the Before sheet box, click (move to end). Click OK.
4. A new sheet appears after Computers and its tab is labelled “Computers (2).” Double-click this tab. Type Stereo and press ENTER.
5. Repeat steps 2 to 4 to create sheets for TV and Music, each time clicking (move to end) in the Move or Copy dialog box. Remember to click Create a copy each time; otherwise, you will move the sheet instead of copying it. If you moved the sheet, use the procedure described in Exercise 5-5 to move the sheet back to its original
location. When this step is completed, the sheets should be in the order (from left to right) of Computers, Stereo, TV, and Music.
6. Study the four sheets. They are identical in every respect, including cell values, formats, and formulas. All you would need to do now is to input the appropriate revenue and expense amounts in the three new sheets and change the division name at the top of each sheet.
7. Use the Save As command to save this workbook under your own initials. A copy of the solution is in the workbook CT2L5P2S.XLS. Close the workbook after saving.
The technique described in the preceding exercise is recommended for creating identically structured and formatted sheets. While you could copy the cells in the Computers sheet to subsequent sheets using the Copy and Paste commands (described in Lesson 4), these
commands do not yield identically formatted sheets, and you will need to adjust the format of the results.
EXERCISE 5-7
Moving and copying between workbooks
This exercise demonstrates moving and copying sheets between two workbooks. 1. Open the workbook CT2L5P1S.XLS.
2. Choose Switch Windows from the View ribbon and make sure that two workbooks (CT2L5P1S.XLS and Book1) are currently open. If not, choose New under the Office button to open another blank workbook. (It may be named Book2, Book3, and so on.)
3. Click Arrange All in the View ribbon and select Tiled. Click OK. The two workbooks are displayed side-by-side.
4. Click anywhere on the workbook CT2L5P1S.XLS to make the workbook active. Right-click the Computers tab. Choose Move or Copy.
5. Click the drop-down arrow for the To book box. Select Book1 (or Book2). Select Sheet1 to insert the copy before Sheet1. Because you want to make a copy, select Create a copy by clicking it. Click OK. An identical copy of the Computers worksheet is inserted before Sheet1 in the workbook Book1 (or Book2). If you had not selected Create a copy, you would have moved the Computers sheet instead of copying it.
6. You can also move a sheet from one workbook to another by dragging and dropping. Click the tab for Stereo to select it. Drag it across to workbook Book1 (or Book2) between Computers and Sheet1. This move affects the results in the Consolidated worksheet because the CT2L5P1S.XLS workbook no longer contains the worksheet for Stereo.