e04ch07_powerpoints (1).pdf

Your Office: Microsoft Office 365 Excel 2019 Comprehensive Fourth Edition Chapter 7 Multiple Worksheets, Workbooks, and...

Your Office: Microsoft Office 365 Excel 2019 Comprehensive Fourth Edition Chapter 7 Multiple Worksheets, Workbooks, and Templates Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Learning Objectives 7.1 Group worksheets 7.2 Create summary worksheets 7.3 Work with multiple workbooks 7.4 Collaborate using multiple workbooks 7.5 Use existing templates 7.6 Create templates from an existing workbook Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Group Worksheets (1 of 5) Worksheets can be grouped for tasks such as: Entering data Adding formatting Inserting and deleting rows or columns Deleting or clearing cells Entering formulas Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Group Worksheets (2 of 5) Multiple ways to group worksheets: Click the tab of a worksheet, hold down Ctrl Click the worksheet tab of additional worksheets If tabs are adjacent to each other: – Click the tab of the first worksheet, hold down Shift, and click the tab of the last worksheet Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Group Worksheets (3 of 5) To determine if sheets have been grouped: The tab of each worksheet will appear white or a lighter color than the rest The file name at the top of the workbook will also include the show [Group] Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Group Worksheets (4 of 5) To ungroup worksheets: Right-click on a grouped worksheet tab Select Ungroup Sheets from the shortcut menu Click on the tab of a worksheet that is not grouped Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Group Worksheets (5 of 5) Figure 4 Fill Across Worksheets dialog box Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Create Summary Worksheets (1 of 6) Option Use Consolidate by position Arrange data in all worksheets in identical order and location Consolidate by category Organize data with the same row and column labels, but the data is presented differently on different sheets Consolidate by formula Formulas with cell references or 3-D references to other worksheets with data not in a consistent position or consistent categories of data PivotTable report PivotTable instead of a consolidation Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Create Summary Worksheets (2 of 6) Figure 8 3-D formula added to worksheet Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Create Summary Worksheets (3 of 6) Figure 10 Ranges added to Consolidate dialog box Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Create Summary Worksheets (4 of 6) Figure 11 Consolidate dialog box options Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Create Summary Worksheets (5 of 6) Figure 12 Consolidate by category Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Create Summary Worksheets (6 of 6) Figure 13 Consolidate dialog box with link box checked Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Work with Multiple Workbooks (1 of 2) Figure 16 Workbooks arranged with the Tiled option Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Work with Multiple Workbooks (2 of 2) Linking Workbooks Advantage – Can update source data when linked workbook is opened Disadvantage – Links between workbooks are easily broken Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Collaborate Using Multiple Workbooks (1 of 4) Collaboration allows workbooks to be shared Co-authoring workbooks: – Two or more users can work simultaneously – Users see changes in real time Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Collaborate Using Multiple Workbooks (2 of 4) Co-authoring requirements Office 365 subscription Latest version of Office – Excel Online may also be used Users must use Office 365 subscription Workbook will be stored in the cloud Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Collaborate Using Multiple Workbooks (3 of 4) Figure 20 AutoSave in the OneDrive Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Collaborate Using Multiple Workbooks (4 of 4) Figure 22 Workbook collaboration Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Use Existing Templates (1 of 2) Two main types: Local Templates – Installed on your hard drive – Located on the File tab Online Templates – Thousands are available to download locally Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Use Existing Templates (2 of 2) Figure 23 Creating a new template Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Create Templates from an Existing Workbook (1 of 2) Create workbook Remove specific data, leaving formulas and formatting Save as a template format (.xltx extension) Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Create Templates from an Existing Workbook (2 of 2) Figure 25 Small business calendar online template Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Summary (1 of 3) Make changes to multiple worksheets at the same time with grouping Summarize data – Consolidate by Position – Consolidate by Category – Consolidate by Formula – PivotTable Report Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Summary (2 of 3) Arrangements for two or more workbooks: – Tiled – Horizontal – Vertical – Cascaded Link workbooks to automatically update data Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Summary (3 of 3) Advantages of co-authoring workbooks – Multiple users – Saved on the cloud – Make and view changes in real-time Templates: Use built-in or download online templates Create templates from existing workbooks Modify existing templates Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Questions? Copyright © 2020, 2017, 2014 Pearson All Rights Reserved Copyright Copyright © 2020, 2017, 2014 Pearson All Rights Reserved