Master the advanced capabilities of Microsoft Excel in this comprehensive course designed to transform your data management skills. Moving beyond basic spreadsheet entry, this course focuses on automation, complex logical reasoning, and professional-grade data analysis. You will learn how to handle large datasets with ease using modern Excel features like Dynamic Arrays and XLOOKUP, ensuring your work is both resilient and efficient.
A significant portion of this training is dedicated to Power Query, the industry-standard tool for automating data cleaning and transformation. You will discover how to connect to various external data sources and build self-refreshing reports that save hours of manual labour. Additionally, the course introduces advanced PivotTable techniques and What-If Analysis tools to help you extract meaningful business insights and create interactive, professional dashboards.
By the end of this course, you will be equipped to build sophisticated, automated workbooks that meet the high-speed demands of modern technical and business environments.
Perfect for: Users who are comfortable with Excel basics and want to specialise in automation, advanced logic, and big data analysis.
Course Content
Part 1: Intermediate Data Management
- Advanced Data Validation: Using custom formulas to restrict data entry and creating dynamic dependent drop-down lists.
- Conditional Formatting with Formulas: Using logical expressions to highlight trends, overdue dates, or variances automatically.
- Advanced PivotTables: Moving beyond basics to include Calculated Fields, Grouping (by date/number), and Slicers/Timelines for interactive reporting.
- Logical Functions: Master nested IF, IFS, AND, OR, and XOR to automate decision-making processes.
Part 2: Advanced Lookups and Logic
- The Power of XLOOKUP: Replacing older VLOOKUP/HLOOKUP methods for more resilient data retrieval.
- Dynamic Arrays & Spilling: Utilizing UNIQUE, SORT, FILTER, and SEQUENCE to create automatically expanding datasets.
- Text & Date Automation: Using functions like TEXTJOIN, LET, and complex Date/Time calculations for data cleaning.
- Handling Errors: Proactive error management using IFERROR and ISNA.
Part 3: Data Automation & Power Tools
- Mastering Power Query:
- Connecting to external data sources (Web, PDF, Folders).
- Data transformation: Unpivoting columns, splitting delimiters, and merging/appending multiple datasets.
- Automating the "Cleaning" process so it refreshes with one click.
- Financial & Statistical Analysis: Using PMT, XIRR, FORECAST.ETS, and the What-If Analysis toolset (Goal Seek and Scenario Manager).
- Introduction to Macros & VBA: Recording simple macros to automate repetitive tasks and understanding the basics of the VBA Editor.
- Workbook Security & Protection: Advanced protection of specific ranges, hidden formulas, and structural locking.
Course Dates, Prices & Enrolment
Scroll right for more details
| Delivery Method | Dates & Times | Hours | Price | Enrolment |
Online Training using Zoom
|
10 Jun 2026 - 12 Jun 2026
Wednesday & Friday
03:00 PM - 06:00 PM
BT
| 6-hour over 2-day |
| Enrol Now |
Online Training using Zoom
|
15 Jul 2026 - 17 Jul 2026
Wednesday & Friday
03:00 PM - 06:00 PM
BT
| 6-hour over 2-day |
| Enrol Now |
Online Training using Zoom
|
12 Aug 2026 - 14 Aug 2026
Wednesday & Friday
03:00 PM - 06:00 PM
BT
| 6-hour over 2-day |
| Enrol Now |
Price Calculator & Booking Request Form
Calculate prices for Corporate, 1-on-1 or group training and request a booking.
Do you have a special training requirement or unable to find any suitable training date? Please complete and submit the booking request form, if you want to:
-
book a course on different dates
-
book for a group of delegates
-
book corporate training
-
book a customised training
-
book a one-on-one training
The price person is less when you book a course for more people. You can find the price per person and the total cost by changing the values of the training hours and the number of people below: