About this course
Advanced Excel is for people who already use Excel regularly and want to work faster and more accurately with larger amounts of data. Participants learn modern lookup and array functions, summarise data with PivotTables, clean and combine data with Power Query and automate repetitive steps. Exercises are based on real reporting and analysis tasks, and can use your own sample data on request.
Who it's for
- Regular Excel users who build reports or analyse data
- Finance, operations and sales teams
- Anyone who spends hours each week on repetitive spreadsheet work
What participants will learn
- Use XLOOKUP, INDEX and MATCH to combine data from different sources
- Work with dynamic array functions such as FILTER, SORT and UNIQUE
- Summarise and explore data with PivotTables and PivotCharts
- Clean, reshape and combine data with Power Query
- Validate data and protect workbooks from errors
- Run what-if analysis with Goal Seek and scenarios
- Record simple macros to automate repetitive tasks
Course outline
1.Advanced formulas
- XLOOKUP, INDEX and MATCH
- Logical and nested functions
- Dynamic arrays: FILTER, SORT and UNIQUE
- Formula auditing
2.Analysing data
- PivotTables and PivotCharts
- Slicers and timelines
- What-if analysis with Goal Seek and scenarios
3.Preparing data
- Importing data from files and other sources
- Cleaning and reshaping data with Power Query
- Combining data sets
4.Reliability and automation
- Data validation
- Protecting sheets and workbooks
- Recording and running simple macros
Before you start
Confident everyday use of Excel, including basic formulas. Excel essentials or equivalent experience is recommended.