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

  • Return order_id, date, region, name, amount in that order, sorted by numeric order_id. Amounts are integer whole dollars.
  • For each order_id, collapse byte-for-byte identical records to one. If any source field disagrees within an ID, exclude every record for that ID; never choose a maximum amount.
  • Accept only real calendar dates written YYYY/MM/DD. Exclude ambiguous or impossible dates. Convert accepted dates to YYYY-MM-DD.
  • An empty region becomes Unknown. Trim outer spaces and collapse repeated spaces in names. Preserve numeric zero amounts.

Use the complete project source data in the course download. Every output check must pass.

order_id,date,region,name,amount
1000,2026-09-01,Unknown,Client 1,0
1001,2026-09-02,West,Client 2,28
1002,2026-09-03,North,Client 3,31
1003,2026-09-04,East,Client 4,34
1004,2026-09-05,West,Client 5,37
1005,2026-09-06,North,Client 6,40
1006,2026-09-07,East,Client 7,43
1007,2026-09-08,West,Client 8,46
1009,2026-09-10,Unknown,Client 10,52
1010,2026-09-11,West,Client 11,0
1011,2026-09-12,North,Client 12,58
1012,2026-09-13,East,Client 13,61
1013,2026-09-14,West,Client 14,64
1014,2026-09-15,North,Client 15,67
1015,2026-09-16,East,Client 16,70
1016,2026-09-17,West,Client 17,73
1017,2026-09-18,North,Client 18,76
1018,2026-09-19,Unknown,Client 19,79
1019,2026-09-20,West,Client 20,82
1020,2026-09-21,North,Client 21,0
1021,2026-09-22,East,Client 22,88
1022,2026-09-23,West,Client 23,91
1023,2026-09-24,North,Client 24,94
1024,2026-09-25,East,Client 25,97
1025,2026-09-26,West,Client 26,100
1026,2026-09-27,North,Client 27,103
1027,2026-09-28,Unknown,Client 28,106
1028,2026-09-01,West,Client 29,109
1029,2026-09-02,North,Client 30,112
1030,2026-09-03,East,Client 31,0
1031,2026-09-04,West,Client 32,118
1032,2026-09-05,North,Client 33,121
1033,2026-09-06,East,Client 34,124
1034,2026-09-07,West,Client 35,127
1035,2026-09-08,North,Client 36,130
1036,2026-09-09,Unknown,Client 37,133
1037,2026-09-10,West,Client 38,136
1038,2026-09-11,North,Client 39,139
1039,2026-09-12,East,Client 40,142
1040,2026-09-13,West,Client 41,0
1041,2026-09-14,North,Client 42,148
1042,2026-09-15,East,Client 43,151
1043,2026-09-16,West,Client 44,154
1044,2026-09-17,North,Client 45,157
1045,2026-09-18,Unknown,Client 46,160
1046,2026-09-19,West,Client 47,163
1047,2026-09-20,North,Client 48,166
1048,2026-09-21,East,Client 49,169
1049,2026-09-22,West,Client 50,172
1050,2026-09-23,North,Client 51,0
1051,2026-09-24,East,Client 52,178
1052,2026-09-25,West,Client 53,181
1053,2026-09-26,North,Client 54,184
1054,2026-09-27,Unknown,Client 55,187
1055,2026-09-28,West,Client 56,190
1056,2026-09-01,North,Client 57,193
1057,2026-09-02,East,Client 58,196
1058,2026-09-03,West,Client 59,199
1059,2026-09-04,North,Client 60,202

Related public objectives: https://support.microsoft.com/en-us/excel/top-ten-ways-to-clean-your-data.

Independent original materials. CC BY 4.0.