Microsoft Excel – Advanced (In Person) NEW DATE

Course Overview

Master advanced Excel techniques to build powerful, reliable, and scalable business solutions. This course is designed for experienced Excel users who need to move beyond standard formulas and basic analysis. It focuses on advanced formula construction, data control, structured datasets, and analytical tools used in real business environments. Learners will develop robust, maintainable spreadsheets that handle large volumes of data, enforce accuracy, and support confident decision-making. This course is essential for anyone responsible for complex Excel models, reports, or analysis.

Who Should Attend?

This course is suitable for:
• Experienced Excel users working regularly with formulas and functions
• Staff responsible for complex spreadsheets or large datasets
• Analysts, team leaders, and financial professionals
• Technical users who need accuracy, control, and scalability in Excel models

Required Knowledge:

Participants must already be comfortable with:
• Intermediate Excel functions such as IF, XLOOKUP, SUMIFS, and COUNTIFS
• Absolute and relative cell referencing
• Creating charts and applying standard formatting
• Navigating multi-sheet workbooks and linking cells
• Working with structured Excel Tables and formula syntax

What You Will Learn:

Advanced Formula Techniques
• Nest functions to solve complex calculation problems
• Build advanced logical tests using IF, AND, OR, and IFS
• Handle errors safely using IFERROR
• Work with Dynamic Arrays including UNIQUE, SORT, and FILTER
• Create and manage Named Ranges and Named Cells

Data Control and Validation
• Design spreadsheets with strong Data Validation rules
• Create dependent drop-down lists and dynamic selection controls
• Enforce data accuracy and consistency across workbooks

Importing and Transforming External Data
• Import data from external sources including TXT, CSV, and PDF files
• Perform basic data cleaning and transformation directly in Excel
• Refresh and update linked external datasets

Database Design with Tables
• Convert datasets into structured Excel Tables
• Use table-based formulas for automatic expansion and consistency
• Understand how Excel Tables support database-style design

PivotTables for Analysis
• Create and configure PivotTables correctly
• Manage field settings and value summarisation methods
• Group, filter, and analyse large datasets
• Create calculated fields within PivotTables
• Use slicers for interactive analysis
• Refresh and maintain PivotTable data sources

Introduction to Automation
• Record basic macros to automate repetitive tasks
• Assign macros to buttons for easier execution
• Understand when recorded macros are appropriate and when they are not

This course does not cover:
• Power BI or Power BI Desktop
• Power Pivot or data models
• DAX formulas
• Advanced VBA or macro programming
• Enterprise-level automation or custom Excel applications

Key Learning Outcomes

At the end of this training, you should know how to:
• Build complex formulas that are accurate and maintainable
• Control user input and protect data quality
• Work confidently with structured Tables and large datasets
• Analyse data effectively using PivotTables and slicers
• Import and manage external data sources
• Automate simple tasks safely using recorded macros

IMPORTANT INFORMATION