This one-day training is aimed at experienced Excel users who want to sharpen their skills with advanced analysis techniques, complex formulas and the core functionality of Power Query. The focus is on immediate applicability and working efficiently with data.

Staff who already work with Excel at an advanced level and are comfortable with functions, pivot tables and data analysis. Participants will have completed the Advanced training or have equivalent experience.

Classroom training

1. Advanced data analysis

▪ Simulations and what-if analysis

▪ Creating and managing scenarios

▪ Goal Seek for specific outcomes

▪ Solver for optimisation problems

2. Formulas and functions

▪ Automatic creation of cell names (scoped)

▪ Array functions and dynamic array functions in complex calculations

3. Power Query core functionality

▪ Importing data from a range of sources (Excel, CSV, databases)

▪ Cleansing and transforming data

▪ Merging and editing tables

▪ Refreshing data automatically

4. Data management and presentation

▪ Restricting end-user range

▪ Comparing values across rows and columns

▪ Extracting unique values

▪ Applying slicers and table styles across multiple workbooks

▪ Tips & tricks for working efficiently at expert level

‍