Excel Advanced

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

________________________________________

Exclusions:

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
These topics are covered in specialist or higher-level courses.

________________________________________

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

________________________________________

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.

________________________________________

Recommended Progression Pathway

After completing this course, learners should consider:
• Excel Power Query – Data Transformation
• Excel Power Pivot and Data Modelling
• Advanced Excel Automation with VBA
• Microsoft Power BI – Data Analysis and Visualisation
These courses extend Excel into advanced analytics, automation, and business intelligence.

________________________________________

Learner Support

Participants will have indefinite online access to all practice files and notes at https://www.dti.ie. Email support will also be offered on items covered during this course at support@dti.ie.