Computer Data Analysis



Computer Data Analysis Instructor: Greg Shaw

CGS 2518

Microsoft Excel – Tutorial 6, Session 6.3

Working with Multiple Worksheets and Workbooks

Skills Checklist and Notes

❑ Switching Between Open Workbooks

• You may have many workbooks open at the same time

• To switch from one open workbook to another, click the Window menu, and then click on a particular workbook from the list

❑ Arranging Multiple Open Workbooks

• When several workbooks are open, you may view them in a variety of ways

• From the Window menu, choose Arrange..., and then click the Tiled, Horizontal, Vertical, or Cascade radio buttons

← Make sure the Windows of active workbook button is not checked

• To “undo” the arrangement, click the Maximize button on each worksheet (or choose a different arrangement)

• No matter how many workbooks are open or what kind of arrangement you specify, only one workbook is active at any given time

← The active workbook is the one in which the next action will occur, and is distinguished by its Title Bar being a much brighter shade of blue than the others

• To make a different workbook the active one, just click anywhere in it (or click its title bar if the workbooks are cascaded)

❑ Hiding Open Workbooks

• You can hide an open workbook to “move it out of the way” temporarily without having to close it and then open it again

1. Make it the active workbook (see above)

2. Choose Hide from the Window menu

• To unhide a workbook, choose Unhide... from the Window menu, choose a workbook from the list of hidden ones, and click OK

❑ Referencing Data in a Different Workbook (External References)

• We have seen how a formula in one worksheet may reference cells on another worksheet of the same workbook (Session 6.1)

• Similarly, a formula may reference cells in a different workbook entirely (this is known as an external reference, because the cells referenced are in a different file)

• To create an external reference, we use the workbook file name enclosed in square brackets. E.g.

=[LBC2005.xls]Annual!$B$22

is an absolute reference to cell B22 on the worksheet name Annual in the workbook LBC2005.xls

• By default, cells in an external reference are referenced absolutely. To change to a relative or mixed reference, edit the formula

• When a formula contains an external reference, we say that the two workbooks are “linked” together

o The source file is the workbook that contains the cells referenced by the formula

o The destination file is the workbook that will receive the data (i.e. the workbook that contains the formula)

❑ Creating an External Reference by Pointing

1. In the destination workbook, type the equal sign in the cell that will contain the formula

2. Switch to the source workbook, click the proper worksheet tab, and click to select a cell or drag to select a range

3. Press [Enter] or click the Enter button to the left of the Formula Bar

❑ Working with Linked Workbooks

• When workbooks are linked, it is important that the destination file accurately reflects what is currently in the source file

• To make sure this is so, we “update” the links periodically

o Excel will prompt you to update the link to the source file whenever you open the destination file

o If both the source and destination are open, Excel will automatically update the link whenever a referenced cell in the source is changed

o If only the destination is open, you can manually update the link at any time (see next topic)

❑ Working with the List of Links

• To view a list of all the links in the active workbook, choose Links... from the Edit menu (the Edit Links dialog box will open)

• To update links, choose any number of links from the list and click the Update Values button

← For more on the Edit Links dialog box, see pages EX 283 – 284

❑ Using Lookup Tables

See handout, “The VLOOKUP Function”

❑ Excel Workspaces

• To open multiple related workbooks – e.g., the 5 Lakeland BoyChoir workbooks in Tutorial 6 - all at once, we create a Workspace File

← This saves time, and – more importantly - the trouble of having to remember several different filenames and folder locations

• The workspace file does not contain the workbooks themselves, but does contains information about them, such as their locations and display options

• To create a workspace file

1. Open all the workbooks that will go in the workspace

2. From the File menu choose Save Workspace...

3. In the Save Workspace dialog box, specify a destination folder and name for your workspace

4. In the Save as type list, make sure Workspace is selected

5. Click the Save button

6. When prompted to save changes, click the Yes To All button

• To open all the workbooks in the workspace, just open the workspace file exactly the same way you open a workbook file

← From the File menu, choose Open..., browse to find the file, etc

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download