Details

Category: Statistical Software for Quantitative Analysis

Published: Friday, 07 August 2026 11:15

Written by: Matt Pye - HWD

Excel Intermediate for Analysts

Overview

This practical one-day course is designed for analysts who are already familiar with Excel’s basic functionality and want to develop more efficient and sophisticated ways of analysing data and producing reports.

The course takes a balanced look at writing formulas, analysing data and presenting results, while introducing a range of techniques that can significantly improve the speed and reliability of analytical work. Participants will learn how to work across multiple worksheets and files, use more advanced functions, apply conditional formatting, and compare different datasets more effectively.

The course also introduces PivotTables, slicers and more advanced charts, enabling participants to summarise and present data clearly. Lookup functions including VLOOKUP and XLOOKUP are covered, helping participants retrieve and combine information from different sources. Throughout the course, practical tips and shortcuts are used to build confidence and improve efficiency.

Learning outcomes

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

• Use absolute cell references effectively when copying and extending formulas

• Understand the difference between relative and absolute references

• Group worksheets to enter and manage data across multiple sheets

• Use 3D formulas to calculate across multiple worksheets

• Create and manage links between tables, worksheets and separate Excel files

• Use Excel’s Function Library and Insert Function tools to find and build formulas

• Apply functions including COUNTA, COUNTBLANK and COUNTIFS

• Use logical IFS functions to test conditions and return different results

• Apply and manage conditional formatting rules to highlight important values, patterns and exceptions

• Compare worksheets and Excel files side by side

• Create and manage PivotTables to analyse and summarise data

• Control PivotTable layouts, values and number formatting

• Use slicers to filter and interrogate PivotTable reports interactively

• Add charts to PivotTable reports to communicate findings visually

• Create Combo charts to compare different types or series of data

• Create Waterfall charts to illustrate cumulative positive and negative changes

• Use VLOOKUP and XLOOKUP to retrieve information from other tables and files

• Understand the key differences between VLOOKUP and XLOOKUP and when each is appropriate

• Apply a range of time-saving techniques to work more efficiently with analytical spreadsheets

The source outline positions this as an intermediate course for users already comfortable with areas such as AutoSum, filters and basic formatting.