Skill Workbench
← Back to course

Excel Data Cleanup: handouts

Choose Print / Save as PDF, then select Save as PDF in your browser. The learning copy hides answers.

Excel Data Cleanup

Original practice materials · Version 1.0.0 · Synthetic business data

1. Import without losing data

Keep quoted text and stable headers when importing CSV.

  1. Read the headers before changing any values.
  2. A comma inside quotes belongs to the same cell. A doubled quote represents a literal quote.
  3. Remove blank records, keep data rows and preserve notes.

Rescue a quoted sales file

  • Keep order_id, customer, note.
  • Remove only the empty record.
  • Keep commas and quotation marks in notes.
order_id,customer,note
1,Avery,"Desk, blue"
,,
2,Morgan,"He said ""ship"""

Reference answer

order_id,customer,note
1,Avery,"Desk, blue"
2,Morgan,"He said ""ship"""

A blank record can be removed. A quoted comma or quote is part of the note.

Preserve a multiline delivery note

  • Return ticket_id and note.
  • Preserve the two-line note.
  • Remove the blank row.
ticket_id,note
21,"Leave at door
Ring once"
22,"Call, then deliver"
,

Reference answer

ticket_id,note
21,"Leave at door
Ring once"
22,"Call, then deliver"

Do not split a quoted newline into a new record.

Extension: Try a note containing a newline; the CSV parser preserves it in the same cell.

2. Handle missing values

Fill only fields with an explicit business default.

  1. Distinguish a missing number from a real zero.
  2. For these exercises the policy sets missing region to Unknown.
  3. A missing amount becomes 0 only where the exercise explicitly says so.

Apply region defaults

  • Keep all rows and columns.
  • Set a missing region to Unknown.
  • Keep amount 0 as 0.
order_id,region,amount
1,East,120
2,,0
3,West,80

Reference answer

order_id,region,amount
1,East,120
2,Unknown,0
3,West,80

Only the missing region changes. Zero is a valid amount.

Prepare an explicitly defaulted quantity

  • Keep sku and quantity.
  • Business policy: missing quantity means no count recorded; use 0 for this exercise.
  • Preserve the existing zero.
sku,quantity
PEN,5
MUG,
BOOK,0

Reference answer

sku,quantity
PEN,5
MUG,0
BOOK,0

Use the stated policy, not an inferred rule for all datasets.

Extension: In real work ask for the missing-value policy before inventing defaults.

3. Deduplicate by a business key

Keep the first record for each order ID.

  1. Use order_id as the declared business key.
  2. Duplicate notes may differ; this does not make a duplicate order unique.
  3. Keep the first occurrence and preserve other orders.

Remove repeated order IDs

  • Keep order_id, amount, note.
  • Deduplicate on order_id, keeping the first record.
order_id,amount,note
1,120,first
1,120,retry
2,80,first

Reference answer

order_id,amount,note
1,120,first
2,80,first

Whole-row comparison misses the duplicate business key.

Deduplicate customer contacts

  • Keep the first record for each customer_id.
  • Two different customer IDs sharing an email are distinct.
customer_id,email
7,a@example.test
8,a@example.test
7,b@example.test

Reference answer

customer_id,email
7,a@example.test
8,a@example.test

Email is not the declared key. Keep customer 8.

Extension: If duplicate records disagree on money, flag them for review rather than silently taking a maximum.

4. Standardize dates and whitespace

Normalize declared date formats without guessing.

  1. The input dates use YYYY/MM/DD; convert separators to YYYY-MM-DD.
  2. Trim outer whitespace and repeated spaces in names.
  3. Never infer whether 03/04 means March 4 or April 3.

Normalize a shipping log

  • Input format is YYYY/MM/DD. Return YYYY-MM-DD.
  • Trim names and collapse repeated spaces.
  • Keep IDs and column order.
order_id,date,name
1,2026/09/03, Avery  Lee 
2,2026/09/04,Morgan

Reference answer

order_id,date,name
1,2026-09-03,Avery Lee
2,2026-09-04,Morgan

Convert only the declared format and preserve the name.

Reject an ambiguous date

  • Accepted format is YYYY-MM-DD.
  • Preserve raw_date. Mark other formats review.
  • Mark the ISO date valid.
record_id,raw_date,status
1,03/04/2026,
2,2026-09-05,

Reference answer

record_id,raw_date,status
1,03/04/2026,review
2,2026-09-05,valid

Do not choose an interpretation for an ambiguous date.

Extension: Store an error log for ambiguous or invalid dates; do not make a silent choice.

Final project

Handoff a clean order file

  • Deduplicate order_id keeping first.
  • Convert YYYY/MM/DD to YYYY-MM-DD.
  • Set missing region to Unknown; trim names; keep amount 0.
order_id,date,region,name,amount
1,2026-09-01,East,Avery,120
2,2026-09-02,Unknown,Morgan,0

Source objectives: https://support.microsoft.com/en-us/excel/top-ten-ways-to-clean-your-data. Independent original materials. CC BY 4.0.