Spreadsheet reconciliation: matching two lists and explaining the differences
For office, finance and operations staff who regularly compare two lists that should agree: bookings against payments, an invoice against an internal register, a stock list against a count. Prerequisite: the course on lookups, conditional totals and checking spreadsheet data, or equivalent comfort with COUNTIF, SUMIFS and XLOOKUP. The separate course on XLOOKUP, INDEX/MATCH and missing matches covers lookup formulas in depth; this one covers the reconciliation workflow around them: control totals, preparing keys, duplicates and many-to-one matches, classifying every difference, proving that your explanations add up, and writing a difference register someone else can act on. Formulas follow Microsoft's Excel documentation for Microsoft 365, Excel 2024 and Excel 2021.
- Level
- Intermediate
- Length
- About 85 minutes
- Contents
- 5 lessons · final exam
- Status
- Published · updated 10 Oct 2026
Skills you'll practise
- Prepare two lists for matching by agreeing a key, cleaning it and recording control counts and totals
- Identify duplicate and many-to-one keys with COUNTIF and conditional formatting before matching
- Classify every row as matched, amount difference, timing difference or missing using COUNTIF, SUMIFS and XLOOKUP
- Calculate that the explained differences add up to the gap between the two control totals
- Write a difference register entry that states the cause, evidence, owner and action for each difference
Course outline
- 1.Prepare two lists: agree a key, clean it and record control totalsLesson · 15 min
- 2.Find duplicate and many-to-one keys before you matchLesson · 14 min
- 3.Classify every row: matched, amount difference, timing or missingLesson · 16 min
- 4.Prove that the explained differences add up to the gapLesson · 12 min
- 5.Write a difference register entry for each differenceLesson · 12 min
- 6.Spreadsheet reconciliation: matching two lists and explaining the differences: knowledge checkKnowledge check · 14 questions
- 7.Spreadsheet reconciliation: matching two lists and explaining the differences: practical exerciseKnowledge check · 1 question
- 8.Final exam10 questions · passing it completes the course, so people who already know the material can test out
Sources it draws on
The lessons and questions are written from these references, so learners can go back to the original.
- Microsoft Support: COUNTIF function
- Microsoft Support: COUNTIFS function
- Microsoft Support: SUMIFS function
- Microsoft Support: XLOOKUP function
- Microsoft Support: Filter for unique values or remove duplicate values (includes highlighting duplicates with conditional formatting)
- Microsoft Support: TRIM function
- Microsoft Support: EXACT function
- Microsoft Support: ROUND function
See it with your own jobs and topics
Tell us about your team and we'll walk you through setup, from choosing jobs to your first skills check.