Lesson 10: Managing Multiple-Sheet Workbooks

1. Lesson 10: Managing Multiple-Sheet Workbooks

2. Using Multiple Worksheets • Summarize data into a summary sheet • Create linking formulas between sheets

3. Modifying the Default Number of Slides Excel default is three sheets Change affects only new workbooks

4. 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

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

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

7. Copy Formats between Worksheets • Copy formats consistently between worksheets Format Painter Select All button

8. 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

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

10. Creating Names from Row or Column Titles Select titles and values (columns A and B) FormulasDefined Names Create from Selection Specify which cells to use for names Defined names

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

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

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

14. Types of Hyperlinks Internal To cells in a workbook External To another workbook or non-Excel file To a web page To an email address in Outlook

15. Creating Hyperlinks Create a ScreenTip to display in the worksheet Location type Cell reference and worksheet Inserted hyperlink 15

16. 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 workbook 16

17. Lesson 10: Managing Multiple-Sheet Workbooks