Exploring Microsoft Excel

1. Exploring Microsoft Excel Chapter 5 Consolidating Data: 3-D Workbooks and File Linking By Robert T. Grauer Maryann Barber Exploring Microsoft Excel 2002 Chapter 5

2. Objectives (1 of 2) • Distinguish between a cell reference, worksheet reference, and a 3D reference; use appropriate references to consolidate data • Select and group multiple worksheets to enter common formulas or formats • Use AutoFormat Exploring Microsoft Excel 2002 Chapter 5

3. Objectives (2 of 2) • Explain the advantages of using functions over building formulas when consolidating multiple worksheets • Organize and document a workbook • Copy and Paste to another workbook • Distinguish between a source versus dependent workbook; create external references to link workbooks Exploring Microsoft Excel 2002 Chapter 5

4. Overview • Combine data from several sources into a summary report • Reconcile summary totals with detail totals • Two approaches to 3-D Workbooks: • Workbook contains 3 branch office worksheets and 1 summary worksheet; or • Workbooks for each branch office and summary workbook Exploring Microsoft Excel 2002 Chapter 5

5. 3-D Workbook • Electronic equivalent of a 3 ring binder • Tabs at bottom display worksheet names • Scrolling buttons allow easy movement among worksheets • Window menu allows for tiling, cascading options for multiple workbooks or worksheets Exploring Microsoft Excel 2002 Chapter 5

6. Arranging All Worksheets • Multiple workbooks tiled • The workbook with the blue title bar is the active workbook Exploring Microsoft Excel 2002 Chapter 5

7. Hands-On Exercise 1 • Objective: To open multiple workbooks; to use the Windows Arrange command to tile the open workbooks; to copy a worksheet from one workbook to another • Open a New Workbook • Open the Individual Workbooks • Copy the Atlanta Data • Copy the Boston and Chicago Data • The Corporate Sales Workbook Exploring Microsoft Excel 2002 Chapter 5

8. Worksheet References • Allows you to reference cells in other worksheets in the same workbook • Requires using the name of the worksheet before the cell range • Exclamation point separates worksheet and cell reference • =Atlanta!B3 +Boston!B3+Chicago!B3 • Worksheet reference is absolute • Cell reference can be absolute, relative, or mixed • Can be entered by pointing Exploring Microsoft Excel 2002 Chapter 5

9. 3-D References • Range that spans two or more worksheets in a workbook • Can be used in a Summary Sheet • Requires worksheet names be separated using a colon and exclamation point to separate worksheet name from cell reference • =SUM(Atlanta:Chicago!B3) • The cell range must be identical in every worksheet • Can be entered by pointing Exploring Microsoft Excel 2002 Chapter 5

10. Grouping Worksheets • Grouping worksheets allows for formatting or formulas to be entered one time to a group • Once worksheets are grouped, anything you do in one sheet is done in all grouped sheets • To do something in one sheet only, ungroup the sheets Exploring Microsoft Excel 2002 Chapter 5

11. AutoFormat Command • AutoFormat command allows for predefined formats to be applied to worksheets • Select cells to apply AutoFormat to, then select a format Exploring Microsoft Excel 2002 Chapter 5

12. Hands-On Exercise 2 • Objective: To use 3-D references; to group worksheets; to open multiple windows • Insert a Worksheet • Use AutoFill to help enter data labels • Sum the Worksheets • The Arrange Windows Command • Changing Data • Group Editing • The AutoFormat Command • The Finishing Touches Exploring Microsoft Excel 2002 Chapter 5

13. The Documentation Worksheet • Helpful to detail the workbook with a documentation worksheet • Workbooks can contain many worksheets • Workbooks often created by one person, used by many others • Contains vital descriptive information making the workbook easier to use for all • Formatting ability will improve appearance • Print out cell formulas for added documentation Exploring Microsoft Excel 2002 Chapter 5

14. Hands-On Exercise 3 • Objective: To improve the design of a workbook with a documentation worksheet; sophisticated formatting • Add the Documentation Worksheet • The Wrap Text Command • Add the Worksheet Title • The Page Setup Command • Print the Cell Formulas Exploring Microsoft Excel 2002 Chapter 5

15. Linking Workbooks • Retain information in separate workbooks • Linking uses external references • Dependent workbook requires external data from source workbooks • =[Atlanta.xls]Sheet1!B3 Exploring Microsoft Excel 2002 Chapter 5

16. An External Reference Exploring Microsoft Excel 2002 Chapter 5

17. Hands-On Exercise 4 • Objectives: To create a dependent workbook with external references; to use pointing to create external references • Open the Workbooks • The AutoFill Command • File Linking • Copy the Formulas • Create a Workspace • Change the Data • Close the Workbooks Exploring Microsoft Excel 2002 Chapter 5

18. Summary (1 of 2) • Combine data from different sources into a summary report • Use single workbook or multiple workbook approach • Excel workbook may contain up to 255 worksheets • Each worksheet is identified by a tab at the bottom of the workbook Exploring Microsoft Excel 2002 Chapter 5

19. Summary (2 of 2) • Worksheet reference indicates a cell in another worksheet in the same workbook • When a single workbook is impractical, create external references to other workbooks • Workbooks should be clearly organized and documented Exploring Microsoft Excel 2002 Chapter 5