Clean carefully and keep a quality log

Understand the method

Cleaning is a sequence of documented decisions. Do not treat every repeated identifier, empty cell or negative value as an instruction to delete a row. First confirm the field definition and intended grain. In this exercise, the data dictionary and reference establish one original order value per unique order, with a final status snapshot.

Work on a copy

Copy the raw Source range into a separate scratch sheet or workbook. Keep the original Source sheet unchanged. Find M004 by filtering order_id: it appears twice with identical fields. Because the stated grain is one order and the reference confirms one M004, retain one copy in the prepared result. On a line-item or event-history dataset, the same repeated order ID could be legitimate. Removing duplicates without understanding grain would then lose information.

Useful diagnostics

In Analysis B22 enter =COUNTA('Source'!$A$6:$A$18) to count populated identifiers in the fixed raw sample. B23 uses =COUNTIFS('Source'!$A$6:$A$18,"M004"). B24 uses =COUNTBLANK('Source'!$C$6:$C$18). B25 uses =COUNTIFS('Source'!$E$6:$E$18,"<0"). For B26 link the problematic raw date using ='Source'!B17. These are specific diagnostics, not a universal data-quality score.

A copied helper such as =COUNTIFS($A$6:$A$18,A6) can flag repeated keys on a scratch copy. The fixed range stays anchored while A6 changes as the formula is filled down. Count the affected IDs separately from affected rows; two copies of one order are not two different duplicated business events.

Text and number hygiene

TRIM removes ordinary excess spaces and LOWER makes casing consistent. For example, =LOWER(TRIM(" Web ")) returns web. This example illustrates a method; it is not an additional deliberate defect in the supplied raw file. Do not overwrite a meaningful category or assume TRIM removes every invisible character. Nonbreaking spaces and other Unicode characters may need a specific, agreed correction.

Use VALUE only after confirming the numeric convention. A comma can mean a decimal separator or a thousands separator. A successful conversion is not proof that you chose the right convention. The supplied reference already has numeric order values, and the practice workbook stores them as numbers.

Dates require more than ISNUMBER

DATE can roll an out-of-range day into another month, so constructing a date from year, month and day components is not by itself validation. Compare the resulting year, month and day back to the intended components, or use a checked import process and the verified reference. The fixture's 2026-03-99 should be flagged, not normalized into a convenient calendar date. The reference gives the approved correction.

Record impact and authority

Log M004's duplicate, M006's invalid date, M006's missing channel and M005's negative original value. M004 inflates completed sales. The other records are cancelled or returned, so those defects do not affect the correctly filtered completed-sales total; they still matter for other uses. Identify both the specific impact and the verified source for correction. Never infer that a negative amount is wrong in every dataset: negative refund-ledger entries may be legitimate.