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

12-13 Jun, 2025, 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 20 years working experience as a trainer in the field of Information Technology. He has obtained ITIL Certification and also 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.

 

Venue Details

CONCORDE HOTEL KUALA LUMPUR
2 Jalan Sultan Ismail, 50250 Kuala Lumpur, Malaysia,
Phone : +603 2144 2200

https://kualalumpur.concorde hotelsresorts.com/

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