Course Overview
What You Will Learn
By the end of this course you will be able to build powerful spreadsheet solutions using advanced functions, macros, and Power Query. You will be confident with INDEX/MATCH, dynamic dashboards, and data model basics.
Use XLOOKUP, INDEX/MATCH and dynamic array formulas
Build and run macros to automate repetitive tasks
Transform and clean data using Power Query
Create professional dashboards with charts and slicers
Use advanced PivotTable techniques
Write complex nested formulas with confidence
Use dynamic array functions: FILTER, SORT, UNIQUE
Protect and audit complex workbooks
Curriculum
Course Content
1
Advanced Lookup and Array Formulas
- XLOOKUP — the modern replacement for VLOOKUP
- INDEX and MATCH for flexible lookups
- Dynamic array functions: FILTER, SORT, UNIQUE, SEQUENCE
- IFERROR and IFNA for error handling
- Complex nested formulas
- Practical exercises with real-world data
2
Macros and Basic Automation
- What macros are and when to use them
- Recording a macro step by step
- Running and editing recorded macros
- Assigning macros to buttons
- Introduction to the VBA editor
- Common automation tasks: formatting, sorting, copying
3
Power Query — Data Transformation
- What Power Query is and why it matters
- Connecting to Excel, CSV and other sources
- Removing duplicates, errors and blank rows
- Splitting columns and transforming data types
- Merging and appending queries
- Refreshing queries automatically
4
Dashboards and Advanced Charts
- Planning and structuring a dashboard
- Dynamic charts linked to PivotTables
- Combo charts, sparklines and data bars
- Using slicers and timelines for interactivity
- Formatting for a professional finish
- Protecting and sharing your dashboard
Next available: 14th May 2026
Ready to book your place?
From £245 +VAT • Max 10 delegates • Certificate included
Audience
Who Should Attend?
Intermediate Excel Users
Confident with VLOOKUP and PivotTables and ready to go further
Analysts and Reporting Professionals
Building complex reports and wanting to automate repetitive work
Finance and Data Teams
Working with large datasets needing Power Query and advanced formulas
Power Users
Self-taught in advanced features and wanting to fill gaps and formalise knowledge
Solid working knowledge of intermediate Excel is required for this advanced course.
This course starts from scratch and builds at a comfortable pace throughout the day.
Transformation
Before & After This Course
Before
- Spending hours on repetitive formatting and copying tasks
- Struggling with complex datasets and data cleaning
- Unable to build the dashboards and reports your role demands
After
- Automating repetitive tasks with macros in minutes
- Cleaning and transforming data with Power Query
- Building professional dashboards that update automatically
Questions & Answers
Frequently Asked Questions
Do I need any prior Excel experience?
Yes — solid working knowledge of Excel including VLOOKUP and pivot tables is required. If you need to build up to this level, please book the Intermediate course instead.
What equipment do I need?
A PC or laptop with Excel installed, a reliable internet connection, and a microphone and headphones. A webcam is optional but encouraged. Most modern laptops have everything needed.
Is a certificate awarded?
Yes. You will receive a Certificate of Achievement on completion of the course.
How many people are in each session?
Sessions are limited to a maximum of 10 delegates to ensure you receive personalised attention and can ask questions freely throughout the day.
When will I receive joining instructions?
Joining instructions including the Zoom or Teams link will be sent 7 days before your course date.
Can I transfer to a different date?
Yes — we are as flexible as possible. We will always do our best to transfer you to a different date at no extra charge if your circumstances change.
What is your cancellation policy?
We will always work with you to find an alternative date if needed. If that is not possible, we offer a full refund in accordance with our Refund Policy, available in the About Us section of our website.
Is in-house Excel training available?
Yes. We offer tailored in-house Excel training delivered via private virtual classroom for groups of 4 or more. Contact us for a quote.
