Excel Level 3 - Advanced

This intensive Advanced Excel course is designed for experienced users who want to elevate their skills in data analysis, formula construction, and structured spreadsheet development.

By the end of the course, participants will:
• Master complex, nested formulas for robust data analysis
• Learn how to design and manage large databases with built-in validation and controls
• Work with PivotTables, dynamic arrays, and data automation techniques
• Gain confidence importing and transforming external data sources
• Explore best practices for building scalable, collaborative Excel workbooks
This course lays the foundation for participants to continue their Excel journey into data modelling, automation, and BI tools such as Power Query, Power Query and Power BI.
________________________________________
Who Should Attend?
This course is intended for:
• Experienced Excel users working regularly with functions and formulas
• Staff who need to manage complex spreadsheets with large data volumes
• Analysts, team leaders, financial professionals, and technical users
Required Knowledge:
Participants must already be comfortable with:
• Intermediate Excel functions (IF, XLOOKUP, SUMIFS, COUNTIFS)
• Absolute vs. Relative cell referencing
• Creating charts and using standard formatting
• Navigating multi-sheet workbooks and linking cells
• Understanding structured Tables and working with Excel's formula syntax

Delivery Format
This is a classroom-based course. Each participant will have access to a fully equipped workstation with Microsoft Excel installed. The instructor will guide the course using live demonstrations, practical exercises, and real-time feedback.
Training Environment Includes:
• Individual PCs with Microsoft Excel (Office 365 or Excel 2021 or later)
• Course materials and online notes
• Comfortable classroom setting conducive to learning
Participants are not required to bring their own laptop.
________________________________________
Course Outline
This course is highly practical and formula-focused. The following topics are included.
Where time constraints apply, all content will be provided in accompanying course notes.
Note: Power BI, Power Pivot, and VBA/Macro programming are not covered in this course and are addressed in specialist or higher-level modules.
________________________________________
Advanced Formula Techniques:
• Nesting functions (e.g., XLOOKUP inside IF, etc.)
• Building complex logical conditions using IF, AND, OR, IFS
• Error handling with IFERROR
• Working with Dynamic Arrays (UNIQUE, SORT, FILTER)
• Creating and managing Named Ranges and Named Cells
________________________________________
Data Control & Validation:
• Designing robust spreadsheets with Data Validation rules
• Creating dependent dropdowns and dynamic selection lists
• Enforcing data integrity and consistency across workbooks
________________________________________
Importing & Transforming External Data:
• Importing structured and semi-structured data from:
o .TXT, .CSV, .PDF sources
• Basic data cleaning and transformation directly within Excel
• Refreshing and updating linked datasets
________________________________________
Database Design with Tables:
• Converting datasets to structured Excel Tables
• Applying table-based formulas for dynamic ranges
• Understanding the relationship between spreadsheets and databases in Excel
________________________________________
PivotTables for Analysis:
• Creating and configuring PivotTables
• Managing Field Settings and Value Summarisation
• Grouping and Filtering data
• Inserting Calculated Fields within PivotTables
• Using Slicers for interactive data filtering
• Refreshing and maintaining PivotTable data sources
________________________________________
Intro to Automation:
• Recording a basic macro
• Assigning a macro to a button for task automation
• Understanding the limitations and appropriate use of recorded macros
________________________________________
Learning Outcomes
By the end of this course, learners will be able to:
• Create and troubleshoot advanced formulas
• Design intelligent spreadsheets that minimise risk and maximise reuse
• Use PivotTables and slicers to generate meaningful insights
• Enforce standards using validation controls and dropdowns
• Automate repetitive tasks using simple macros
• Import, clean, and manage data from external sources
• Work confidently as an advanced Excel user in any business context
________________________________________
Progression Opportunities
This course acts as a bridge to specialist Excel and data-focused tools:
• Excel Power Tools Workshop: Power Query, Power Pivot, Power BI
• Data Analysis with Excel: Statistical functions, dashboards, and business modelling