Microsoft Excel 2019 Advance

0

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

Get To Know The Trainer

Private Profile

 

Chat with us LIVE to get a

FREE QUOTATION!

COURSE INCLUDES

  • 2 Days - Public Class
  • 18 - 19 Nov 2020
    16 - 17 Dec 2020
  • 9am - 5pm
  • Training Centre (Petaling Jaya)

COURSE BENEFITS

  • e-Certificate of Completion
  • Training e-Manual
  • Lunch + Tea Provided (Halal)
  • PC & Software Provided
  • HRDF Certified & Claimable

ALL IN FROM ONLY

RM499/PAX

*T&C Applies

PARTICIPANT REVIEWS FOR THIS COURSE

What Other People Bought

LOGIN

Login with your social account

or

Don’t have an account with us?

FREE FOR LIFE

Sign Up to Bookmark your Favourite Course
or Request for a Quotation instantly

Sign in with your social account

or

By signing up, you agree to our Terms of Use and Privacy Policy

Have an account with us already?