Excel Data Analysis Masterclass: Lookups, Logical Functions, Power Query & Pivot Table

20-21 Jul, 2026, Concorde Hotel Kuala Lumpur

Register Now
PDF Brochure

IPA Training is Registered with

Learn from the Best

Morgan holds a Degree in IT & Tech Management from UTM, he has 24 years working experience as a trainer in the field of Information Technology. He is a Microsoft Certified Power Bl Data Analyst & Microsoft Certified Office 365 Specialist & also obtained ITIL Certification. He is PSMB approved trainer (TTT). He has conducted various trainings. His key expertise are:

 

  • Conducting IT Training in the area of Basic Networking, PC Repairing, PC Troubleshooting & Microsoft Office Training
  • Conducting research on the latest development and requirement for training based on customer request Developing & Maintaining a sound marketing strategy
  • Ensure KPI and Customer Satisfaction are meet

 

As a qualified professional educator, he has personally designed and delivered many specialized training courses including the validation and management of over 10 other trainers, operating nationwide in Malaysia.
As a qualified professional educator, he has personally designed and delivered many specialized training courses including the validation and management of over 10 other trainers, operating nationwide in Malaysia. He's also a well sought after consultant and works on several key IT projects each year, with leading brands in the region.
He has trained in Provater Sectors, Insurance led companies, MNCs, Government Ministries & Government agencies, among them are :
•Thermos (M) Sdn Bhd
•Prime Minister Office (PMO)
•IKBN
•JKR
•PNS
•Bank Negara Malaysia
•Tenaga Nasional Bhd
•MAHSA University
•Ranhill
•Alam Flora
•REAL Education
•Sime Tyres
•Tawau Polytechnic
•Dasmesh International School


 

Venue Details

Concorde Hotel Kuala Lumpur
2 Jalan Sultan Ismail, 50250 Kuala Lumpur, Malaysia,

https://kualalumpur.concorde hotelsresorts.com/



Phone : 03 2144 2200

Contact us

Juliany,
03 2283 6109
juliany@ipa.com.my

Phoebe,
03 2283 6100
phoebe@ipa.com.my 

FOR CUSTOMISED IN-HOUSE TRAINING
Jane,
03 2283 6101
Jane@ipa.com.my

ADDRESS 
A-28-5, 28th Floor, Menara UOA Bangsar, 
No.5, Jalan Bangsar Utama 1, 
59000 Kuala Lumpur
www.ipa.com.my

FOCUSING ON
  • Session 1: Lookup and Logic Functions for Smarter Data Analysis
    • Module 1: Mastering Lookup Functions
    • Module 2: INDEX & MATCH
    • Module 3: Logical Functions for Business Rules
    • Module 4: Using IF with Other Functions
  • Session 2: Automation & Dynamic Reporting
    • Module 5: Power Query for Data Transformation
    • Module 6: Combining & Merging Multiple Tables
    • Module 7: Pivot Tables for Interactive Reporting
    • Module 8: Calculations in Pivot Table
INTRODUCTION

This 2-day hands-on course is designed for professionals who work extensively with Excel and want to master advanced data-handling techniques.

Participants will explore powerful Excel tools like lookup functions, logical operations, Power Query automation, and Pivot Table reporting to enhance data analysis, decision-making, and reporting efficiency.

LEARNING OBJECTIVES
  1. Using LOOKUP functions effectively to retrieve, match, and cross-reference data
  2. Applying logical formulas for decision-making scenarios
  3. Cleaning, transforming, and combining data using Power Query
  4. Summarizing and visualizing data dynamically with Pivot Tables
WHO SHOULD ATTEND
  • Executives, Analysts, and Professionals who handle large datasets in Excel
  • Admin and Operations Staff who need to automate reporting and data cleaning
  • Finance, HR, and Sales Personnel involved in tracking, validating, and summarizing
    business data
  • Anyone with Basic Excel knowledge seeking to upgrade to advanced data-handling
    and reporting techniques
METHODOLOGY
  • Guided Learning, group-paced, hands-on
    classroom training with real life examples for
    business applications.
  • Challenging Exercises are set based on practical applications for typical organizational functions
    such as HR, Admin, Production, Sales etc.
DAY 1
 

SESSION 1: LOOKUP AND LOGIC FUNCTIONS FOR SMARTER DATA ANALYSIS

9:00

MODULE 1: MASTERING LOOKUP FUNCTIONS

  • VLOOKUP vs HLOOKUP : When and Why
  • Using LOOKUP with multiple criteria
10.45

MODULE 2: INDEX & MATCH

  • INDEX & MATCH: Dynamic lookups beyond limitations
  • Approximate vs exact match scenarios
1:00 Lunch
2:00

MODULE 3: LOGICAL FUNCTIONS FOR BUSINESS RULES

  • IF, IFS, AND, OR: Building logical statements
  • Nested IFs and cleaner alternatives
3:45

MODULE 4: USING IF WITH OTHER FUNCTIONS

  • Integrating IF with other functions
  • Error handling with IFERROR and IFNA
  • Complex formula using IF
5:00 End of Day 1
DAY 2
 

SESSION 2: AUTOMATION & DYNAMIC REPORTING

9:00

MODULE 5: POWER QUERY FOR DATA TRANSFORMATION

  • Introduction to Power Query interface
  • Importing from multiple sources: Excel, CSV, Text
  • Cleaning data: split columns, handle blanks
10.45

MODULE 6: COMBINING & MERGING MULTIPLE TABLES

  • Append vs Merge Queries
  • Dynamic updates & refresh for recurring reports
1:00 Lunch
2:00

MODULE 7: PIVOT TABLES FOR INTERACTIVE REPORTING

  • Creating Pivot Tables from raw data
  • Grouping, sorting, and filtering for insight
3:45

MODULE 8: CALCULATIONS IN PIVOT TABLE

  • Calculated Fields and Value Field Settings
  • Slicers and Timelines for interactive dashboards
  • Common mistakes & troubleshooting
5:00 End of Course