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

Lessons

  1. 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. 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. 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.

Module practice

Module quiz

1. What should VLOOKUP's last argument be for an exact match?
2. Which shortcut turns filters on or off on Windows?
3. What happens when you type in the row just below an Excel Table?
4. Which lookup works in every Excel version and can return a column to the left?
5. Why does a formula rule use $D2 rather than D2 to colour whole rows?
6. Why can data validation not guarantee clean data?

‹ Logic, text and datesAnalysis and charts ›