Keeping a cleaning log

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. What is the main purpose of a cleaning log?

A To record each problem found and the fix applied, so changes can be explained and checked

B To store the final charts and graphs made from the data

2. Which set of columns would a useful cleaning log have?

A Problem, Fix, Rows affected

B Name, Age, Favourite colour

3. A student finds 4 blank cells in the Age column and fills them with 'Unknown'. Which log entry is best?

A Problem: fixed some stuff | Fix: cleaned | Rows affected: a few

B Problem: blank Age cells | Fix: filled with 'Unknown' | Rows affected: 4

4. You delete 3 duplicate rows from a survey spreadsheet. What should you do?

A Record the deletion in the cleaning log, including that 3 rows were removed

B Nothing, because deleted rows are gone so they don't need to be recorded

5. Why is it important to record deleted rows in a cleaning log?

A So the deleted rows come back automatically

B So others can see why the row count dropped and check the deletion was fair

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1Problem: 3 blank Age cells		AThe entry does not name the problem or the fix and gives no row count, so it cannot be checked.
2Rows affected: 12		BDescribes the data problem that was found in the dataset
3A log entry records the fix applied, so others know exactly what was done to the data		CShows how many rows were changed or removed by the fix
4Deleted rows must be recorded in the log, with the reason, so the change can be checked later		DProblem: duplicate names \| Fix: deleted rows \| Rows affected: 4
5Log: Fixed some data. Rows affected: ?		ERows were deleted but the log says 0 rows were affected, so the record is wrong and hides the lost data.
6Problem: duplicates \| Fix: deleted \| Rows affected: 0		FFix: used TRIM to remove extra spaces from column B

Definitions

cleaning log

Fill in the Blanks

Complete each line of code by filling in the missing parts.

Problem,Fix,_____________ Blank age,Added N/A,4

Total rows affected in the log: =___(C2:C6)

Problem: blank cells in column B =_______(B2:B30,"")

C4 holds the deleted rows from the log =COUNTA(A2:A30)_C4

Problem: extra spaces in names Fix: =____(A2)

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A Fix: 3 duplicate rows

B Fix: removed the extra copies

C Rows affected: 3

D Deleted rows: 3 (recorded)

Question 2

A Rows: =COUNTA(B2:B31)

B Problem: blank Age cells

C Fix: filled with the median

D Log it in CleaningLog sheet

Question 3

A Problem: Sidney typed 6 times

B Fix: Replace with Sydney

C Rows affected: 2

D Deleted rows: 0

Question 4

A Problem: 5 test rows in data

B Fix: deleted rows 12 to 18

C Rows affected: 5

D Deleted rows noted in the log

Question 5

A Problem: 4 dates typed as text

B Fix: converted to real dates

C Deleted rows: 4

D Rows affected: 4

Answer Key

Keeping a cleaning log

Section A: Quick Questions

A — To record each problem found and the fix applied, so changes can be explained and checked

A — Problem, Fix, Rows affected

B — Problem: blank Age cells | Fix: filled with 'Unknown' | Rows affected: 4

A — Record the deletion in the cleaning log, including that 3 rows were removed

B — So others can see why the row count dropped and check the deletion was fair

Section B: Match the Pairs

Problem: 3 blank Age cells → Describes the data problem that was found in the dataset

Rows affected: 12 → Shows how many rows were changed or removed by the fix

A log entry records the fix applied, so others know exactly what was done to the data → Fix: used TRIM to remove extra spaces from column B

Deleted rows must be recorded in the log, with the reason, so the change can be checked later → Problem: duplicate names | Fix: deleted rows | Rows affected: 4

Log: Fixed some data. Rows affected: ? → The entry does not name the problem or the fix and gives no row count, so it cannot be checked.

Problem: duplicates | Fix: deleted | Rows affected: 0 → Rows were deleted but the log says 0 rows were affected, so the record is wrong and hides the lost data.

Section C: Definitions

cleaning log — A record of each data problem found, the fix applied and how many rows were affected, so every change, including deleted rows, can be explained and checked.

Section D: Fill in the Blanks

Problem,Fix,Rows affected
Blank age,Added N/A,4

Total rows affected in the log:
=SUM(C2:C6)

Problem: blank cells in column B
=COUNTIF(B2:B30,"")

C4 holds the deleted rows from the log
=COUNTA(A2:A30)+C4

Problem: extra spaces in names
Fix: =TRIM(A2)

Section E: Spot the Error

A — Line 1 should name the problem found, so it must start with 'Problem:'. 'Fix:' is for the action taken, and the next line already gives the fix. Correct line: Problem: 3 duplicate rows.

A — COUNTA counts cells that are filled in, not blank ones. To count the blank cells (the rows affected) use =COUNTBLANK(B2:B31).

C — The problem says Sidney was typed 6 times and Replace changed each one, so 6 rows were affected. A log with the wrong count can't be checked. It should say Rows affected: 6.

B — Rows 12 to 18 is 7 rows, but only 5 test rows were found and the log says 5 rows were affected. The fix should say rows 12 to 16 so the log matches.

C — Converting text to dates changes the cells but removes no rows. The log should say Deleted rows: 0. Rows affected is a different number from rows deleted.