Excel Course
Beginner-to-advanced Microsoft Excel training: formulas, data cleaning, pivot tables, charts and dashboards.
View course →
Advanced Excel training
Advanced Excel Course
Replace manual, error-prone reporting with robust formulas, dynamic arrays, Power Query and automated dashboards.
- Hands-on, project-based
- Trainer feedback on every module
- Free counselling & demo on request
- Format
- Live, instructor-led
- Duration
- Shared at counselling
- Fees
- Shared on enquiry
- Level
- Intermediate to advanced
Advanced Excel Course overview
This course is for people who already build Excel reports and want them to be faster, safer and easier to maintain. The focus is on removing the fragile parts of a workbook: hard-coded ranges, nested IFs nobody can read, lookups that break when a column moves.
You learn modern functions (XLOOKUP, FILTER, SORT, UNIQUE, LET), multi-condition aggregation, advanced pivot techniques and the Power Query / Power Pivot pair that bridges Excel and Power BI. If you are still learning the basics, begin with the Excel course.
Who this course is for
- Excel users who build recurring reports every week or month
- MIS, finance, FP&A, operations and sales-ops professionals
- Analysts preparing to move on to Power BI
- Anyone who inherits large, complicated workbooks
Prerequisites
- Comfort with formulas, absolute references and basic pivot tables
- Microsoft 365 or Excel 2021 for the dynamic-array topics
Advanced Excel topics we cover
XLOOKUP, INDEX MATCH and multi-criteria lookups
Return values left or right, handle missing items gracefully and look up on two or more conditions.
SUMIFS, COUNTIFS and IFS
Replace helper columns with single-formula aggregations across several conditions.
Dynamic arrays
FILTER, SORT, UNIQUE, SEQUENCE and LET so a report updates itself when data grows.
Pivot tables and pivot charts
Calculated fields, show-values-as, grouping, slicers and GETPIVOTDATA.
Power Query and Power Pivot
Repeatable cleaning, merges and appends, a Data Model and your first DAX measures.
Validation, conditional formatting and automation
Input controls, rule-based highlights and macros for repetitive steps.
What you will learn
- Choose between XLOOKUP and INDEX MATCH and know why
- Build dynamic reports with FILTER, UNIQUE, SORT and LET
- Aggregate across many conditions with SUMIFS, COUNTIFS and IFS
- Create advanced pivot analyses and linked dashboards
- Automate import and cleaning with Power Query
- Model multiple tables with Power Pivot and write basic DAX
- Audit and document workbooks so others can maintain them
Tools covered
- XLOOKUP
- INDEX MATCH
- Dynamic arrays
- LET
- PivotTables
- Power Query
- Power Pivot
- Data validation
- Macros
Practical projects
Self-updating sales report. A report driven by UNIQUE and FILTER that adds new regions automatically when data changes.
Monthly consolidation with Power Query. Combine twelve monthly files from a folder into one model with one refresh.
Interactive management dashboard. Pivot-driven KPIs and slicers on a Data Model with DAX measures.
Detailed curriculum
The program is taught module by module. Each module ends with a hands-on exercise built on business-style data.
Module 1Advanced formula design
- Nested logic, IFS and SWITCH
- Named ranges and structured references
- LET for readable formulas
Module 2Lookups and reference functions
- XLOOKUP in depth, wildcard and approximate matches
- INDEX MATCH, two-way lookups
- Handling duplicates and missing data
Module 3Conditional aggregation
- SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS
- SUMPRODUCT for weighted calculations
Module 4Dynamic arrays
- FILTER, SORT, SORTBY, UNIQUE, SEQUENCE
- Spill ranges and referencing them safely
Module 5Advanced pivot tables and charts
- Calculated fields and items, show values as
- Slicers, timelines, pivot chart design
Module 6Power Query
- Import from files, folders and the web
- Merge, append, unpivot, custom columns
- Refresh and error handling
Module 7Power Pivot and DAX basics
- Data Model and relationships
- Measures vs calculated columns
- CALCULATE and time intelligence introduction
Module 8Dashboards, validation and automation
- Dashboard layout and design rules
- Data validation, conditional formatting
- Macro recording and editing
Module 9Capstone
- End-to-end reporting solution reviewed by the trainer
Learning methodology
Every concept is taught on a working file. You see the business question first - for example, "which regions missed target last quarter?" - and then build the formula, query or measure that answers it.
Each module closes with an exercise you complete yourself, followed by trainer feedback. Mistakes are part of the process: we cover the errors people actually hit, such as text-formatted numbers, broken lookups and wrong relationship direction.
Training files and practice datasets are provided so you can repeat every exercise after class.
Training format, batches and certification
Format. Instructor-led, practical sessions. Online, classroom and hybrid options are confirmed when you speak to us.
Batches. Upcoming batch dates are shared during counselling so you get current information. Ask for weekday, weekend or fast-track availability.
Certification. You receive a CharterPeak course completion certificate on completing the program and its assessments. This is not a Microsoft certification. If you want to prepare for a Microsoft exam such as PL-300, tell us and we will map the syllabus to the official skills outline.
Your trainers
Trainer names, backgrounds and areas taught are listed on our trainers page. Ask for a short demo class or a call with the trainer before you enrol.
Sample lesson: from nested IF to a lookup table
A pricing sheet uses a seven-level nested IF that fails when a new tier is added. We rebuild it as a small tier table with XLOOKUP in approximate-match mode, test the boundary values, and document the table so a colleague can change a rate without touching a formula.
Career applications
- MIS and reporting analyst
- FP&A and finance analyst support
- Business analyst and operations analyst roles
- Automation of recurring finance, HR and sales reports
- Preparation for advanced Excel roles and Power BI
Continue your learning path
- Apply these skills in Excel data analysis
- Build presentation-grade views in the Excel dashboard course
- Go deeper on Power Query
- Move to Power BI or the full Excel + Power BI program
Frequently asked questions
How advanced is "advanced"?
You should already use formulas, absolute references and pivot tables. We start with a short diagnostic exercise so the first session is pitched correctly.
Do I need Excel 365?
Dynamic arrays and XLOOKUP need Microsoft 365 or Excel 2021 and later. Older versions can follow most of the course; we point out which features differ.
Is VBA included?
The course covers recorded macros and light editing. A deeper VBA programming syllabus is a separate topic - ask us at counselling if you need it.
What is the difference between this and the Excel data analysis course?
This course is about technique - formulas, arrays, Power Query, automation. The data analysis course is about the analytical process: framing questions, profiling data and drawing conclusions.
What is the duration of the Advanced Excel Course?
Duration depends on the batch (weekday, weekend or fast-track). We confirm the schedule and total hours when you speak to us, so you get current information rather than a fixed number on a web page.
Who can join the Advanced Excel Course?
Students, graduates, working professionals and career changers. The prerequisites are listed on this page; if you are unsure, book a free counselling call and we will point you to the right starting course.
Is the training practical?
Yes. Each module is taught on working files with exercises you complete yourself, followed by trainer feedback and project work.
Is the training online or in a classroom?
Delivery options depend on the batch. Ask about current online, offline and hybrid availability when you enquire.
Is certification provided?
You receive a CharterPeak completion certificate after finishing the program and its assessments. It is not a Microsoft certification and we do not promise exam results.
What fees and payment options apply?
Fees depend on the batch and format and are shared on enquiry. See our refund policy before you enrol.
Related courses
Excel Data Analysis Course
The analytical process in Excel: frame the question, clean and profile data, analyse with pivots and statistics, and present findings.
View course →Excel Dashboard Course
Design and build interactive Excel dashboards with pivots, slicers, KPIs and clear layout.
View course →Excel Power Query Course
Import, clean, merge and automate data preparation in Excel and reuse the same skills in Power BI.
View course →Excel + Power BI + Data Analytics
Our complete practical program: Excel, Power Query, Power Pivot, Power BI, DAX, dashboards, projects and analytics career preparation.
Explore the flagship program →Related learning resources
XLOOKUP in Excel: Syntax, Examples and Common Errors
How XLOOKUP works, how it differs from VLOOKUP, and how to fix the errors people hit most often.
Read guide →Power Query in Excel: Clean and Combine Data Automatically
Import, clean, merge and refresh data with Power Query - the same engine used in Power BI.
Read guide →Pivot Tables in Excel: A Practical Guide
Create, group and refresh pivot tables, and avoid the mistakes that produce wrong summaries.
Read guide →Ready to start?
Talk to us about your background and goals. We will recommend the right batch and starting point - no pressure.