1 / 17

Lesson 10: Managing Multiple-Sheet Workbooks

Lesson 10: Managing Multiple-Sheet Workbooks. Using Multiple Worksheets. Summarize data into a summary sheet Create linking formulas between sheets. Modifying the Default Number of Slides. Excel default is three sheets Change affects only new workbooks. Linking Cells: Why Link?.

keren
Download Presentation

Lesson 10: Managing Multiple-Sheet Workbooks

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related