Microsoft Excel 2016/2019 Advanced

Better understanding of more advanced features of Excel.


2 days
All levels
0 lessons
0 quizzes
0 students

Course Info

This course covers the advance features of Excel. It is suitable for Excel users who wish to enhance their knowledge on more advanced formulas and functions. At the end of this course you will have better understanding of the more advanced features of Excel. You will be able to link and consolidate information from different Excel files, manipulate data lists using filtering, subtotals and pivot tables. You will also be able to use logical and lookup functions.

What Will I Learn From This Course?

Create formulas to link multiple worksheets

Consolidate data across multiple worksheets

Create custom list and data form

Filter data using Advanced Filter feature

Compute subtotals

Analyze data using Pivot Table

Analyze and lookup data using database functions

Record macros and assign macros to button


Completion of Microsoft Excel (Basic & Intermediate) course or equivalent knowledge

Course Outline for This Programme

  1. Creating Links
    i. Linking Sheets Within the Workbook
    ii. Linking to Other Workbooks
    iii. Amending Broken Links
  2. Using Data Consolidate
    i. Consolidate By Position
    ii. Consolidate By Category
  3. Consolidate Data Using Formulas content
  1. Tracing Formulas and Errors
    i. Trace cells that provide data to a formula (precedents)
    ii. Trace formulas that reference a particular cell (dependents)
  2. Error Checking
    i. Correct common formula errors one at a time
    ii. Correct an error value
  3. Watch Window
    i. Add cells to the Watch Window
    ii. Remove cells from the Watch Window
  4. Protecting Your Files and Worksheets
    i. Locking & Unlocking Cells
    ii. Setting Multiple Range Passwords
    iii. Protecting The Contents of a Worksheet
    iv. Protecting The Structure of a Workbook
    v. Encrypting Your Workbook
  1. Introducing Excel Tables
    i. Inserting a Table
    ii. Benefits of A Table
    iii. Removing A Table

  2. Sorting Data
    i. Performing A Quick Sort
    ii. Performing A Complex Sort
    iii. Using A Custom List
    iv. Sorting Based On Colors

  3. Entering Data Using Forms
    i. Customizing The Quick Access Toolbar
    ii. Entering New Data
    iii. Editing Data
    iv. Performing A Criteria Base Search


  4. Filtering Data
    i. Using Filters
    ii. Using Custom AutoFilter

  5. Advanced Filtering
    i. Using Multiple OR To Filter
    ii. Using Multiple AND To Filter
    iii. Using AND & OR To Filter
    iv. Using Wildcards To Filter

  6. Subtotals
    i. Adding Subtotals
    ii. Adjusting Views with Subtotals
    iii. Removing Subtotals
    iv. Performing Nested Subtotals

  7. Validating Data Entry
    i. Creating A Drop Down List
    ii. Creating A Customized Error Message
    iii. Controlling Values & Dates
    iv. Using Formulas to Control Data

  8. Importing Text Files
    i. When to Use Delimited
    ii. When to Use Fixed Width
    iii. Using Text To Column to manipulate data

  1. Introduction to PivotTables
    i. What Are PivotTables?
    ii. Creating a PivotTable
    iii. Customizing Your Report
    iv. Filtering Data
  2. Creating a Pivot Chart
    i. Inserting A Pivot Chart
    ii. Formatting & Adding Elements
    iii. Removing Buttons From A Pivot Chart
  1. Using the Function Library
  2. Introduction to Logical Functions
    i. IF Function
    ii. Nested IF function
  3. Introduction to LookUp Functions
    i. When to Use The Exact Match
    ii. When to User The Nearest Match
    iii. VLOOKUP
    iv. HLOOKUP
    v. Problems In VLOOKUP & HLOOKUP
  1. Creating Data Tables
    i. Creating A One Input Data table
    ii. Creating A Two Input Data table
  2. Using Goal Seek For Simple Problems
  3. Using Solver For Complex Problems
    i. Using Solver
    ii. Minimizing & Maximizing Models
    iii. Adding Constraints to Your Model
    iv. Saving Solver Solutions to the Scenario Manager
    v. Retrieving a Solution From the Scenario Manager
  1. Automating Tasks with Macros
    i. Turning On The Developer Tab
    ii. Viewing The Project Explorer
    iii. Recording a Macro
    iv. Running a Macro
    v. Editing a Macro
    vi. Assigning Macros to Buttons
    vii. Saving A Macro Enabled Workbook
  1. Keyboard access to the ribbon
  2. CTRL combination shortcut keys
  3. Function keys
  4. Other useful shortcut keys
Curriculum is empty

Academic Qualifications
  • Diploma in Accountancy
  • ACCA (Association of Chartered Certified Accountants), UK

Professional Certifications
  • Pembangunan Sumber Manusia Berhad (PSMB) Certified Trainer
  • Microsoft Certified Trainer (MCT)

Julian specialises in conducting MS Excel application courses & seminars for various companies, local & multinationals. He teaches practical on-the-job experience, which translates to real-life exercises for participants’ benefit. Julian conducts various IT courses using Word Processors, Spread sheet, Database, Multimedia & Programming applications