How to Clean Data: A Step-by-Step Guide for Excel and SQL

You open the customer export and something’s off. The same customer appears 3 times with slightly different names. Half the phone numbers are blank. Dates show up in 2 formats, and a column of order totals won’t add up because some numbers are secretly text.

Messy data is normal. Every system, import and person who types into a form adds a little chaos. The good news is that most problems fall into a handful of types, and each has a standard fix. This guide walks through an 8-step process for cleaning data, with the exact Excel functions and SQL queries for each step.

Why clean data first

Every report, dashboard and model inherits the problems in its data. Duplicates inflate customer counts. Missing values skew averages. A trailing space makes “Texas” and “Texas ” count as 2 states. If you’re building forecasts, as covered in our guide to predictive analytics, dirty data quietly distorts the results.

Cleaning isn’t glamorous, but it’s where trustworthy analysis starts. The goal isn’t perfection. It’s data accurate enough for the decision at hand, with every change documented.

The 8 most common data problems

Problem	Example	Excel fix	SQL fix
Duplicate rows	Same order imported twice	Data > Remove Duplicates	ROW_NUMBER() and delete rows numbered above 1
Near-duplicates	“Acme Inc” and “ACME, Inc.”	TRIM, UPPER, SUBSTITUTE, then compare	UPPER(TRIM()) and REPLACE() to build a match key
Missing values	Blank email or region	Filter blanks, fill or flag	COALESCE(), IS NULL checks
Placeholder values	“N/A”, “none”, “0000”	Find and Replace	NULLIF()
Extra spaces and hidden characters	“Texas ” or nonbreaking spaces	TRIM, CLEAN, SUBSTITUTE(CHAR(160))	TRIM(), REPLACE()
Inconsistent case	“new york”, “NEW YORK”	PROPER, UPPER, LOWER	INITCAP(), UPPER(), LOWER()
Numbers or dates stored as text	“1,200” won’t sum	VALUE, DATEVALUE, Text to Columns	CAST() or type conversion functions
Inconsistent categories	“CA”, “Calif.”, “California”	Lookup table with XLOOKUP or VLOOKUP	CASE or a join to a mapping table

Before you start: back up and profile

Step 1: Back up and work on a copy

Before you change anything, save the original file or table untouched. Microsoft’s own instructions for Remove Duplicates warn that duplicates are permanently deleted, and recommend copying the original data to another worksheet first.

• Excel: Duplicate the sheet and name it “raw.” Do all cleaning on a separate sheet.
• SQL: Never clean the source table in place. Create a copy or a view:

sql CREATE TABLE customers_clean AS SELECT * FROM customers_raw;

Also start a short change log: a list of every rule you apply, such as “removed 214 duplicate orders based on order ID.” It makes your work repeatable and auditable.

Step 2: Profile the data

Profiling means getting a quick picture of what’s wrong before fixing it. Check row counts, blanks per column, distinct values and ranges.

In Excel:

• Turn on filters (Data > Filter) and click each column’s dropdown to see distinct values and blanks.
• Use =COUNTBLANK(B2:B5000) to count empty cells in a column.
• Add a helper column with =COUNTIF($A$2:$A$5000,A2)>1 to flag IDs that appear more than once, or use a PivotTable to count rows per ID. On large ranges, a PivotTable is faster.
• Highlight duplicates visually with Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

In SQL:

sql SELECT COUNT(*) AS total_rows, COUNT(DISTINCT customer_id) AS unique_customers, COUNT(*) - COUNT(email) AS missing_emails, MIN(order_date) AS first_order, MAX(order_date) AS last_order FROM orders_clean;

Write down what you find. That list becomes your cleaning plan.

Step 3: Remove duplicates

Decide first what makes a row a duplicate. Is it the same order ID? The same customer email? The same name, date and amount? The rule matters more than the tool.

In Excel:

1. Select the data range.
2. Go to Data > Remove Duplicates.
3. Check only the columns that define a duplicate, such as Order ID.
4. Click OK and note how many rows were removed.

Microsoft recommends filtering for unique values first to confirm the result is what you want before removing anything.

In SQL, number the rows within each group and keep only the first. ROW_NUMBER() numbers each row within its partition starting from 1:

sql WITH ranked AS ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY order_id ORDER BY updated_at DESC ) AS rn FROM orders_clean ) SELECT * FROM ranked WHERE rn = 1;

Ordering by updated_at DESC keeps the most recent version of each order.

Near-duplicates (“Acme Inc” vs “ACME, Inc.”) need a match key. Create a helper column that strips spaces, punctuation and case, then check duplicates on that key:

• Excel: =UPPER(SUBSTITUTE(SUBSTITUTE(TRIM(A2),",",""),".",""))
• SQL: UPPER(REPLACE(REPLACE(TRIM(company_name), ',', ''), '.', ''))

Review near-duplicate matches by hand before merging. Automated fuzzy matching can merge records that shouldn’t be merged.

Step 4: Handle missing values

Not every blank is a problem, and not every blank should be filled. For each column, choose 1 of 4 options:

Option	When to use it	Example
Leave blank and flag	The value is unknown and guessing would mislead	Missing phone number
Fill from another source	The value exists elsewhere	Look up region from ZIP code
Fill with a default	A sensible default exists	“Unknown” for a missing lead source
Remove the row	The row is unusable without it	Order with no amount or date

Avoid filling numeric blanks with zero unless zero is actually true. A missing sale amount isn’t a $0 sale.

Placeholder values like “N/A”, “none” or “0000” are disguised blanks. Convert them to real blanks first.

• Excel: Use Find and Replace (Ctrl+H) to replace “N/A” with nothing, matching entire cell contents.
• SQL: NULLIF() returns null when a value equals a placeholder, and COALESCE() returns the first non-null value, which is useful for defaults:

sql SELECT customer_id, NULLIF(TRIM(phone), 'N/A') AS phone, COALESCE(NULLIF(TRIM(lead_source), ''), 'Unknown') AS lead_source FROM customers_clean;

Key takeaway: Decide the rule before you touch the tool. “A duplicate is the same order ID,” “blank lead source becomes Unknown,” “California is always CA.” Written rules make cleaning consistent, repeatable and easy to explain.

Steps 5 and 6: Fix formatting and standardize categories

Step 5: Fix formatting problems

Formatting issues are the most common and the easiest to miss.

Extra spaces and hidden characters. Excel’s TRIM removes all spaces except single spaces between words. But Microsoft notes that TRIM by itself doesn’t remove the nonbreaking space character (value 160), which often arrives in data copied from web pages. Combine functions:

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

CLEAN removes nonprinting characters, and SUBSTITUTE swaps nonbreaking spaces for regular ones before TRIM runs.

In SQL, TRIM(column) removes leading and trailing spaces. Use REPLACE() for specific unwanted characters.

Text case. Use PROPER, UPPER or LOWER in Excel, and UPPER() or LOWER() in SQL (PostgreSQL also offers INITCAP() for title case). Store emails in lowercase so matching works.

Numbers stored as text. These won’t sum and sort strangely. In Excel, use the error-indicator option to convert to number, VALUE(), or Data > Text to Columns and click Finish. In SQL, convert with CAST(amount AS NUMERIC) after removing commas and currency symbols.

Dates. Dates stored as text, or mixed formats like 03/04/2026 and 2026-04-03, cause wrong sorting and wrong month totals. In Excel, use DATEVALUE() or Text to Columns with a date format. In SQL, convert with a date function and a format string appropriate to your database, and store dates in a proper date type, not text.

Splitting and combining columns. Use Excel’s Convert Text to Columns wizard to split “City, State” into 2 columns, and & or CONCAT to combine them. Flash Fill (Data > Flash Fill) can also learn a pattern from a couple of typed examples.

Step 6: Standardize categories

Free-text categories drift: “CA”, “Calif.” and “California”, or “Facebook”, “FB” and “facebook ads.” The fix is a mapping table with every variant and its standard value.

Raw value	Standard value
CA	California
Calif.	California
california	California
FB	Facebook
facebook ads	Facebook

• Excel: Put the mapping table on its own sheet and use XLOOKUP (or VLOOKUP) to return the standard value.
• SQL: Join to a mapping table, or use CASE for a short list:

sql SELECT order_id, CASE WHEN UPPER(TRIM(state)) IN ('CA', 'CALIF.', 'CALIFORNIA') THEN 'California' ELSE TRIM(state) END AS state_clean FROM orders_clean;

Keep the mapping table and reuse it every time new data arrives. This is a small but real piece of data governance.

Steps 7 and 8: Validate, document and automate

Step 7: Validate and check outliers

Now check that values make sense:

• Ranges: No negative quantities, no birthdates in the future, no order dates before you opened.
• Formats: Emails contain “@”, ZIP codes have 5 digits, phone numbers have the right length.
• Totals: Compare the cleaned total against the source system’s report. If revenue changed, you should know exactly why.
• Outliers: Sort amounts high to low and check the top and bottom rows. A $250,000 order at a coffee shop is probably a typo, but confirm before deleting. Some outliers are real.

sql SELECT * FROM orders_clean WHERE quantity <= 0 OR order_date > CURRENT_DATE OR email NOT LIKE '%@%';

Step 8: Document and automate

Finish by updating your change log: what you changed, how many rows were affected and why. Then make cleaning repeatable, because the same problems will be back next month.

• Excel: Power Query (Data > Get & Transform) records cleaning steps and reapplies them when you refresh with new data.
• SQL: Save your cleaning queries as views or scheduled models. If you load data into a warehouse, cleaning usually happens in the transform step. Our guide to ETL vs ELT explains where that fits.
• Prevention: Fix problems at the source with dropdown menus instead of free text, required fields and input validation on forms.

AI tools can speed some of this up, such as suggesting formulas or spotting inconsistent categories. Always check results against your rules.

Where to start

If you have a messy file in front of you right now:

1. Save a copy of the original.
2. Profile it: count rows, blanks and distinct values in key columns.
3. Remove exact duplicates on the column that defines a unique record.
4. Convert placeholders to blanks, then decide how to handle each column’s blanks.
5. Run TRIM, CLEAN and SUBSTITUTE (or SQL TRIM and REPLACE) on text columns.
6. Convert text numbers and dates to proper types.
7. Map categories to standard values.
8. Compare totals with the source and write down what changed.

If you want to build these skills into a career, see how to become a data analyst, and for putting clean data to work, data analytics for small business.

Conclusion

Data cleaning follows the same pattern every time: back up, profile, remove duplicates, handle missing values, fix formatting, standardize categories, validate and document. Excel handles most small and midsize datasets with Remove Duplicates, TRIM, CLEAN, SUBSTITUTE and lookups. SQL handles larger data with ROW_NUMBER, TRIM, NULLIF, COALESCE and mapping tables. Write your rules down, automate them where you can and fix problems at the source so there’s less to clean next time.

Sources

• Microsoft Support: Find and remove duplicates — highlighting and removing duplicates in Excel, and the advice to back up first
• Microsoft Support: TRIM function — what TRIM removes and its limit with nonbreaking spaces
• Microsoft Support: Top ten ways to clean your data — CLEAN, SUBSTITUTE, case functions, text-to-number and date fixes, Text to Columns
• PostgreSQL documentation: Conditional expressions — COALESCE and NULLIF behavior
• PostgreSQL documentation: Window functions — ROW_NUMBER numbering rows within a partition