Microsoft Excel 2016 Advanced
Private Class From
RM1100
/day
(2 Days)
COURSE OBJECTIVES

Upon completion of this program, participants should be able to:

  • Advance Chart function and PowerPivot Function
  • Analyze data Using Pivot Tables
  • Perform What If Analysis using:
  • Goal Seek
  • Solver
  • Input Table
  • Scenarios
  • Use VLOOKUP function to extract data
  • Nesting INDEX and MATCH Function
  • Combining & Consolidating Data
  • Create Hyperlink in a workbook
AUDIENCE

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.

PREREQUISITES
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 use and apply Database function
  • Able to use and apply Logical function (IF, AND)
  • Able to use and apply Statistical function (COUNTIF, AVERAGEIF, COUNTIFS, AVERAGEIFS)
  • Able to use and apply Mathematical function (SUMIF, SUMIFS)
  • Able to use and apply Lookup function (VLOOKUP)
  • Able to use Auto and Advanced Filter command
  • Able to validate data
  • Able to apply Conditional Formatting
  • Able to Protect worksheet and workbook
METHODOLOGY

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

Topic A: Subtotal and Outlining

  • Creating Subtotals
  • Displaying and Collapsing Levels
  • Using Automatic Outlining

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 A: Using the Advanced Function

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

Topic B: Linking, Consolidating, and Combining Data

  • Linking Workbooks
  • Consolidating Workbooks
  • Combining Worksheets

Topic A: Create a Trendline

  • Trendlines
  • Types of Trendlines
  • Adding a Trendline
  • The Format Trendline Task Pane

Topic B: Create Advanced Charts

  • Dual Axis Charts
  • Creating Custom Chart Templates