Chapter 11 | Data Quality and Preparation

Advanced Data Cleaning

Convert untidy business records into consistent, analysis-ready data. Learn a controlled cleaning process for duplicate entries, unwanted spaces, mixed formats, incorrect dates, stored-as-text numbers, missing values and inconsistent categories.

Start Learning Cleaning Project

Choose Language

Data Quality Chapter · Eight Practical Activities Included

Learning Objectives

After This Chapter, You Will Be Able To

Clean business data systematically while preserving evidence, accuracy and traceability.

Audit Data Quality

Identify duplicates, blanks, inconsistent labels, invalid values and incorrect data types.

Clean Text Records

Use Excel tools and formulas to repair spaces, characters, casing and text patterns.

Convert Data Types

Turn text-based numbers and dates into genuine values that calculate and sort correctly.

Validate Results

Confirm that cleaning has not removed valid records or changed important business totals.

Safe Working Rule: Never clean the only copy of a dataset. Preserve a read-only raw-data sheet, create a working copy and maintain a brief cleaning log showing what was changed and why.

Lesson 1

A Professional Data-Cleaning Workflow

Reliable cleaning follows a repeatable sequence. Random corrections may hide errors, alter totals or make the process impossible to reproduce.

1 Profile, Protect, Clean and Verify

Data cleaning is the process of finding and correcting records that are incomplete, duplicated, inconsistent, incorrectly formatted or unsuitable for analysis. The objective is not to make data look attractive; it is to make every field trustworthy and usable.

1Preserve

Keep the original file or raw-data sheet unchanged.

2Profile

Measure row count, blanks, unique values, formats and exceptions.

3Clean

Apply controlled transformations to a working copy.

4Verify

Recheck counts, totals, keys, formulas and rejected records.

Initial Data Audit Checklist

Audit Area	What to Check	Useful Excel Method	Risk if Ignored
Record count	Total rows before cleaning	COUNTA or Table Total Row	Valid records may be lost silently
Unique keys	Customer ID, invoice ID or employee code	COUNTIF, Conditional Formatting	Duplicates distort totals
Missing values	Blank mandatory fields	Filter blanks, COUNTBLANK	Incomplete decisions and reports
Categories	Different spellings for the same item	Sort, Filter, UNIQUE	One category appears as many groups
Data type	Numbers or dates stored as text	ISTEXT, ISNUMBER, error indicator	Incorrect sorting and calculations
Range validity	Impossible ages, quantities or dates	Conditional Formatting, MIN/MAX	Unrealistic analysis results

Definition: A data-quality rule is a clear condition that a record must satisfy, such as “Invoice ID must be unique,” “Quantity must be greater than zero,” or “Region must come from an approved list.”

Practical Experiment 1: Build a Data Quality Profile

Use a copy of a customer or sales dataset with at least 30 records.

Step 1: Record Baseline

Write the current row count, column count and total of one important numeric field.

Step 2: Inspect

Filter every column and note blanks, unexpected values, inconsistent formats and duplicates.

Step 3: Prepare Log

Create columns for Issue, Record/Range, Planned Action, Result and Reviewer.

Learning Output: A documented baseline that can be compared with the cleaned dataset.

Lesson 2

Finding and Removing Duplicate Records

A duplicate is not always an identical row. The correct test depends on which field or combination of fields uniquely identifies a real transaction or entity.

2 Define the Business Key Before Deleting Anything

Two customers may share the same name, and one customer may make many purchases. Therefore, duplicates should be identified using a reliable key such as Customer ID, Invoice Number, Email Address, or a combination of Date + Product + Transaction ID.

=COUNTIF($A$2:A2,A2)>1

Flags the second and later occurrence of an ID while preserving the first.

=COUNTIFS($A$2:A2,A2,$B$2:B2,B2)>1

Flags repeated combinations, such as the same invoice and product.

Methods for Duplicate Review

Conditional Formatting: visually highlight duplicates for review before deletion.
COUNTIF or COUNTIFS: create a transparent helper column that explains why a row is flagged.
Remove Duplicates: permanently removes repeated rows based on selected columns.
Advanced Filter: copies unique records to another location without changing the source.

Important: The Remove Duplicates command keeps the first occurrence it finds. Sort the data intentionally and preserve a backup before using it.

Practical Experiment 2: Remove Duplicates Safely

Step 1: Select the Key

Decide whether one field or several fields define a unique record.

Step 2: Flag First

Use Conditional Formatting or COUNTIFS to identify repeated keys.

Step 3: Remove and Reconcile

Remove confirmed duplicates, record the number removed and compare the final count with the baseline.

Learning Output: A cleaned dataset with a documented duplicate-removal decision.

Lesson 3

Standardizing Categories, Labels and Formats

Small differences such as “North”, “NORTH”, “North ” and “N. Region” can create separate groups in PivotTables and dashboards.

3 Replace Variations with Approved Values

Begin by creating an approved list of category values. Then map every variation to one standard label. This approach is safer than correcting entries from memory because it creates a consistent business rule.

Find & ReplaceCorrect exact, repeated spelling variations quickly.

Flash FillRecognize a demonstrated pattern and fill similar results.

Mapping TableUse XLOOKUP to convert old labels into approved labels.

Filter ReviewInspect all distinct entries before finalizing replacements.

=XLOOKUP(A2,Map[Old Value],Map[Approved Value],"Review")

Standardizes entries through a controlled mapping table and flags unmapped values.

=UPPER(TRIM(A2))

Removes extra outer spaces and applies a consistent case when uppercase is required.

Professional Tip: Keep category codes separate from category descriptions. A stable code is often more reliable than a label that may be renamed later.

Practical Experiment 3: Standardize a Category Column

Step 1: List Variations

Sort or use UNIQUE to reveal every version of a region, department or product category.

Step 2: Create Mapping

Build an Old Value and Approved Value table.

Step 3: Apply and Check

Use XLOOKUP or Find & Replace, then confirm that only approved labels remain.

Learning Output: One controlled category list suitable for PivotTables and dashboards.

Lesson 4

Splitting and Reorganizing Combined Data

One cell should normally contain one type of information. Combined names, addresses, codes or descriptions are difficult to filter, validate and analyze.

4 Text to Columns and Flash Fill

Text to Columns separates data using a delimiter such as comma, space, tab or hyphen, or by fixed character positions. Flash Fill learns from a sample pattern and is useful when the separation rule is visible but not easy to describe.

Situation	Recommended Tool	Example	Key Precaution
Consistent delimiter	Text to Columns	City, State	Ensure destination columns are empty
Fixed-position code	Text to Columns or LEFT/MID/RIGHT	REG-2026-0145	Confirm every code follows the same structure
Recognizable pattern	Flash Fill	Extract first name from full name	Review exceptions manually
Dynamic output needed	TEXTBEFORE/TEXTAFTER	Split email at @	Use error handling for missing delimiters

=TEXTBEFORE(A2,"-")

Returns the text before the first hyphen in modern Excel versions.

=TEXTAFTER(A2,"-")

Returns the text after the first hyphen and updates automatically when the source changes.

Practical Experiment 4: Separate Customer and Product Fields

Step 1: Duplicate the Column

Preserve the original combined field before splitting.

Step 2: Choose Method

Use Text to Columns, Flash Fill or a dynamic text formula based on the pattern.

Step 3: Inspect Exceptions

Filter blanks and unusually short or long outputs to locate records that did not follow the pattern.

Learning Output: Separate, analysis-ready fields with exceptions identified for review.

Lesson 5

Cleaning Text with Excel Functions

Formula-based cleaning is transparent and repeatable. The original value remains visible while the cleaned result can be checked before replacement.

5 TRIM, CLEAN, SUBSTITUTE and Case Functions

Function	Main Use	Example	Important Note
TRIM	Removes leading, trailing and repeated ordinary spaces	`=TRIM(A2)`	May not remove non-breaking spaces
CLEAN	Removes many non-printing characters	`=CLEAN(A2)`	Usually combined with TRIM
SUBSTITUTE	Replaces specific text or characters	`=SUBSTITUTE(A2,"/","-")`	Can target a specific occurrence
PROPER	Capitalizes the first letter of words	`=PROPER(A2)`	Review names such as McDonald or acronyms
UPPER / LOWER	Applies consistent letter case	`=UPPER(A2)`	Useful for standard codes and emails
VALUE	Converts suitable numeric text to a number	`=VALUE(A2)`	Remove currency symbols or separators if necessary

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

Replaces non-breaking spaces, removes non-printing characters and normalizes ordinary spaces.

=PROPER(TRIM(CLEAN(A2)))

Cleans a name or description and applies title-style capitalization.

Convert Formulas to Final Values

After checking the cleaned output, copy the formula results and use Paste Special → Values when a permanent clean field is required. Keep the original field or archive it until the cleaned dataset is approved.

Practical Experiment 5: Create a Clean Customer Name

Step 1: Add Helper Column

Create a Clean Name column beside the raw name.

Step 2: Apply Formula

Use TRIM, CLEAN and the most suitable case function.

Step 3: Compare

Filter records where raw and cleaned values differ, then review exceptions before pasting values.

Learning Output: A repeatable text-cleaning formula with reviewed exceptions.

Lesson 6

Repairing Dates, Numbers and Mixed Data Types

A value may look like a number or date but still be stored as text. Such values often fail to sum, sort chronologically or work correctly in PivotTables.

6 Detect Before Converting

Use ISNUMBER, ISTEXT, alignment clues and Excel’s error indicators to identify mismatched types. Do not rely only on appearance because number formats can make different underlying values look similar.

=IF(ISNUMBER(A2),A2,IFERROR(VALUE(SUBSTITUTE(A2,",","")),"Review"))

Preserves genuine numbers, converts suitable numeric text and flags unconvertible records.

=IF(ISNUMBER(B2),B2,IFERROR(DATEVALUE(B2),"Review"))

Preserves real dates and attempts to convert date text into a serial value.

Useful Conversion Methods

Use the error indicator’s Convert to Number option for small datasets.
Use Text to Columns → Finish to convert many text numbers when the pattern is consistent.
Multiply by 1 or use VALUE only after confirming that identifiers with leading zeros should not remain text.
Use DATE, DATEVALUE or controlled parsing when imported dates follow different patterns.
Format the converted result only after the underlying value is correct.

Leading-Zero Warning: Product codes, postal codes, account references and employee IDs may look numeric but are identifiers. Converting them to numbers can remove meaningful leading zeros.

Practical Experiment 6: Repair Imported Numbers and Dates

Step 1: Test Types

Add helper columns using ISNUMBER and ISTEXT.

Step 2: Convert Carefully

Apply VALUE, DATEVALUE or Text to Columns only to fields that should be numeric or date-based.

Step 3: Validate

Sum the numeric field, sort the date field and filter conversion errors.

Learning Output: Correct data types that calculate, sort and group reliably.

Lesson 7

Handling Blanks, Errors and Final Quality Checks

Not every blank should become zero, and not every error should be hidden. The correct action depends on the business meaning of the field.

7 Classify Missing and Invalid Values

A blank may mean not collected, not applicable, pending, unavailable or truly zero. Replacing all blanks with one value can create false information. First classify the reason, then choose whether to retain, complete, exclude or flag the record.

1Blank but Valid

A middle name may be optional. Preserve the blank when it has no analytical risk.

2Blank and Required

A missing invoice ID should be flagged for correction or exclusion.

3Error Value

Investigate the source of #N/A, #VALUE! or #DIV/0! before applying error handling.

=IF(A2="","Missing",A2)

Labels truly blank cells without changing nonblank values.

=IFERROR(calculation,"Review")

Provides a controlled result, but the original error cause should still be investigated.

Post-Cleaning Reconciliation

Compare raw and cleaned row counts.
Recalculate one or more control totals.
Confirm that unique keys are still unique.
Filter every field for blanks, errors and unexpected categories.
Check minimum and maximum values for unrealistic results.
Review a sample of changed records against the source.
Save rejected or unresolved records in a separate review sheet.

Practical Experiment 7: Complete a Quality-Control Review

Step 1: Create Checks

Add control cells for row count, duplicate keys, blank mandatory fields, error count and numeric total.

Step 2: Compare

Compare raw and cleaned results and explain every expected difference.

Step 3: Approve or Reject

Mark the dataset Ready only when all material exceptions are resolved or documented.

Learning Output: A measurable approval process instead of visual guesswork.

Real-Time Practical Assignment

Customer and Sales Master Data Cleanup

Prepare an untidy imported dataset for reliable reporting without losing the original evidence.

1Preserve and Profile

Create Raw_Data, Working_Data, Mapping and Cleaning_Log sheets. Record baseline counts and totals.

2Clean and Standardize

Correct duplicate IDs, names, regions, product codes, dates, quantities and missing mandatory values.

3Validate and Report

Prepare a summary of records received, changed, removed, rejected and approved for analysis.

Practical Experiment 8: Final Cleaning Audit

Step 1: Add Test Records

Include duplicate keys, extra spaces, inconsistent categories, text numbers, invalid dates and blanks.

Step 2: Apply Controlled Rules

Use helper columns, mapping tables and documented Excel tools to clean the working dataset.

Step 3: Present Evidence

Show before-and-after counts, exception records, formulas used and final approval checks.

Final Deliverable: A clean Excel Table, cleaning log, exception sheet and one-page quality summary.

Interactive Cleaning Method Selector

Select a common data problem to receive a recommended Excel method and sample formula.

Data Problem Source Cell or Field

Practice Worksheet

Activity	Required Output	Review Question
Data profile	Baseline row count, unique-key count, blank count and control total	Can every later change be reconciled?
Duplicate control	Helper formula and duplicate-review list	Does the selected key represent a truly unique record?
Category standardization	Mapping table and approved labels	Are all unexpected values flagged?
Text cleaning	Raw and cleaned text columns	Were names, codes or acronyms changed incorrectly?
Type conversion	Valid numeric and date fields	Do totals and chronological sorting now work?
Exception management	Separate unresolved-record sheet	Are uncertain records preserved instead of guessed?
Final verification	Before-and-after quality summary	Can another person understand and repeat the process?

AICPE Quality Learning Commitment: AICPE Gurukul focuses on practical, career-oriented learning that helps learners manage real office data responsibly. Learn more at aicpeindia.org and aicpe.online.

Common Mistakes

Mistakes Learners Should Avoid

Data cleaning becomes dangerous when speed is valued more than evidence and verification.

Wrong Habits

Editing the only copy of the source file.
Deleting duplicate-looking rows without defining a unique key.
Replacing every blank with zero.
Using Find & Replace without checking the affected scope.
Converting identifiers to numbers and losing leading zeros.
Hiding all formula errors with IFERROR without investigating causes.
Changing labels manually without an approved mapping list.
Finishing without reconciling counts and totals.

Professional Habits

Preserve raw data and work on a controlled copy.
Create measurable data-quality rules before editing.
Use helper columns to make transformations visible.
Review exceptions instead of guessing replacements.
Keep identifiers as text when their digits carry no arithmetic meaning.
Document every material removal or correction.
Maintain standard categories through mapping tables.
Perform final reconciliation and sample review.

Remember: A clean-looking worksheet is not automatically reliable. A professional dataset must pass documented quality checks.

Quick Revision

Remember These Data-Cleaning Principles

Review these points before moving to input control and data validation.

Protect Raw Data

Preserve the original dataset and record baseline counts and totals.

Define the Key

Remove duplicates only after deciding what uniquely identifies a valid record.

Standardize Through Rules

Use approved category lists and mapping tables instead of memory-based corrections.

Clean Transparently

Use helper columns and formulas so changes can be reviewed before becoming permanent.

Respect Data Types

Convert real quantities and dates, but preserve codes and identifiers as text when appropriate.

Reconcile Everything

Verify row counts, totals, keys, blanks, errors and exceptions after cleaning.