Data Cleaning & Preparation

Real-world data does not arrive clean. It arrives with empty cells, duplicates, "Cairo" written four different ways, dates as text, and impossibly negative quantities. Cleaning that chaos is the largest slice of a data analyst's work — most estimates put it at 60–80% of the job — and it is also where analyses are won or lost.

This topic gives you one repeatable workflow instead of a bag of tricks: profile → clean → verify → document. You will apply it in Excel and SQL (the tools you already know) using Power Query and repeatable queries, so your cleaning survives new data arriving next week. The same concepts return later in Python and Power BI — once you have the framework, only the buttons change.

Complete Basics, Excel and SQL — you will clean in those tools. A spread of statistics terms (outliers, skew) helps but is not required yet.

Every claim you make later is built on this step. A missing regional flag silently underweights that region; a duplicated order inflates revenue; a "text" date column breaks every month-over-month comparison. Garbage in, garbage out is not a joke — it is the reason managers stop trusting analytics.

Think of cleaning as journalism for numbers: you are establishing that each row means what it appears to mean before anyone reports it. The good news: a structured workflow makes the process fast and auditable instead of a nightly scramble.

Profiling is a quick survey of the data before you fix anything. You want answers to six questions:

1. How many rows and columns, and what does one row mean (the grain)?
2. Which columns exist and what type is each (number, date, text)?
3. How many nulls per column and what fraction of the rows do they cover?
4. How many duplicates, exact and partial?
5. What are the min/max/typical values per numeric column?
6. How many distinct values does each categorical column have?

In Excel: a quick Table + filter dropdown + COUNTIFS/COUNTBLANK answers most of this. In SQL, a one-query overview does it:

SELECT
  COUNT(*)                   AS total_rows,
  COUNT(customer_name)       AS name_filled,
  COUNT(DISTINCT customer_id) AS distinct_customers,
  COUNT(DISTINCT region)     AS regions,
  MIN(order_date)            AS first_order,
  MAX(order_date)            AS last_order,
  SUM(CASE WHEN amount < 0 THEN 1 ELSE 0 END) AS negative_amounts
FROM orders;

Profiling first means you fix real problems in priority order, and you know your data's limitations before you defend a conclusion.

Missingness is not random: rows usually go blank for a reason, and that reason can bias your result. Before fixing, ask three things: how much is missing (1% or 40%?), why (not collected? failed import? only applies to some segments?), and does missingness correlate with the variable you care about (richer customers leave the income field blank?).

The professional habit: never silently choose. Whatever you do, record it in your transformation log so reviewers see the decision and can challenge it.

Two identical rows are an error unless the row type allows repeats (two same-sized orders from the same customer are legal). The decision rule: first define the unique key — the column set that makes a row unique (e.g. order_id) — then check duplicates on the key.

• Exact duplicates: every column identical. Almost always an import artifact — remove and log.
• Key duplicates: same order_id twice, different amounts. Do not delete blindly — investigate which record is the true one.
• Near-duplicates: "New York" and "new york" and "NY". Those are standardization problems (next section) more than duplicates.

In Excel use Remove Duplicates only after confirming the key. In SQL: SELECT key, COUNT(*) … GROUP BY key HAVING COUNT(*) > 1 shows the suspects before you touch anything.

Consistency is the quiet superpower of clean data. Standardize four things:

• Types — dates as dates, numbers as numbers. "1,234.56" with a thousand separator, or 2026-09-04 stored as text, breaks every aggregation. Make the type real (Excel: Text-to-Columns / unformatted cell; SQL: CAST(x AS date)).
• Text case — TRIM, then normalize case (Excel LOWER, SQL LOWER()) so joins and filters match.
• Categories — one canonical label per value. Map "BI" to "Business Intelligence", "bI", "Bi" to the same thing, and record the mapping so it is repeatable.
• Units and scales — revenue in USD everywhere, time in the same timezone, percentages stored consistently (0.05 vs 5%).

Build a lookup table for canonical categories instead of editing strings one cell at a time — it is faster, documentable, and reusable for next month's export.

Beyond format, check meaning. A value can be clean-looking yet impossible:

• Range rules — negative quantity, price above a sane cap, age > 120
• Cross-field rules — shipped_date < order_date, end_date < start_date, subtotal ≠ sum of line items
• Completeness rules — an order with no customer, a payment with no amount

These checks are exactly what a data validation layer exists for. In Excel, conditional formatting + data validation lists catch them visually; in SQL, WHERE amount < 0 or a CHECK-style filter surfaces them fast. Write the rules before analyzing so you catch problems on day one, not after you have charted garbage.

An extreme value is not automatically wrong — a whale account ordering 10,000 units is real. The rule from your statistics topic applies here with extra force: detect → investigate → decide.

After detecting (IQR fence or a sorted scan), ask the data: could a typo have created this? (a 1,000,000 instead of 10,000), is it within the real business range?, does it match the source system? Only then choose: fix the typo, keep-and-flag the legit giant, or exclude with a documented reason. The moment you silently delete a suspicious row, you have decided on someone's behalf — and that is where auditors look first.

Clean data is a process, not a one-time edit. Companies formalize this as ETL / ELT — Extract, Transform, Load — moving data from source systems into a warehouse ready for analysis. You build the same shape at your scale:

1. Raw — source files untouched, archived (your Excel "Raw" sheet)
2. Clean — one repeatable transform (Power Query steps, SQL views/CTEs, or a Python script) that turns raw into tidy output
3. Report — the analysis layer reads only the clean step, never the raw chaos

Power Query (Excel/Power BI) records every click as a step you can re-run on new files; SQL puts cleaning into CREATE VIEWs. The payoff: next month's file reprocesses in minutes, and your cleaning logic is version-controlled evidence — a professional artifact, not a mysterious spreadsheet you "fixed" by hand.

A cleaned dataset is only as trustworthy as its backstory. Deliver two companions with any important dataset:

• Data dictionary — one line per column: name, type, meaning, allowed values, unit
• Transformation log — what you changed and why: "removed 3 exact-duplicate rows; filled region='UNK' for 12 nulls; changed 9 dates from text to date"

This turns your work from "a file" into evidence. When a manager asks "wait, where did this number come from?" — and they will — you can answer precisely instead of hoping.

Reuse any messy CSV you can find (or deliberately dirty a clean one: delete cells, repeat rows, mix date formats, add a " new york " row). Complete:

1. Profile it: rows, columns, nulls %, duplicates, min/max per numeric column, distinct values per categorical column.
2. Fix missing values in the least destructive sensible way and log the choice.
3. Define the unique key; remove true duplicates with it, investigate key duplicates.
4. Standardize case/trim/spacing, convert dates and numbers to true types, map categories to one canonical label.
5. Write 5 validation rules and count how many rows violate each.
6. Flag outliers with the IQR rule and classify each as error or genuine.
7. Assemble the clean dataset in Power Query (reproducible steps) and write a one-page quality report.

Hiring managers test cleaning judgement because every real project starts in the mud: split systems, one-off exports, fields named differently from one department to the next. The analysts who stand out are the ones who can look at a messy file and describe — in one minute, with a profile — what is wrong and what they will do about it. Later in this path your Power BI model and Python analyses both consume this pipeline: build it once, reuse it everywhere.

Business problem: The finance team merged three regional exports into one spreadsheet and handed it to you with the note "there's a few issues".

Dataset: any 300+ row sales file (superstore-style) that you deliberately break: 5% nulls, ~10 duplicate rows, mixed date formats, inconsistent category labels, 2–3 out-of-range amounts, 1–2 negative quantities.

Objective: deliver a clean, reproducible, documented dataset and let someone else verify your choices.