Data cleaning steps, excel examples and checklist
Correct duplicate and inconsistent records with a data cleaning workflow, Excel examples and checks for product catalogues and customer exports.
- Published
- Last updated
Data cleaning finds and corrects inaccurate, inconsistent, duplicate or unusable values so your data can do its intended job. Preserve the original export, agree the rules, correct a working copy and verify the result before importing it.
For a retailer, a repeated product row and a second size of the same product need different treatment. The workflow below keeps those differences intact and carries the same safeguards into customer exports.
What data cleaning means
Data cleaning corrects existing records against an agreed definition of a valid value. The task is also called data cleansing. Data wrangling also covers reshaping and combining data, such as joining supplier files into one import table.
Correcting a verified weight from kilograms to grams is cleaning. Adding a material specification that the record never contained is product data enrichment. An empty material field stays unresolved until someone finds evidence.
Before and after a correction
These examples are fictional. Each correction depends on a stated rule or source.
| Field or record | Before | Action | After |
|---|---|---|---|
| Colour | Navy | Trim stray spaces | Navy |
| Product row | Two identical rows for SKU 00120-S-N | Confirm the same variant and source, retain one | One row for 00120-S-N |
| Weight | 0.25 kg | Convert to the required grams | 250 in weight_grams |
| Selling price | -29.90 | Compare with the approved price list | 29.90 when the list confirms it |
| Material | Blank | Request verified specification | Blank, recorded as an exception |
A negative value can be valid in a refund export. Its validity depends on the field’s purpose, so a blanket rule to remove every negative number would damage that file.
Profile the data and set acceptance rules
Profile the untouched export before correcting values so you can explain what changes and why. Keep an access-controlled source copy, then create a separate working file with a batch identifier.
Write down the destination and its required columns. Define the record key, accepted formats, allowed categories and which source decides disputed values. For a variant-level product file, the key must identify each sellable variant. A parent product name alone is insufficient.
Count blank required fields, repeated keys, invalid numbers and unexpected categories. Keep those counts separately because one row can fail several checks. Compare minimum and maximum values with the field’s business meaning before treating an outlier as an error.
For example, require unique variant identifiers, positive package weights and prices matching the approved price list. Allow an unknown material only if the destination accepts a blank. Otherwise hold that record outside the import.
Customer exports also need an access review. Identify names, contact details, addresses and consent fields. Restrict the working file to the people completing the migration and follow your organisation’s retention rules for copies.
Follow the data cleaning steps
Use a repeatable sequence so one correction doesn’t hide another problem.
- Profile the source and record the baseline counts. List the checks that the final file must pass.
- Correct field types. Keep identifiers as text, quantities as numbers and dates under an explicitly agreed interpretation.
- Standardise known values. Trim stray spaces, map approved category spellings and convert compatible units into the destination’s unit.
- Review duplicate candidates. Compare the record key and the facts each row contains before retaining, merging or excluding anything.
- Handle missing or implausible values. Recover a fact from an authoritative source or put the record in an exception list.
- Validate the working file, then test a small import. Compare the imported result with the source before processing the remaining records.
Keep a transformation log containing the affected field, the rule, the original value and the resulting value. Record the reason and approver for a manual correction. Another team member must be able to reproduce the output from the preserved export.
An exception list needs the record key, disputed field, available evidence, assigned reviewer and resolution status. A blank dimension and a contradictory dimension need different investigations, even if both prevent import.
Clean a spreadsheet in excel
Use an Excel table to keep each record together while you filter and inspect the export. The example uses stock keeping units (SKUs), which identify the retailer’s variants.
- Import the file through Excel’s data import workflow. Set SKU, barcode and customer ID columns to Text before a numeric conversion removes leading zeros. Inspect automatic type-conversion steps in Power Query.
- Create a table with the header-row option enabled. Filter required columns for blanks and filter colour values to find unexpected spellings.
- Add a helper column for each proposed correction. If B2 contains
Navy,=TRIM(B2)producesNavy. Keep the original column until you compare the results. - Review repeated keys with conditional formatting or a duplicate check. Use Remove Duplicates only after deciding which columns define an identical record.
- Apply data validation to edited fields. Use an approved colour list and a numeric rule for weight, then inspect the existing records against those rules.
- Copy approved results into the destination structure. Export and inspect the resulting CSV, including identifiers, delimiters and decimal values.
Microsoft’s TRIM instructions explain that it also collapses repeated ordinary spaces inside text. It doesn’t remove non-breaking spaces by itself. Preserve meaningful spacing in fields where that change would alter the value.
Excel inputs and expected results
| Input | Action | Expected result |
|---|---|---|
SKU 00120-S-N | Import as Text | 00120-S-N, unchanged |
Colour Navy in B2 | =TRIM(B2) | Navy |
Weight 0.25 in kilograms | Multiply the numeric value by 1,000 | 250 grams |
Price text 29.90 | Convert using the source decimal convention | Numeric 29.90 |
| Two rows sharing a product name | Compare SKU and size | Keep both when they identify different variants |
Formatting a damaged identifier afterwards doesn’t establish its original value. Return to the source export when zeros or digits have already been lost. Microsoft documents preserving leading zeros through text handling during import.
For dates, establish whether the source orders its day before its month before converting. In Power Query, select the column’s data type using the source locale. Its data-type guidance explains locale-based interpretation. Ambiguous values stay in the exception list until that convention is known.
For recurring imports, save the Power Query steps instead of repeating manual edits. Refresh a new working batch, inspect conversion errors and compare its output with the previous accepted batch.
Clean product facts before catalogue import
Clean a product file at variant level so each size and colour keeps its own facts. The following fictional export contains one shirt in three variants. The approved supplier sheet confirms the weights and the selling-price list confirms each price.
Original and cleaned variant records
| SKU | Size | Original colour | Original weight | Original price | Cleaned colour, grams and price |
|---|---|---|---|---|---|
00120-S-N | S | Navy | 0.25 kg | $29.90 | Navy, 250, 29.90 |
00120-M-N | M | navy | 270 g | 29.90 | Navy, 270, 29.90 |
00120-S-B | S | Black | 0.25 kg | 29,90 | Black, 250, 29.90 |
In this example, the supplier confirms that the comma is a decimal separator. The destination requires a numeric price without a currency symbol. A comma in another file needs its own interpretation before conversion.
Map navy to the approved Navy category. Preserve all three SKUs, their sizes and their colour differences. Converting 0.25 kg to 0.25 grams makes the weight one thousand times too small. Deduplicating on the product name would also remove valid choices.
Those errors reach the storefront. A shopper’s colour filter can miss an inconsistent value, and product comparisons can display the wrong weight. A shopping answer that uses the imported facts can repeat those mistakes. Define product attributes consistently before setting their validation rules.
Keep the destination’s identifiers and option columns intact. Shopify’s Shopify CSV guidance warns that variant updates depend on option columns. Omitting required option information can replace existing variants with a default variant.
Use a small test batch in a non-production store. Review the import preview, then compare the imported SKUs and option combinations with the accepted file. Check a Navy size S search and a side-by-side weight comparison. Expect all three variants and weights of 250, 270 and 250 grams respectively.
An unknown material or product dimension stays an exception. Cleaning an existing catalogue doesn’t authorise a guess or require rebuilding its parent and variant structure.
Clean customer and CRM exports before migration
Clean customer relationship management (CRM) exports with stable customer IDs and their related records intact. Use a generic test migration when a platform-specific matching rule would add unnecessary complexity.
In this fictional example, customer C100 has email alex@example.com and phone 0412 345 678. Remove only surrounding email whitespace. Under an agreed Australian phone rule, normalise that local mobile to +61412345678 and retain its original value separately.
Customer C101 shares the phone number but has another name and customer ID. Keep both records. Shared household or business contact details are duplicate candidates, not proof that two customers are one person.
Retain the source system and source customer ID with each record. Split or standardise address fields only when the source establishes their meaning. Preserve unit numbers and postcodes as text. A missing unit number must remain unresolved.
Use this matching rule for the example: repeated source-system and customer-ID pairs enter duplicate review. Matching email or phone alone only flags a candidate. A reviewer compares account history and linked orders before approving a merge.
Keep a mapping from every old customer ID to its retained ID when a merge is approved. Preserve the evidence for each consent value. A blank consent field doesn’t establish permission, and contradictory values need an assigned decision before migration.
In a disposable test destination, import a small batch with linked orders. Compare the exported and imported customer IDs, order relationships, order totals and consent values. With C100 and C101 unmerged, expect two customer records and every order still attached to its original customer.
Reconcile any approved merge against its mapping and review all rejected rows. A successful import message alone doesn’t show that the customer relationships survived.
Choose data cleaning tools for the workload
Data cleaning tool selection starts with the work you must repeat and the decisions people must review. The best tool for an occasional spreadsheet correction can be insufficient for a daily multi-team import.
| Workload | Suitable approach | What to assess |
|---|---|---|
| Occasional, reviewable export | Excel tables, filters and helper columns | Visible originals, validation rules and recoverable edits |
| Recurring files with stable columns | Power Query transformations | Explicit types, saved steps and error handling on refresh |
| Many inconsistent text categories | OpenRefine facets and reviewed clustering | Candidate groups, approved replacements and complete exports |
| Several source systems and teams | Governed enterprise pipeline | Access controls, change history and assigned exception queues |
OpenRefine’s facets expose distinct field values and their counts. Its clustering proposes similar text values for review. Approve each proposed group against the field definition so similar names don’t become an accidental merge.
Its running guidance explains local operation. Assess any external reconciliation service’s data handling before using it with customer records.
Evaluate reversible changes, field-level validation, duplicate matching, audit logs, privacy controls and export compatibility. Test with a troublesome sample from your own workflow, including a genuine variant and an uncertain duplicate.
For data stewardship across teams, also require field ownership records, approval workflows, exception queues and assigned quality checks. A tool that transforms rows doesn’t automatically decide who can approve a changed price or resolve disputed consent.
Verify the result with a data cleaning checklist
Verify data before, during and after cleaning. Use the checklist to decide whether a batch is ready for its test import.
- Preserve the source export and confirm the working copy’s batch identifier.
- Confirm every retained record has a stable key and every required key is unique.
- Check field types, leading zeros and the source date interpretation.
- Reconcile duplicate removals and approved merges with their recorded decisions.
- Explain required-field blanks and keep unresolved records outside an import that can’t accept them.
- Validate category values, compatible units and business-specific numeric ranges.
- Compare corrected samples with their authoritative sources.
- Reconcile row counts and relevant totals before and after transformation.
- Confirm the exported structure matches the destination and inspect the test import.
- Record unresolved exceptions and assign the person who will resolve each one.
If a 100-row file contains two confirmed repeated rows, expect 98 retained rows after removing them. With five of those records held as exceptions, expect 93 import-ready rows. Explain every difference instead of forcing the final count to match the source.
For customer data, reconcile linked-order counts and totals too. For product data, compare variant counts and the accepted prices. Keep a final batch report containing the baseline, corrections, excluded records and unresolved issues, with the evidence needed to replay the work.
Keep recurring imports clean
Apply the same field definitions and acceptance rules to every new supplier or customer export. Save the transformations, then review new values before releasing each batch.
Assign a reviewer for new category values and changed supplier formats. Compare duplicate rates, invalid-value counts and unresolved exceptions with the previous batch. A falling error count is useful only when the underlying rules and exclusions are understood.
When an import repeatedly fails, inspect the supplier format first. A renamed weight column can leave a saved conversion reading the wrong field. Next inspect the transformation rule for type or unit errors. Finally check whether the destination mapping still points to the intended column.
Correct the failing stage and rerun the preserved batch through the same acceptance checks. Record the approved rule change so the next import uses it.
Assign data stewardship roles and responsibilities
Data stewardship is the continuing responsibility for keeping data usable and trustworthy. A steward maintains field definitions and quality rules. The data owner approves business decisions, while a custodian operates the storage and access controls. Data governance sets the organisation’s overall rules for those responsibilities.
Retail field responsibilities
This example assigns roles that a retailer can adapt to its own team.
| Field | Steward | Approver | System of record |
|---|---|---|---|
| SKU and variant identifiers | Catalogue operations lead | Product data owner | Approved product master |
| Product dimensions | Product information specialist | Category manager | Verified supplier specification |
| Selling price | Pricing analyst | Commercial manager | Approved pricing system |
| Customer consent | Customer data steward | Privacy lead | Consent record with source evidence |
The approver named in each row resolves a disputed business value. The steward gathers evidence and documents the proposed correction. For a conflicting dimension, compare the supplier specification with the existing product master and seek category-manager approval before changing it.
Record the accepted value, its evidence and the approval. Keep an unsupported dimension unresolved. Assign a supplier contact or internal reviewer to obtain the missing specification.
Use accountable decisions, consistent definitions, traceable changes, restricted access and regular quality checks as stewardship principles. A quality score alone doesn’t assign responsibility for a wrong fact.
Require approval before changing a field definition, allowed category or import mapping. Have the catalogue steward review duplicate rates and invalid values after each batch. The customer data steward reviews consent exceptions and merge decisions.
After an approved catalogue change, check its effects on storefront search and product comparisons. Release the next batch only when the assigned reviewers can explain its corrections and account for the records still held aside.
Give your store its own shopping agent
Customers ask in their own words. Your agent finds, compares and checks out from your catalogue, right on your site.