EX19A(2) Microsoft Excel – Advanced

Course Modules

• Module 1: Getting Different Views 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 & Projection of Your Data

  Topic A: Using Data Analysis Tools
  – Using a One or Two
  – Input Data Table
  – Using Goal Seek

  Topic B: Exploring Scenarios
  – What is a Scenario?
  – Creating a Scenario
  – Saving Multiple Scenarios
  – Creating a Scenario Summary Report

  Topic C: Using Solver
  – Understanding Solver
  – Generating Reports and Scenarios with Solver
  – Changing Solver Values
  – Managing Solver Constraints
  – Using Solver as a Goal Seek Tool

• Module 3: Advanced Functions
  

  Topic A: Working with Array Formulas
  – What are Array Formulas?
  – Using Functions within Array Formulas

  Topic B: Using the LOOKUP Function
  – Using VLOOKUP to Find Data
  – Find Exact Match with VLOOKUP
  – Find Closest Match with VLOOKUP
  – Find with HLOOKUP

  Topic C: Alternative LOOKUP Function
  – Using the INDEX Function
  – Using the MATCH Function
  – Combining the MATCH and INDEX functions

  Topic D: 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
  – Slicers
  – Insert Slicers Dialog Box

  Topic C: Analyzing Data with PivotChart
  – Creating PivotChart
  – Editing a PivotChart

• Module 6: Navigating Large Workbooks
  

  Topic A: Customizing Workbooks
  – Inserting Hyperlinks
  – Editing Hyperlinks
  – Formatting Hyperlinks
  – Using Hyperlinks in Excel

• Module 7: Macros
  

  Topic A: Macros
  – Recording Macros
  – Saving to Macros Enabled Workbook
  – Closing and Re-Opening a Macro Workbook
  – Security Warning Message
  – Playing a Macro
  – Assigning a Keystroke to a Macro

• Module 8: Prevent User Changes with Protection
  

  Topic A: Protecting Worksheet and Workbook
  – Protecting the Current Sheet
  – Protecting an Entire Workbook
  – File Protection