Working with data
Lookups with VLOOKUP, XLOOKUP and INDEX with MATCH, sorting, filtering and Excel Tables, and conditional formatting and data validation.
About 3 hours of study
Learning objectives
- Look up values between tables
- Organise lists as Tables
- Highlight and validate data
Lessons
- 1
Lookups: VLOOKUP, XLOOKUP and INDEX with MATCH Intermediate
Finding values in a table: VLOOKUP and its pitfalls, XLOOKUP in Excel 2021 and Microsoft 365, the flexible INDEX and MATCH pair, exact and approximate matches, and handling values that are not found.
- 2
Sorting, filtering and Excel Tables Intermediate
Organising lists: sorting by one or more columns, filtering with AutoFilter, removing duplicates, and turning a range into an Excel Table with automatic formatting, structured references and a total row.
- 3
Conditional formatting and data validation Intermediate
Making a sheet react to its data and protecting what users can type: conditional formatting rules, colour scales and data bars, formula-based rules, drop-down lists and validation messages.