Path 68 · The matching desk · Beginner
Explain every unmatched amount.
A guided, practical route through spreadsheet reconciliation.
What you will be able to do
Reconcile two transaction lists, identify missing and mismatched records and build a review sheet that preserves the original data.
Plan about 9h 22m in total. Reading and practice are editorial estimates; video runtimes come from public provider metadata. Your pace will vary.
Before you begin
Check your starting point
What you need
Basic spreadsheet formulas. Use Excel if available or a free spreadsheet with equivalent lookup and conditional functions.
The selected public provider lessons are free to view. The original case can be completed with paper, a text editor or freely available local tools. Paid exams, certificates, subscriptions and optional provider software are not required for this project.
Completing this WikiFree path does not award a provider certificate. Any provider assessment, account requirement or credential must be checked separately on its official page.
Three readiness questions
Can equal totals prove every item matches?
No. Offsetting errors can cancel.
What should happen to duplicate keys?
Flag them before relying on a single-result lookup.
Is an unmatched item automatically an error?
No. It may be timing or missing evidence.
Use a workable pace
At 5 hours per week, allow about 2 weeks. Start with Stage 1, continue in order and reserve 2h for the capstone. If a gate fails, use its repair step before advancing. At another weekly budget, divide 562 estimated minutes by your available weekly minutes and round up.
Keep a small evidence folder. A checked box means you reviewed your output; it is not a certificate or proof that a provider lesson was watched.
The working case
Explain the 25-unit difference
A fictional club compares its internal payment log with a bank export. Match by a unique reference after trimming spaces. Do not match by amount alone. The dates are outside this simplified exercise; all amounts are teaching units.
| Source | Reference | Amount |
|---|---|---|
| Ledger | P1 | 100 |
| Ledger | P2 | 150 |
| Ledger | P3 | 80 |
| Bank | P1 | 100 |
| Bank | P2 | 145 |
| Bank | P4 | 60 |
The table and explanations preserve the essential case information when the diagram is unavailable.
Your route
Five outputs, then a final handoff
- 01Define matching rulesReconciliation contract
About 2h 2m including assigned learning and practice. - 02Compare by referenceMatching table
About 2h 25m including assigned learning and practice. - 03Reconcile the totalsDifference bridge
About 1h including assigned learning and practice. - 04Investigate without inventingThree-item investigation log
About 55m including assigned learning and practice. - 05Make the process reusableReusable workbook and test note
About 1h including assigned learning and practice. - FINALCapstone reviewCombine the evidence into one inspectable handoff.
About 2h .
0 of 6 outputs checked. JavaScript adds browser-only progress saving.
The complete lesson and all downloads remain available. Use the printable project workbook to track your progress.
Use the minimum useful source material
The courses behind this route
Simon Sez IT
Excel Formulas and Functions: Free Course
Existing logical-function and advanced-lookup lessons support matching and exception classification; software-specific alternatives may differ.
Selected material only; this path does not claim completion of the entire provider course.
Course publication and selected public lesson metadata were checked on 13 October 2026. This is not a claim of full video playback, provider-course completion or permanent availability. Recheck the linked source if a lesson changes.
Learn, make, inspect
The five-stage workshop
Define matching rules
Video 56m · reading budget 0m · original practice about 1h 5m.
Decide the key, normalization rules, amount tolerance and duplicate policy before calculating. A lookup can silently choose the first of several duplicate keys. Preserve raw data on separate sheets and put normalized values in new columns. In this case the tolerance is exactly zero and each reference should be unique per source.
Watch these selected lessons
- Logical Functions In Excel Tutorial (IF, IFS, AND, OR, COUNTIF, SUMIF): Excel Formulas and Functions56:30
Do the work
Create raw sheets and a rule sheet with zero tolerance.
Save this output
Reconciliation contract
Pass the review gate
Neither source is edited in place and duplicate checks are present.
If you need another attempt
Count references before using them in a lookup.
Compare by reference
Video 1h · reading budget 0m · original practice about 1h 25m.
Build the union of references from both sources so one-sided items cannot disappear. For each reference, show presence, ledger amount, bank amount and bank-minus-ledger difference when both exist. Keep missing values distinct from a genuine zero amount. The reference calculation is independent of any particular spreadsheet brand.
Watch these selected lessons
Do the work
Create rows for P1, P2, P3 and P4 and classify each.
Save this output
Matching table
Pass the review gate
P1 matches, P2 differs by -5, P3 is ledger-only and P4 is bank-only.
If you need another attempt
Do not convert missing entries to zero before classifying presence.
Reconcile the totals
Video 0m · reading budget 0m · original practice about 1h .
The total difference must be explained by the classified items. A difference bridge is a useful control because row-level labels can otherwise look complete while omitting a transaction. Use a consistent sign convention and write it above the table. Compare both row counts and amounts.
Original application stage. Use your preceding outputs and the supplied case; there is no additional required provider lesson.
Do the work
Reconcile ledger total 330 with bank total 305 using the three exceptions.
Save this output
Difference bridge
Pass the review gate
-5 – 80 + 60 = -25, matching bank minus ledger.
If you need another attempt
Reverse signs only after defining which source is subtracted from which.
Investigate without inventing
Video 0m · reading budget 0m · original practice about 55m.
An exception is a request for evidence. Record the likely question, owner and next action, but do not mark a discrepancy resolved because a plausible story exists. A timing difference needs a dated supporting record; a fee needs evidence of the charge. This teaching case supplies no such external evidence.
Original application stage. Use your preceding outputs and the supplied case; there is no additional required provider lesson.
Do the work
Prepare an exception log with questions for P2, P3 and P4.
Save this output
Three-item investigation log
Pass the review gate
All exceptions remain open until the stated evidence is supplied.
If you need another attempt
Replace assumed explanations with evidence requests.
Make the process reusable
Video 0m · reading budget 0m · original practice about 1h .
A reusable workbook detects new rows, duplicate keys and changed totals without manual patches. Test a new matching reference and a duplicate. Explain any formula-range limits. Protecting a sheet can prevent accidental edits, but it does not replace a review of the logic or the evidence behind adjustments.
Original application stage. Use your preceding outputs and the supplied case; there is no additional required provider lesson.
Do the work
Add P5 with equal amounts to both sources, then inject a duplicate in a test copy.
Save this output
Reusable workbook and test note
Pass the review gate
P5 matches and the duplicate produces a visible warning.
If you need another attempt
Use a defined data range or table and recheck the count reconciliation.
Make the result inspectable
Your final project
Deliver untouched raw sheets, rules, matching table, difference bridge, exception log and two test cases. State which spreadsheet and functions you actually used; do not claim a macro or automated bank connection.
The ZIP includes synthetic input data, a project workbook, worked reference, evidence log, scoring rubric and case-specific materials. Code projects include source and clearly stated local execution requirements.
Use one consistent rubric
| Criterion | Maximum | Evidence |
|---|---|---|
| Evidence and correctness | 30 | Calculations, source use and results match the supplied case; limitations are honest. |
| Required artifacts | 30 | All five stage outputs and the capstone handoff are present and inspectable. |
| Reasoning and decisions | 20 | Choices, assumptions, alternatives and the case-specific critical condition are explained. |
| Reproducibility | 10 | A reviewer can follow the stated steps and distinguish expected from observed results. |
| Clear communication | 10 | Labels, units, structure and evidence references make the work easy to review. |
Score at least 80/100, supply every required artifact, and satisfy the path-specific critical condition. Rework missing evidence before marking complete.
The critical condition
No unexplained difference is hidden in an adjustment row and missing values remain distinguishable from zero.
Compare with a worked reference
Ledger total is 100 + 150 + 80 = 330. Bank total is 100 + 145 + 60 = 305. The difference of −25 is explained by P2 (−5), missing bank P3 (−80) and bank-only P4 (+60). The supplied CSVs and reference output can be used in a free spreadsheet.
Know the boundary
This is a synthetic reconciliation exercise, not a bank integration or an audited financial record.
Questions before you finish
Do I need to finish every source course?
No. This path names selected lessons or reading sections. Complete the full provider course only if it supports your wider goal. The path does not claim equivalence to that course.
How do I know when to advance?
Use the output and review gate in each stage. If evidence is missing or a result conflicts with the case, follow the repair step and retry before advancing.
Can I do the project without paid software?
The selected public provider lessons are free to view. The original case can be completed with paper, a text editor or freely available local tools. Paid exams, certificates, subscriptions and optional provider software are not required for this project.
What does my checklist prove?
It records your own review in this browser only. It does not verify viewing, skill, a provider credential or a real-world deployment.
What if a lesson or tool changes?
Use the linked course and provider page to inspect current availability. Record the change and revisit the relevant assignment; do not invent a replacement lesson or claim an unavailable activity was completed.
Questions & discussion
Share a useful question or correction. Comments appear after moderation. Please avoid personal or sensitive information.