EXA-2: Microsoft Excel 2021 Advanced

Module 1: Organize Summary and Details on Your Data
Topic A: Outlining and Grouping Data

• Using Automatic Outlining
• Displaying and Collapsing Levels
• Grouping Data Manually
• Creating Subtotals

Module 2: Planning, Forecasting & Projecting of Your Data
Topic A: Using Data Analysis Tools

• Using a One or Two Input Data Table
• Using Goal Seek

Topic B: Exploring Scenarios

• Creating a Scenario
• Editing Scenarios
• Creating Scenario Summary Report

Topic C: Using Solver

• Generating Reports with Solver
• Changing Solver Values
• Managing Solver Constraints
• Using Solver as a Goal Seek Tool

Module 3: Retrieve, Compare or Compiling Data with Functions
Topic A: Using the XOOKUP Function

• Using XLOOKUP to Find Data
• Find Exact Match with XLOOKUP
• Find Closest Match with XLOOKUP

Topic B: Alternative LOOKUP Function

• Using the INDEX Function
• Using the MATCH Function
• Combining the MATCH and INDEX functions

Topic C: Linking, Consolidating, and Combining Data

• Linking Workbooks
• Consolidating Workbooks
• Combining Worksheets

Module 4: Present Data with Visual Report
Topic A: Create Combo Chart

• Comparing 2 Types of Values with high Variance
• Creating Combo Chart
• Editing Combo Chart

Module 5: Summarizing and Analyzing Data with PivotTable, Slicer and PivotCharts
Topic A: Create a PivotTable

• Create PivotTable Dialog Box
• PivotTable Fields Pane
• Summarize Data in a PivotTable
• Show Values as Functionality of a PivotTable
• Personalize the default PivotTable layout

Topic B: Filter Data by Using Slicer & Timeline

• Insert Slicers Dialog Box
• Insert Timeline

Topic C: Analyzing Data with PivotChart

• Creating PivotChart
• Editing a PivotChart

Module 6: Navigating Large Workbooks
Topic A: Linking Workbooks

• Inserting Hyperlinks
• Editing Hyperlinks
• Formatting Hyperlinks
• Using Hyperlinks in Excel

Module 7: Prevent User Changes with Protection
Topic A: Protecting Worksheet and Workbook

• Lock & Unlock Cells
• Protecting the Current Sheet
• Allow users to edit range
• Protecting an Entire Workbook
• File Protection