Global Learning for Operational Leaders
Professional training course
Advanced Excel: Functions, Power Query and Power Pivots
The "Advanced Excel: Functions, Power Query, and Power Pivots" training course delves into advanced, and most recently a... The outline covers Advanced Excel Functions and Features, Advanced Techniques in Creating and Cu...
Introduction
Course overview
Why Attend
The "Advanced Excel: Functions, Power Query, and Power Pivots" training course delves into advanced, and most recently added, Excel functions like XLOOKUP, TEXTJOIN, TEXTSPLIT, UNIQUE, SORT, and FILTER, enabling you to streamline your data tasks with ease. The course will cover Power Query to import and transform data from various sources, such as Excel files, folders, PDFs, and websites, preparing it for further analysis and reporting. Additionally, the course introduces Power Pivots, which teaches you how to create sophisticated data models, establish relationships between tables, and craft decisive DAX measures for complex calculations. This course is designed to elevate your Excel skills, enhance your productivity, and provide you with the tools needed for advanced data analysis and reporting.
Course Methodology
This is a hands-on course with practical applications using Microsoft Excel 2021 or Office 365 throughout the five days. All examples, exercises, and cases are practiced in Excel.
Course Objectives
By the end of the course, participants will be able to:
- Apply key Excel functions to prepare data for analysis and reporting
- Utilize pivot tables functions and calculations to generate a set of management and business analysis reports
- Utilize Power Query to clean up and prepare data for reporting
- Report and analyze big data using Power Pivots
- Utilize Excel automation solutions to speed up work
Target Audience
Accountants, senior and junior accountants, business analysts, accounting and finance professionals, business analysts, research professionals, and staff from any function who need to master and upgrade their skills in Excel functions, pivot tables, Power Query, and Power Pivots and work with big data analysis.
Target Competencies
- Excel functions
- Power Query
- Practicing pivot tables
- Utilizing Power Pivot
- Reporting
- Analyzing business data
What you will achieve
Learning objectives
- Apply key Excel functions to prepare data for analysis and reporting
- Utilize pivot tables functions and calculations to generate a set of management and business analysis reports
- Utilize Power Query to clean up and prepare data for reporting
- Report and analyze big data using Power Pivots
- Utilize Excel automation solutions to speed up work
Who should attend
Target audience
- Accountants, senior and junior accountants, business analysts, accounting and finance professionals, business analysts, research professionals, and staff from any function who need to master and upgrade their skills in Excel functions, pivot tables, Power Query, and Power Pivots and work with big data analysis.
- Target Competencies
- Excel functions
- Power Query
- Practicing pivot tables
- Utilizing Power Pivot
- Reporting
- Analyzing business data
Methodology
Learning approach
- This is a hands-on course with practical applications using Microsoft Excel 2021 or Office 365 throughout the five days. All examples, exercises, and cases are practiced in Excel.
Course content
Course outline and key learning areas
Module 1
Advanced Excel Functions and Features
- Table tool
- Naming cells and ranges
- Data validation using custom formulas
- Conditional formatting using formulas
- Functions:
- Text functions: TextSplit, TextJoin, TextAfter, TextBefore
- Trim, Clean, Find/Search, Len
- IF, IFS, and using AND & OR
- CountIF, SUMIF, and AverageIF
- XLookup and Index & Match
Module 2
Advanced Techniques in Creating and Customizing Pivot Tables
- Number and cell format
- Report layout
- Grouping and un-grouping fields
- Default and customized sorting and filtering
- Sorting using a custom list
- Creating a calculated field
- Pivot charts
- Filtering using slicers and timelines
- Slicer settings and connections
- Customizing reports using the GetPivotData option
Module 3
Power Query: A Must-have Skill
- Get and transform: Link your Excel to external other data sources
- Excel files
- Text files
- Web
- Folder: all files and latest file
- Power Query to transform and clean up data:
- Naming, merging, splitting and removing columns
- Filtering rows
- Fill, Replace, Clean, Trim, and Format
- Adding Date columns
Module 4
Power Pivot and the Data Model
- Benefits and drawbacks of PowerPivot
- Merging data from multiple tables using relationships
- Creating better calculations using the DAX Formulas
- Using DAX to create calculated fields
- Formatting using DAX measures
- Date DAX measures
- Calculate and Related functions
Module 5
Excel Automation Tools
- Data types
- Currencies
- Geography
- Organization
- Stocks
- Automate routine tasks with office scripts (e.g., formatting sheets)
- Practical examples of using Macros
FAQ
Frequently asked questions
What does Advanced Excel: Functions, Power Query and Power Pivots cover?
This course covers Accounting and Finance through a structured five-day outline focused on practical application, discussion, and implementation planning.
When is the next available session?
The next scheduled session starts on 11 - 15 May 2026, with additional classroom dates and mirrored Online / Live options listed in the course schedules section.
Who should attend this course?
Accountants, senior and junior accountants, business analysts, accounting and finance professionals, business analysts, research professionals, and staff from any function who need to master and upgrade their skills in Excel functions, pivot tables, Power Query, and Power Pivots and work with big data analysis., Target Competencies, Excel functions
How can I register for a session?
Use any Register button next to the available course dates to open the participant registration page and submit your booking request for the selected session.
Is this course available online as well as classroom-based?
Yes. The course detail page includes both classroom sessions and Online / Live sessions, with online options aligned to the same course dates for easier planning.
Where are classroom sessions delivered?
Current classroom venues include Kuala lumpur, Barcelona, London, Munich, Amsterdam, Istanbul, Rome.
Still Have Questions?
Contact the academy team for course details, delivery options, and delegate guidance.
Related training courses
Continue building your improvement capability
Quick request
Send quick request
Send a fast enquiry about this course and the academy team will get back to you.