EX19A(2) Microsoft Excel – Advanced

4.2

Course Objectives

  • Upon completion of this program, participants should be able to:
  • Create Subtotal Using the Subtotal 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
  • Summarizing & Analyzing with Pivot Table and Pivot Charts
  • Record and Run a Macro

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.

METHODOLOGY

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

Target 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.

Methodology

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

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

Get To Know The Trainer

Trainer Beatrice

 4.5

Beatrice has been in the training industry for the past 27 years being one of the pioneer trainers of Microsoft Office training programs. She has vast experience having trained many people from various corporate sectors. She is a certified Microsoft Office training consultant and is accredited by Pembangunan Sumber Manusia Berhad (PSMB/HRDF).

Chat with us LIVE to get a

FREE QUOTATION!

THIS PUBLIC COURSE INCLUDES

CLASS DETAILS

ALL IN FROM ONLY

RM0/PAX

*T&C Applies

4.2

  • 39%
  • 42%
  • 19%
  • 0%
  • 0%
  • LL
    Lim Ah Luan
    Manager / Supervisor, Accounting and Finance

    5

  • AP
    ADIBAH BINTI MOHAMED PADIL
    Junior Executive, Marketing / Design

    5

  • LY
    LEE QUAN YEN
    Executive, Accounting and Finance

    5

  • MI
    Mohd Fakhrurrazi Ibrahim
    Manager / Supervisor, Operations

    5

  • MQ
    Michelle Quah
    Executive, Engineering

    5

  • AS
    Ahmad Fikrul Khatami Bin Sukeman
    Junior Executive, Others

    5

  • NS
    Nur Jannah Binti Samat
    Executive, Procurement / Purchasing

    5

  • SK
    Surendran K.Subramaniam
    Executive, Operations

    5

  • SS
    Sanggheeta Uthaya Surian
    Executive, HR / Learning and Development

    5

  • WY
    Wong Yin Yee
    Executive, Procurement / Purchasing

    5

    Trainer is good and details.
  • WW
    Wong Jin Wai
    Junior Executive, Engineering

    5

  • LL
    Lim Wan Lynn
    Executive, Engineering

    5

  • TY
    TAI HOU YEEK
    Junior Executive, Engineering

    5

  • WS
    Wan Wing Seng
    Executive, Sales

    5

  • NN
    Nur Assila Md Nasser
    Executive, Operations

    4

  • NG
    Noor Atiqa Mohd Ghazali
    Executive, Others

    4

  • Lq
    Lim xiao qing
    Junior Executive, Accounting and Finance

    4

  • YL
    YEO CHEW LING
    Senior Executive, Accounting and Finance

    4

  • AA
    Adriza Mohd Ali
    Assistant Manager, Accounting and Finance

    4

  • LD
    Lane Marianeh Binti Duis
    Executive, IT Management

    4

  • TH
    TEH WAI HUEI
    Junior Executive, Accounting and Finance

    4

  • LL
    Loganaayagi A/P Letchumnan
    Executive, HR / Learning and Development

    4

  • CS
    Choong Lai Suan
    Senior Executive, Accounting and Finance

    4

  • LK
    Lim Lip Ken
    Senior Executive, Accounting and Finance

    4

  • Y(
    Yap Kin Sang (Johnny)
    Senior Executive, Accounting and Finance

    4

  • AA
    Asmaa Liyana Azmy
    Executive, Accounting and Finance

    4

  • NW
    NG YI WAH
    Executive, Procurement / Purchasing

    4

  • AV
    Adelyn Lee Ho Shwu Vern
    Executive, HR / Learning and Development

    4

    Great trainer and learnt alot
  • SL
    Sherlyn Lim
    Executive, Procurement / Purchasing

    4

  • HH
    Hasriyadi Bin Hassan
    Assistant Manager, Engineering

    3

  • MI
    Mariam Md Ismail
    Senior Executive, Sales

    3

  • MZ
    MUHAMAD FAIZAL BIN ZAKARIYAH
    Senior Executive, Engineering

    3

  • SW
    SEE WEI WEN
    Executive, Accounting and Finance

    3

  • Cy
    Chen see yng
    Executive, Accounting and Finance

    3

    Good
  • FB
    Fadzilah Buhari
    Senior Executive, Accounting and Finance

    3

  • Hk
    Haziqah khasmuni
    Executive, Engineering

    3

OF 4Next

Courses you may like

MEMBERS ONLY FEATURE

Kindly sign-up or log-in to get access.

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?