Data cleaning techniques in Excel, SQL and Python

Data cleaning is the work of fixing errors in a dataset before analysis. A dependable order is to profile the data, fix data types, standardise text, remove duplicates, handle missing values, validate ranges and document every change. Standardising text comes before de-duplication because " Asha Rao " and "asha rao" only match once case and spacing agree. Excel, SQL and pandas each have a method for every step.

What are the steps of data cleaning?

The steps run from looking at the data to recording what changed. Each one feeds the next, which is why the order matters.

1. Profile: count rows, blanks, distinct values and repeated rows.
2. Fix types: turn text that looks like numbers or dates into real numbers and dates.
3. Standardise text: trim spaces, unify case, remove invisible characters.
4. Remove duplicates: keep one copy of each real record.
5. Handle missing values: leave, fill or delete, and write down which.
6. Validate ranges: flag values that cannot be true.
7. Document: keep the raw data and a note of every rule applied.

The table below maps each step to the function or feature that does it in Excel, PostgreSQL-style SQL and pandas.

What does messy data look like?

Messy data usually looks fine at a glance. The hypothetical sign-up table below has six rows with six problems: stray spaces, mixed case, numbers stored as text, one blank, one placeholder and one impossible age.

Rows 1 and 4 are the same person. On the raw text, pandas reports 0 missing values and 0 duplicates, because pandas' own documentation says empty strings are not counted as NA, and the case and spacing variants look like different people. A SQL profile query tells a similar story: 6 distinct names as typed, but 5 once LOWER(TRIM(name)) is applied.

How do you fix data types and standardise text?

Convert types first so that numbers can be compared, then standardise text so that equal values match. In pandas, to_numeric with errors set to coerce turns unparseable values into missing values instead of raising an error, and str.strip and str.title tidy text.

In Excel, Microsoft's guidance is to keep a backup copy of the raw data, add a new column with a formula, fill it down, paste as values and remove the original column.

TRIM removes extra spaces, but it was designed for the ordinary space character (value 32) and does not by itself remove the non-breaking space (value 160) that web pages often contain. CLEAN removes nonprinting characters, SUBSTITUTE can replace character 160, and PROPER fixes case. VALUE converts text that looks like a number, such as "$1,000", and returns a value error for text it cannot read. Wrapping it in IFERROR returns a chosen value instead.

In SQL, TRIM, LOWER and INITCAP clean text, and CAST changes type. CAST needs care. SQLite's documentation says casting text to INTEGER keeps the longest numeric prefix and returns 0 if there is none. On the sample values, CAST('' AS INTEGER) returned 0, CAST('n/a' AS INTEGER) returned 0 and CAST('1,200' AS INTEGER) returned 1, all without an error. Turn placeholders into NULL with NULLIF before casting, and strip the comma first.

How do you remove duplicates without deleting real records?

Standardise first, then compare. Excel's Remove Duplicates compares what appears in the cell, not the stored value, so " Asha Rao " and "asha rao" count as different until the text is cleaned. Microsoft also notes that the first occurrence is kept, that the deletion is permanent, and that it is a good idea to filter for unique values first and to copy the original range elsewhere.

In SQL, SELECT DISTINCT removes rows that are identical in every selected column, so dirty text variants survive it. A ROW_NUMBER() window over the cleaned key columns keeps the first row of each group and shows exactly which row was dropped. A grouping query with HAVING COUNT(*) greater than 1 finds the repeats before anything is deleted. On the sample table it returns one group: asha rao in pune, 2 copies.

In pandas, duplicated() flags repeats and drop_duplicates() removes them, keeping the first occurrence by default. After standardising, duplicated() on name, city, age and amount returned 1.

How do you clean the sample table in pandas and SQL?

The pandas version below runs all steps in order on pandas 3.1.1. The documentation for that version deprecates the inplace keyword, so each step assigns its result.

import pandas as pd

raw = pd.DataFrame({
    "id": [1, 2, 3, 4, 5, 6],
    "name": [" Asha Rao ", "BILAL KHAN", "Chitra Nair", "asha rao", "Dev Patel", "Esha Iyer"],
    "city": ["pune", "Pune ", "Chennai", "PUNE", "Mumbai", "Delhi"],
    "age": ["29", "31", "", "29", "240", "27"],
    "amount": ["1,200", "850", "2,400", "1,200", "900", "n/a"],
})
df = raw.copy()
df["name"] = df["name"].str.strip().str.title()
df["city"] = df["city"].str.strip().str.title()
df["age"] = pd.to_numeric(df["age"], errors="coerce")
df["amount"] = pd.to_numeric(df["amount"].str.replace(",", ""), errors="coerce")
df = df.drop_duplicates(subset=["name", "city", "age", "amount"])
df["age"] = df["age"].where(df["age"].between(18, 100))
df["needs_review"] = df[["age", "amount"]].isna().any(axis=1)
print(df.to_string(index=False))

The SQL version does the same on a table of text columns, here run on SQLite 3.54.0. The 18 to 100 age rule is a hypothetical business rule for this example.

WITH cleaned AS (
  SELECT id,
         LOWER(TRIM(name)) AS name,
         LOWER(TRIM(city)) AS city,
         CAST(NULLIF(TRIM(age), '') AS INTEGER) AS age,
         CAST(NULLIF(REPLACE(amount, ',', ''), 'n/a') AS INTEGER) AS amount
  FROM raw_signups),
ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY name, city, age, amount
                               ORDER BY id) AS rn
  FROM cleaned)
SELECT id, name, city,
       CASE WHEN age BETWEEN 18 AND 100 THEN age END AS age,
       amount
FROM ranked
WHERE rn = 1;

Both versions return the same five rows. The pandas output shows ages as 29.0 because a column that holds NaN is stored as decimals.

How do you handle missing values?

Decide per column whether to leave a value missing, fill it or delete the row, and write the choice down. Leaving it missing is often the safest choice, because most tools skip missing values: PostgreSQL's AVG computes the mean of the non-null values only. Filling with 0 changes results. On the amount column of the five clean rows, SQLite's AVG ignoring the missing value returned 1337.5 from 4 values, while AVG with COALESCE(amount, 0) returned 1070.0.

Deleting every row that has a missing value is convenient but not neutral. In Stef van Buuren's textbook Flexible Imputation of Missing Data, listwise deletion gives unbiased estimates when values are missing completely at random, can severely bias means and regression results when they are not, and can discard more than half of a sample. The same book says mean imputation underestimates variance, disturbs relations between variables and should be avoided in general.

In the sample, Chitra's age and Esha's amount stay missing and carry a needs_review flag. COALESCE and NULLIF in SQL, fillna and dropna in pandas, and ISBLANK in Excel are the tools when a fill or delete is justified.

How do you validate ranges, and should you remove outliers?

Validation flags values that cannot be true, such as an age of 240, and an outlier is only a value far from the rest. NIST's e-Handbook of Statistical Methods says an outlier may indicate bad data, and that if a point is determined to be erroneous it should be deleted from the analysis or corrected if possible. When it cannot be determined whether the point is bad data, NIST says one typically does not want to simply delete it, because it may reflect random variation or something scientifically interesting.

The sample follows that rule. Age 240 fails the range test, so the value is set to missing and the row is flagged, not deleted, since Dev Patel's other fields may be fine and the true age is unknown. NIST's box plot fences give a common statistical flag: a value more than 1.5 times the interquartile range beyond the quartiles is a mild outlier, and 3 times is extreme.

Excel's data validation can stop bad values at entry, with rules such as whole numbers, decimals, dates, lists and text length. In PostgreSQL, a CHECK constraint rejects values that fail a Boolean expression when data is written, and an earlier post on creating SQL tables covers how tables and their rules are defined. Neither fixes values already in the table. Clean data is also what makes an experiment readable: a conversion test run on duplicated or mistyped records misleads, as covered in A/B testing explained.

What data cleaning mistakes should be avoided?

Most cleaning mistakes either destroy information or hide a problem.

• Cleaning the only copy. Microsoft's steps begin with a backup of the original data.
• Removing duplicates before standardising text, so near-identical rows survive.
• Casting without guarding placeholders, so '' and 'n/a' become 0 in SQLite.
• Wrapping formulas in IFERROR everywhere. It catches all seven error types Microsoft lists, so a real formula mistake can be hidden along with the expected errors.
• Filling blanks with 0 or the mean without noting the effect on averages and variance.
• Deleting outliers because they are inconvenient.

The sample here has six rows, so none of this shows how a rule behaves on a large table. Test each rule on a copy, compare row counts before and after, and keep the raw data. For a data source cleaned again and again, Microsoft suggests recording a macro or writing code to automate the process.

How does data cleaning fit into a data analytics programme?

Cleaning is the step between receiving data and trusting a result, and it sits near the start of the data analyst roadmap for 2027. The Certification Program in Data Analytics with AI and Gen AI is an online programme from E&ICT Academy, IIT Roorkee, run with Masai as the implementation partner, with live sessions and an optional campus immersion. It covers SQL, Python, Google Sheets, prompt engineering, LLM tools and agentic workflows with LangChain and CrewAI, and a capstone project. Its listed topics include NumPy, pandas, visualisation and AI-assisted data cleaning.

Frequently asked questions

What are the steps of data cleaning?

The steps are to profile the data, fix data types, standardise text, remove duplicates, handle missing values, validate ranges and document every change. Standardising text comes before removing duplicates, because variants such as " Asha Rao " and "asha rao" only count as duplicates once case and spacing are made consistent.

How do you handle missing values?

Leave them missing, fill them or delete the rows, and record the choice. Tools such as PostgreSQL's AVG skip NULLs, so leaving values missing often works. Filling with 0 changed an average from 1337.5 to 1070.0 in the sample. Deleting rows is safe for unbiased estimates only when values are missing completely at random.

Should you remove outliers?

Only when they are shown to be errors. NIST's guidance is to delete or correct an outlying value if it is determined to be erroneous, and not to simply delete it when that cannot be determined. A safer default is to flag the value, check its source, and report results with and without it.

How do you remove duplicates in Excel?

Select the data and use Data, Data Tools, Remove Duplicates. Excel compares the values that appear in cells, keeps the first occurrence and deletes the rest permanently. Clean text with TRIM and PROPER first, filter for unique values to preview the result, and keep a copy of the original range.

Why does TRIM not remove every space in Excel?

TRIM was designed for the ordinary 7-bit ASCII space, character value 32. Text copied from web pages often contains the non-breaking space, character value 160, which TRIM does not remove by itself. SUBSTITUTE can replace character 160 with a normal space, and CLEAN removes nonprinting characters.

Which is better for data cleaning: Excel, SQL or Python?

Each fits a different job. Excel suits one-off fixes on a small sheet, SQL cleans data where it already sits in a database, and pandas keeps every step in a script that can be rerun. Microsoft itself suggests macros or code for data that must be cleaned repeatedly.

Next step

Take any small spreadsheet of your own, copy it as a raw backup, and apply the seven steps in order, noting each change. The Certification Program in Data Analytics with AI and Gen AI covers SQL, Python and Google Sheets for readers who want a structured path.