Details

Category: Statistical Software for Quantitative Analysis

Published: Friday, 07 August 2026 11:18

Written by: Matt Pye - HWD

Excel Advanced for Analysts

Overview

This one-day advanced Excel course is designed for analysts who are already confident with intermediate Excel functionality and want to develop more sophisticated techniques for analysing, interrogating and reporting data.

The course explores advanced formulas and functions for working with multiple criteria, including nested logical formulas, SUMIFS and XLOOKUP. Participants will develop their use of PivotTables to undertake percentage and comparison analysis, create calculated fields, group data and build more effective reporting dashboards.

The course also introduces tools for controlling and validating data, applying more advanced conditional formatting, and undertaking what-if and scenario analysis. Participants will use Goal Seek and Scenario Manager to explore potential outcomes and support forecasting and decision-making.

The final part of the course introduces Excel macros as a way of automating repetitive processes. Participants will learn how to record, run and edit macros, understand macro security and macro-enabled workbooks, and gain an introductory understanding of VBA and the use of buttons to run automated processes.

The course is suitable for analysts who are already familiar with intermediate Excel skills, including writing formulas and using basic conditional formatting.

Learning Outcomes

By the end of the course, participants will be able to:

• Build nested logical formulas using IF, AND and OR to analyse data against multiple criteria.

• Use data validation to control data entry and create appropriate validation rules and dropdown lists.

• Use XLOOKUP to retrieve information from other datasets and files, including exact and approximate matching.

• Create more sophisticated PivotTable analysis, including grouped data, percentages, comparisons, calculated fields and reporting dashboards.

• Apply advanced conditional formatting to highlight trends, exceptions, approaching dates and other important analytical information.

• Use SUMIFS to calculate totals based on multiple analytical criteria.

• Undertake what-if and scenario analysis using Goal Seek and Scenario Manager to explore targets, forecasts and alternative outcomes.

• Record, run and edit macros to automate repetitive Excel processes.

• Understand the basics of macro security, macro-enabled workbooks and the role of VBA in extending Excel automation.