Working with Multiple Excel 2003 Workbooks

Working with more than one worksheet in a single workbook is bad enough, but working with worksheets in different workbooks can be really wicked. The key to doing this successfully is just keeping track of "who's on first," and you do this by opening and using windows on the individual workbook files you have open.

With the different workbook windows in place, you can then compare the data in different workbooks, use the drag-and-drop method to copy or move data between workbooks, or even copy or move entire worksheets.

Comparing windows on different workbooks

To work with sheets from different workbook files you have open, you manually arrange their workbook windows in the Excel Work area, or you choose the Window --> Compare Side by Side With command. If you have only two workbooks open when you choose the Compare Side by Side With command, Excel places the active workbook that you last opened above the one that opened earlier (with their active worksheets displayed). If you have more than two workbooks open, Excel displays the Compare Side by Side dialog box where you click the name of the workbook that you want to compare with the active one.

If you need to compare more than two workbooks on the same screen, instead of choosing the Compare Side by Side With item on the Window menu, you choose Arrange and then select the desired Arrange option (Tiled, Horizontal, Vertical, or Cascading). Just make sure when selecting this option in the Arrange Windows dialog box that the Windows of Active Workbook check box is not selected (in other words, is empty of its check mark).

Transferring data between open windows

After the windows on your different workbooks are arranged on-screen the way you want them, you can compare or transfer information between them. To compare data in different workbooks, you switch between the different windows, activating and bringing the regions of the different worksheets you want to compare into view.

To move data between workbook windows, arrange the worksheets in these windows so that both the cells with the data entries you want to move and the cell range into which you want to move them are both displayed in their respective windows. Then, select the cell selection to be moved, drag it to the other worksheet window, to first cell of the range where it is to be moved to, and release the mouse button. To copy data between workbooks, you follow the exact same procedure, except that you hold down the Ctrl key as you drag the selected range from one window to another.

When you're finished working with workbook windows arranged in some manner in the Excel Work area, you can return to the normal full-screen view by clicking the Maximize button on one of the windows. As soon as you maximize one workbook window, all the rest of the arranged workbook windows are made full size as well. If you used the Compare Side by Side command to set up the windows, you can do this by clicking the Close Side by Side button on the Compare Side by Side toolbar or by choosing Window --> Close Side by Side (which replaces the Compare Side by Side With item on the Window menu).

Transferring sheets from one workbook to another

Instead of copying cell ranges from one workbook to another, you can move (or copy) entire worksheets between workbooks. You can do this with drag-and-drop or by using a variation on the standard cut-and-paste method, the Edit --> Move or Copy menu command.

To use drag-and-drop to move a sheet between open windows, you simply drag its sheet tab from its window to the place on the sheet tabs in the other window where the sheet is to be moved to. As soon as you release the mouse button, the entire worksheet is moved from one file to the other, and its sheet tab now appears among the others in that workbook. To copy a sheet rather than move it, you perform the same procedure, except that you hold down the Ctrl key as you drag the sheet tab from one window to the next.

To use the Edit --> Move or Copy Sheets command on the Excel menu bar to copy or move entire worksheets, you follow these steps:

1. Open both the workbook containing the sheets to be moved or copied and the workbook where the sheets will be moved or copied to.

Both the source and destination workbooks must be open in order to copy or move sheets between them.

2. Click the workbook window with sheets to be moved or copied.

Doing this activates the source workbook so that you can select the sheet or sheets you want to move or copy.

3. Select the sheet tab of the worksheet or worksheets to be moved or copied.

To select more than one worksheet, hold down the Ctrl key as you click the individual sheet tabs.

4. Choose Edit --> Move or Copy Sheets on the Excel menu bar.

Doing this opens the Move or Copy dialog box, as shown in Figure 1.

5. Click the filename of the workbook into which the selected sheets are to be moved or copied in the To Book pop-up menu.

If you want to move or copy the selected worksheets into a new workbook file, click the (New Book) item at the very top of this pop-up menu.

6. Click the name of the sheet that should immediately follow the sheet(s) that you're about to move or copy into this workbook in the Before Sheet list box.

If you want to move or copy the selected sheet(s) to the very end of the destination workbook, click (Move to End) at the bottom of this list box.

7. If you want to copy the selected sheet(s) rather than move them, click the Create a Copy check box.

If you don't select this check box, Excel automatically moves the selected sheet(s) from one workbook to the other instead of copying them.

8. Click OK to close the Move or Copy dialog box and complete the move or copy operation.