In this advanced Excel course you learn to build complex analyses reliably and refresh regular reports in a few clicks, and it is aimed at users who already handle formulas and pivot tables confidently. You combine nested functions, work with dynamic arrays, prepare data with Power Query and build analyses that read new files by themselves. The focus is on troubleshooting, auditability and clean documentation. The examples come from controlling, sales and HR.
What you’ll take away.
- Build and audit nested formulas using IFS, LET and INDEX/MATCH
- Apply dynamic array functions such as FILTER, SORT and UNIQUE
- Merge and clean data from several files with Power Query
- Use pivot tables with a data model and relationships across several tables
- Safeguard recurring reports with refresh, documentation and error checks
Your training in detail
- Who is it for?
- Users from controlling, sales, HR and administration who regularly produce extensive reports.
- Prerequisites
- Confident use of formulas, SUMIFS, lookup functions and pivot tables. A current Excel version for Power Query and dynamic arrays.
- Format & planning
- Suggested format: 2 days (about 7 hours each) live online or on site by arrangement. Scope depends on prior knowledge.
Agenda: from understanding to application
Our independently developed agenda proposal. Focus areas, lab environment and scope are agreed before booking.
Formula workshop: IFS, AND/OR, LET and a look at LAMBDA, plus evaluating formulas and tracing errors with formula auditing.
Looking up and combining with XLOOKUP, INDEX and MATCH, including multiple criteria and approximate matches.
Dynamic arrays: FILTER, SORT, UNIQUE, SEQUENCE and spill ranges in reports.
Power Query: reading a folder of monthly files, unpivoting columns, appending and merging tables and setting types.
Data model and pivot tables across several tables, relationships, measures and date grouping.
Running reports reliably: table references, check cells, sheet protection, a documentation sheet and refresh at a click.
Suggested exercise: Build a monthly report that reads all files in a folder through Power Query, analyses them by cost centre and flags deviations with check cells.
Exams, licences & credentials
For Excel, Microsoft offers exams in its Microsoft Office Specialist programme, including a higher level. Attending the course alone awards no vendor certification. Exam fees and licences are only included if confirmed in the offer.
Vendor references & review date
Sources checked on 2026-11-03. These sources describe the technology or external exam requirements. They do not imply a partnership with NextNowa.
Your learning content
These objectives describe the training topic. The specific syllabus, duration, dates, trainer and price are agreed before booking.
Frequently asked questions
When am I ready for advanced Excel?
If you can build IF, SUMIFS, a lookup such as VLOOKUP and a pivot table without instructions, this course fits. If that still feels difficult, the beginner Excel course is the better start. A short conversation or a sample task helps with the assessment.
Power Query or formulas: which is better for recurring reports?
For reading and cleaning raw data Power Query is usually more robust, because the steps are saved and repeated for new files. Formulas suit calculations and key figures in the finished report. In the course you combine both in one analysis.
Is Excel still worthwhile now that Power BI exists?
Yes, for small and mid-sized analyses, ad hoc work and reports that colleagues continue to edit in spreadsheet form. Power BI is suitable when many people need interactive reports from large data volumes. The Power Query skills from this course help you in both tools.
Does Advanced Excel include a certificate?
For Excel, Microsoft offers exams in its Microsoft Office Specialist programme, including a higher level. Attending the course alone awards no vendor certification. Exam fees and licences are only included if confirmed in the offer.