IT and Computing > Microsoft Excel 2019 Advance

Microsoft Excel 2019 Advance

Course Objectives

Create Subtotal Using the Subtotal Function
Analyze data Using Pivot Tables
Perform What If Analysis using:
o Goal Seek
o Solver
o Input Table
o Scenarios
Use VLOOKUP function to extract data
Nesting INDEX and MATCH Function
Combining & Consolidating Data
Summarizing & Analyzing with Pivot Table and Pivot Charts
Record and Run a Macro

Target Audience

REQUIRED PREREQUISITES:

Basic knowledge of Microsoft Excel is essential with the following pre-requisites:

Have attended Microsoft Excel – Foundation and Intermediate Level; OR
Able to switch between task applications
Able to create simple to complex formulas and functions
Able to validate data in a Worksheet
Able to apply Filter data using Auto & Advanced Filters
Able to clean Duplicate Records

This course is designed for Clerks, Officers, Executives, Supervisors, Administrators, Managers of all levels; and personnel who already know and understand and want to further enhance their knowledge and practical uses of Microsoft Excel.

Methodology

This program will be conducted with interactive lectures, PowerPoint presentation, discussions and practical exercise.

Course Modules

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 & 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

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
• 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: Automate Task with 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