Microsoft Excel 2019
​Intermediate
​Student Area

Module 5: Generating Reports from Multiple Worksheets and Workbooks

OBJECTIVES:

• Copy worksheets between workbooks
• View a workbook in multiple windows
• Organize worksheets in a worksheet group
• Write a 3-D reference
• Write an external reference
• Manage the security features of linked documents
• Create a hyperlink to a document source
• Link to an email address
• Create and apply a named range
• Work with name scope
• Create a workbook template

Module 6: Managing Data with Data Tools

OBJECTIVES:

• Split a workbook window into panes
• Highlight and remove duplicate values in a data range
• Sort a data range by one or more fields
• Add subtotals to a data range
• Find and select workbook cells
• Filter data based on one or more fields
• Create an advanced filter
• Convert a data range to an Excel table
• Work with table styles and table elements
• Create and apply a slicer
• Calculate summary statistics with the SUBTOTAL function
• Design and create an interactive dashboard

Module 7: Summarizing Data with PivotTables

OBJECTIVES:

• Do approximate match lookups
• Work with logical functions
• Calculate statistics with summary IF functions
• Create a PivotTable
• Change a PivotTable layout
• Format a PivotTable
• Create a PivotChart
• Apply a slicer to multiple PivotTables
• Create a timeline slicer

Module 8: Performing What-If Analyses

OBJECTIVES:

• Explore the principles of cost-volume-profit relationships
• Create a one-variable data table
• Create a two-variable data table
• Create and apply different Excel scenarios with the Scenario Manager
• Generate a scenario summary report
• Generate a scenario PivotTable report
• Explore the principles of a product mix
• Run Solver to calculate optimal solutions
• Create and apply constraints to a Solver model
• Save and load a Solver model