Microsoft Excel — Advanced
Move beyond the basics into the tools that automate real analysis work — advanced lookups, dynamic arrays, Power Query and macro-driven reporting.
What this course covers
After This Course, You'll Be Able To
From Faster Formulas to Live Dashboards
A practical, hands-on curriculum that builds from modern lookups and dynamic arrays through Power Query, macros and dashboard construction.
Advanced Lookups & Nested Logic
Retire slow, fragile VLOOKUP formulas for faster, more reliable lookups.
- XLOOKUP fundamentals — a more flexible, two-way lookup than VLOOKUP
- INDEX/MATCH combinations for lookups in any direction
- Nested IF, IFS and logical functions (AND/OR) for multi-condition decisions
- Error handling with IFERROR and IFNA in real formulas
Dynamic Arrays & Modern Excel Formulas
Formulas that spill and update automatically, without helper columns.
- FILTER, SORT and UNIQUE for spill-range formulas that update automatically
- SEQUENCE and array-based calculations without helper columns
- Combining dynamic array functions into live, self-updating reports
- Rebuilding common manual tasks as dynamic formulas
Power Query for Data Cleaning & Transformation
Stop rebuilding the same report by hand every month.
- Importing data from Excel, CSV and other common sources
- Cleaning and reshaping data — removing duplicates, splitting columns, unpivoting
- Merging and appending queries from multiple sources
- Refreshing queries so reports update without manual rework
Data Modelling Basics
An introduction to connecting related tables instead of one giant sheet.
- Structuring related tables for consistent analysis
- An introduction to relationships between tables inside Excel
- Building summary formulas across multiple linked tables
- Preparing a data model for reporting and dashboards
Macros & Introduction to VBA
Automate the repetitive steps you currently do by hand.
- Recording and running macros to automate repetitive tasks
- Reading and lightly editing recorded macro code
- Assigning macros to buttons for one-click workflows
- Good practice and safety when working with macros
Dashboard Construction
Bring formulas, PivotTables and charts together into one decision-ready view.
- Planning a dashboard layout around the questions it needs to answer
- Building KPI cards, charts and dynamic summaries in one view
- Using slicers and form controls for interactivity
- Finalising and presenting a polished, decision-ready dashboard
Built For You
Analysts & Finance Professionals
Replace slow manual lookups with XLOOKUP and INDEX/MATCH, and build reports that update themselves.
Report Owners & Power Users
Automate the recurring report you currently rebuild by hand each cycle, using Power Query and macros.
Managers & Team Leads
Build a live dashboard for your own team's data, without waiting on someone else to build it for you.
About HRD Corp claims
AISynergy's training programmes are designed to be claimable under the HRD Corp SBL-Khas scheme for registered Malaysian employers. We'll confirm this course's specific claim documentation and schedule when you enquire.
Ready to Automate Your Analysis Work?
Message us on WhatsApp for the schedule, delivery format and HRD Corp claim details for Microsoft Excel — Advanced.