Clean and join data
Standardize messy inputs and connect tables without silently losing records.
Pivot Table Excel Tutorial
From Kevin Stratvert. Watch here or open it on YouTube .
A pivot summarizes what it receives; use the video's field list to notice how dirty categories or duplicated joined rows would silently distort the result.What this lesson is really solving.
Never overwrite raw data without a reproducible trail. Profile missingness and duplicates, standardize text and dates, choose a stable join key, and test unmatched or multiply matched records. Similar names are not safe identifiers.
Understand the idea before touching the steps.
Joins depend on keys; if the key is inconsistent or duplicated, the result inherits the problem.
Follow these steps in order.
Take the action in each step; then use the deliverable below to prove the lesson is finished.
- 1
Profile blanks, duplicates, spaces, case, date formats, number-as-text fields, and impossible values.
- 2
Preserve the raw data and perform cleaning in reproducible helper columns or query steps.
- 3
Create or verify a stable key before using XLOOKUP, INDEX/MATCH, or a query merge.
- 4
Count rows and unmatched keys before and after the join to detect lost or multiplied records.
See the standard in context.
A customer table joins to orders by Customer ID, not company name. The analyst trims stray spaces, preserves leading zeros, flags three order IDs with no customer match, and catches one duplicated customer key that would otherwise double revenue.
Inspect before you move on.
- Raw input remains unchanged and refreshable.
- Unmatched, duplicate, and many-to-many join results are counted.
- Row counts and totals are reconciled before and after transformation.
Your deliverable
A repeatable cleaning log and reconciliation table for a two-source join.
Watch for this
Using names as unique identifiers when spelling, spacing, or duplicate people can change the match.
Prove it—don’t just recognize it.
You can report exactly how many records matched, did not match, or duplicated—and why.
Quick knowledge check
Answer from the lesson—not from confidence alone. Score at least 2 of 3 to unlock completion.
This curriculum-aligned check is scored automatically and stored with your account when signed in. It is an objective learning signal, but it has not yet been independently validated as a standardized assessment.
Tools, templates, and references
Pass the knowledge check above first.
Completion unlocks after a score of 2 out of 3. Then confirm that you produced the lesson deliverable.
Go to the knowledge check