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