Lesson 21: Managing Multiple-Sheet Workbooks

1. Lesson 21: Managing Multiple-Sheet Workbooks

2. Learning Objectives • After studying this lesson, you will be able to: • Change the default number of sheets for new workbooks • Create formulas that summarize data from multiple worksheets • Copy worksheets and their formats • Create cell names for navigation and formulas • Construct hyperlinks to worksheet cells and external documents • Print multiple worksheets of a workbook

3. Using Multiple Worksheets • Summarize data into a summary sheet • Create linking formulas between sheets Total from Detail Worksheet 1 Total from Detail Worksheet 2 Total from Detail Worksheet 3 + + Grand total on Summary Sheet

4. Default Number of Sheets • Excel default is three sheets • Change affects only new workbooks

5. Linking Cells: Why Link? • Reflect management needs • Top-level managers want to see the big picture • Department-level managers are interested in details • Automatic updating • Results in linked cells update when detail cells change • Data entered only once

6. Creating Linking Formulas • Create the linking formula in the Summary worksheet • Use Point Mode to create a linking formula Begin with an equals sign =Postage!B16 Source cell reference Sheet name of source cell followed by exclamation point

7. Copy and Move Worksheets • Create an exact duplicate of the original worksheet Check to copy; leave blank to move

8. Copying Formats • Copy formats consistently between worksheets Select All Format Painter mouse pointer Format Painter

9. Paste Options • Not the same ole pasting! • Choose how you wish to paint information from one cell to another Paste Options button – at the point of where you paste Paste options via the Ribbon

10. Naming Cells and Ranges • Enter a name in the Name box for any cell or cell range • Use in formulas in place of cell references • Available throughout a workbook Name box – PostageTotal is the defined name of cell B16

11. Naming Rules • Must begin with a letter • Cannot resemble a cell reference (A3) • No space, hyphens, or symbols • Underscores, periods, capital letters are OK • Instructional_Materials • Instructional.Materials • InstructionalMaterials

12. Using Names to Navigate Choose a name from the list Highlight displays defined range chosen

13. Using Names in Formulas • =SUM(Salaries) • =Sales – Expenses • =TotalPostage • Linking formula

14. Modifying/Deleting Defined Names • Name Manager Delete the selected name… …or change its cell reference with the Collapse button

15. Types of Hyperlinks • Internal • To cells in a workbook • External • To another workbook or Office file • To a web page • To an email address

16. Creating Hyperlinks Use a ScreenTip to display in the worksheet Cell reference and worksheet Location types Inserted hyperlink

17. Printing Multiple-Sheet Workbooks • Select multiple worksheets • (Shift) – Select adjacent sheets • (Ctrl) – Select nonadjacent sheets • Apply page setup options to multiple worksheets • Print selected sheets • Print all sheets in the workbook

18. Lesson 21: Managing Multiple-Sheet Workbooks