Excel data cleaning: preserve the raw sheet and document changes

Clean spreadsheet data should remain traceable to what arrived. Keep an unchanged source snapshot, transform a separate working table and record rules that explain every dropped row, changed label or rejected value. A polished worksheet is not sufficient if another analyst cannot reproduce it.

This walkthrough uses an original spreadsheet quality workbook, with its data contract and Python reference checks. All records are synthetic. The workbook is generated and structurally checked; Excel calculation and refresh behaviour still require review in the target application.

Inspect the source before editing it

Raw_Lines contains eight rows with invoice-line identifiers, customer IDs, dates, categories, quantities, prices, discounts and status. Customer IDs such as 0012 are text. Invoice I001 legitimately has two lines, while line L03 appears twice as an identical delivery replay.

The source's net amount is Quantity × UnitPricePaise − DiscountPaise. Discounts apply once per line, and all amounts are integer paise. The raw total is 82,500 paise, including the replay. These definitions let you distinguish a source defect from an analyst's arithmetic mistake.

Save the source filename, receipt date, sheet name and row count. If the source is regenerated later, retain the original snapshot used for the report. A refreshed file at the same path may contain different evidence.

Separate transformations from corrections

Trimming a category label and mapping it to an approved category are transformations. Deciding that a contradictory customer region should be West rather than East is a business-data correction requiring evidence. Do not hide both activities under a generic “cleaned data” label.

The fixture's category rule trims surrounding whitespace, lowercases for matching and maps software, training and support to their display labels. Unknown remains Unmapped. CategoryRaw stays beside the mapped Category so a reviewer can see what changed.

An illustrative helper formula for normalized labels is:

=LOWER(TRIM([@CategoryRaw]))

This formula targets the ordinary spaces in the fixture. It is not a universal Unicode whitespace normalizer. If imported text includes nonbreaking spaces or other characters, inspect and handle those explicitly rather than assuming every visually blank character is removed.

Remove the replay under a stated contract

LineID is the intended unique key. Compare every business field for the two L03 records before retaining one representative. If the payloads differed, the correct outcome would be an exception, not arbitrary deletion.

Do not deduplicate on InvoiceID: doing so would remove a legitimate I001 line worth 5,000 paise or its other line worth 19,000, depending on which was retained. The business grain determines the key.

After handling the identical replay, the clean table has seven lines and totals 72,500 paise. The 10,000-paise reduction exactly matches the removed L03 replay. This is a reconciliation bridge, not merely a new total that looks plausible.

Keep unresolved issues visible

Customer 0042 has two contradictory region rows in Customers. Customer 0099 has no customer reference record. Neither issue prevents the line arithmetic from being calculated, but both limit region-level reporting.

Create an exception table with the affected key, issue type, source evidence, owner and resolution status. An analyst can then distinguish “ready for overall sales totals” from “ready for authoritative regional attribution.” Quality is use-dependent; one unresolved field need not invalidate every possible analysis.

For repeatable transformations, Power Query can record a sequence of steps that can be applied during refresh. Its source and transformation model is described in Microsoft's Power Query overview. A recorded step still needs a correct business rule and validation.

Build a compact change log

For this workbook, record three decisions: identical L03 replay removed; category labels normalized through a controlled map; unresolved category and customer issues retained as exceptions. Attach before/after row counts and amount totals to the first decision.

Then validate the actual reporting population. January paid activity contains four lines, three invoices and 47,500 paise. Pending January activity contributes another 8,000 but is excluded from that paid-only view. A correct all-data total cannot replace a check of the selected population.

Exercise: change one of the repeated L03 amounts. Your cleaning process should now flag a conflicting key rather than retain whichever row appears first. Explain what evidence would be needed to resolve it.

NeuraPath's Data Analytics with Generative AI course connects Excel and data-quality work with practical reporting. A strong cleaning project preserves its source, makes its rules reproducible and explains every material change.

Continue learning

This article is part of the Excel and spreadsheet quality sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Continue with XLOOKUP with duplicate keys: what your first match hides.
• Then apply it in Excel SUMIFS with dates and inconsistent category labels.