Cleaning Data

An accurate-looking table can still give the wrong answer. Cleaning starts with the meaning of its records: what happened, who or what each row describes, and which fields the analysis needs.

What data integrity means

Data integrity means accuracy, completeness, consistency and trustworthiness throughout the data's life cycle. Values must match the facts they claim to record. Required information must be present, and the same fact must keep the same meaning as it moves between systems.

Integrity can break during entry, storage, transfer or analysis. Someone may mistype a quantity. Copies in two systems can drift apart, a problem called replication. An interrupted transfer can load half a file. Sorting one column alone can attach each amount to the wrong order. System failures and unauthorized changes can also damage records.

Recognize the problem before fixing it

Dirty data is incomplete, incorrect, inconsistent or irrelevant to the question. Different problems need different actions.

A neat format does not establish accuracy. A delivery date can look like a date but still occur before the order date. A test customer can have a complete record but be irrelevant to actual customer sales.

Follow the effect on an answer

Suppose order 1003, worth 250.00, appears twice in an export. Counting rows adds one extra order, and summing amounts adds 250.00 too much. The formulas can work perfectly while the input gives a false result. Text variants create another problem: a chart can divide Lisbon's sales between separate city labels.

Clean data is fit for the analysis being done. A missing middle name has no effect on sales by region. A missing order amount does affect total sales. Judge completeness against the fields needed for the question, then record gaps that remain.

Protect the starting point

Keep an untouched raw copy before making changes. Use another file, a separate sheet or the original database table, and clean a working copy. A tool such as Remove Duplicates deletes rows from its selected range; the raw copy gives you something to compare with and a way to redo a decision.

Work through five stages: inspect, decide, apply, document and verify. Record a problem before choosing its treatment. Record each treatment when you apply it, then check the result against the raw data. Internal data needs the same inspection as an external file: repeated imports and changed field meanings happen inside organizations too. Tell the data owner about recurring entry or export problems so the next delivery improves.

Data profiling is a systematic inspection of a dataset before changing it. It gives you a baseline and a problem list: the size of the table, the contents of each column, gaps, repeated keys and values that need investigation.

Start with the table's structure

Identify what one row means, such as one order or one customer. Check for one header row and one kind of information per column. Find the key, the column or combination of columns that should identify one record. A repeated order ID needs investigation; repeated city names are expected.

Record the starting row count. Then inspect each column's expected type, required status and allowed values. A data dictionary or the data owner supplies these meanings. The minimum and maximum can reveal a date outside the reporting period or a placeholder such as 999 in an age column.

Inspect a spreadsheet

Sort and filter the table, keeping whole rows together. Sort ascending and descending to find blanks, unexpected text and extreme values. Filter dropdowns reveal category spellings. Excel filters ignore case, so a single displayed category can hide capitalization differences; use EXACT when case itself needs checking. Conditional formatting can highlight repeated keys and values outside a range.

For rows 2 through 501, useful checks include:

• =COUNTA(B2:B501) counts nonempty cells.
• =COUNTBLANK(B2:B501) counts blanks, including formulas that return empty text.
• =COUNT(B2:B501) counts numeric cells.
• =MIN(B2:B501) and =MAX(B2:B501) show the numeric range.
• =COUNTIF(C2:C501,"Lisbon") counts matching entries, ignoring case.

If an amount column's numeric count is smaller than its nonempty count, inspect the difference. Some entries may be numeric text; others may be labels such as "N/A". A space-only cell also needs attention even though it looks blank.

Inspect with SQL

These queries read the data without changing it:

SELECT COUNT(*) AS rows_total,
       COUNT(amount) AS amounts_present,
       COUNT(*) - COUNT(amount) AS amounts_missing,
       MIN(order_date) AS first_date,
       MAX(order_date) AS last_date
FROM orders;

COUNT(*) counts rows. COUNT(amount) counts non-NULL amounts. Their difference counts missing amounts represented by NULL, but it does not detect text placeholders.

To inspect category values and their frequency:

SELECT city, COUNT(*) AS n
FROM orders
GROUP BY city
ORDER BY city;

GROUP BY collects equal values; COUNT(*) counts the rows in each group. NULL forms its own group. Text equality follows the database's comparison settings, called collation. PostgreSQL and SQLite ordinarily separate case variants; common MySQL settings combine them.

Turn checks into a problem list

For six amounts 40, NULL, 25, 60, NULL and 35, the row count is 6 and the present count is 4: two amounts need attention. For cities "Lisbon", "lisbon ", "Porto", NULL, "LISBON" and "Porto", exact text comparison finds four non-NULL spellings, although only two cities are represented.

Write down both findings, along with the affected column and count. Inspect frequent values and extremes for hidden placeholders, test records and dates such as "1900-01-01". Profiling the whole table first also reveals dependencies: trimming text can change the duplicate count, so duplicate removal should wait until the comparison fields are standardized.

Data validation checks whether entries follow rules for acceptable data. An entry can be present and neatly displayed while breaking a rule. A quantity of -3 is still invalid when order quantities must be positive.

Choose rules from the data's meaning

Read the data dictionary or ask the data owner. A hotel day-use booking may allow zero nights; another booking system may require at least one. Write the actual limit and whether the field is required.

A format check tests a pattern, not whether the underlying fact is true. A basic email check for one "@" and text on each side cannot confirm that a mailbox exists.

Flag failures in a spreadsheet

Put a test in a helper column and fill it down. For a required numeric percentage in B2, =AND(ISNUMBER(B2),B2>=0,B2<=100) checks both type and range. The type test matters because a blank can behave like zero in a comparison.

With real dates in C and D, =IF(D2<C2,"check","ok") flags a delivery before its order. Check required dates for blanks separately. For IDs in column A, =COUNTIF($A$2:$A$501,A2)>1 flags repeated values. Filter the helper results to see every failure.

List failures with SQL

SELECT * FROM orders
WHERE quantity <= 0 OR quantity IS NULL;

This finds nonpositive and missing quantities. To check statuses:

SELECT * FROM orders
WHERE status NOT IN ('open', 'shipped', 'returned')
   OR status IS NULL;

The explicit NULL check matters. NOT IN alone does not return a row with a NULL status, because its comparison is unknown. A cross-field check such as delivery_date < order_date also needs separate presence checks for required dates.

Prevent new errors and resolve old ones

Excel's Data Validation and Google Sheets' Data validation can provide a dropdown, restrict a number range, or warn about an entry. Database types and constraints such as NOT NULL and UNIQUE enforce related rules. Adding a rule does not repair old records; inspect those separately. Excel's Circle Invalid Data can mark existing failures.

Consider adult survey ages 34, 0, 41, 230 and "forty", with a whole-number range of 18 to 120. "forty" fails the type check; 0 and 230 fail the range check. The word has the clear numeric meaning 40. The intended value of 230 is uncertain, so do not guess between 23 and 30. Recover a reliable value, mark it missing or exclude the record, and log the decision.

An unusual value is different from an invalid value. An order of 500 units may be real. Validate it against the order rules and source record before deciding it is an error.

Two similar rows are not necessarily one record entered twice. A duplicate describes the same real-world entity or event as another row. Begin by deciding what identifies that entity or event.

Define the key and comparison rules

An order ID can identify an order. A name alone is usually a poor customer key because different people can share it. Even two identical purchases on the same day can be real separate events. Check the row meaning and the data owner's key before deleting look-alike rows.

Exact duplicates match in all columns. Near duplicates refer to the same thing but have small differences, such as an extra space or a changed phone number. Clean the comparison fields before searching. If email is the agreed key, normalize spacing and case in a helper column.

For five emails "ana@example.com", "ben@example.com", "Ana@Example.com ", "chen@example.com" and "ben@example.com", exact raw comparison finds the repeated Ben entry. Trimming and lowercasing also reveals Ana's repeated entry. Three customer keys remain after removing two extra copies.

Flag rows before removing them

For cleaned keys in B2:B501:

• =COUNTIF($B$2:$B$501,B2)>1 flags every row whose key repeats.
• =COUNTIF($B$2:B2,B2)>1 flags only the second and later occurrences.

The second range grows as you fill down, so the first occurrence has a count of one. COUNTIF ignores letter case; trim first to handle extra spaces. Inspect flagged groups for conflicting information before applying a keep rule.

Choose what survives

Excel's Remove Duplicates and Google Sheets' Remove duplicates compare the columns you select. Selecting email alone can discard a row with a different phone number. Selecting all columns keeps that conflict because the full rows differ. Both tools use their own matching rules, including case-insensitive comparisons, so they are unsuitable for enforcing character-by-character case distinctions without an additional check.

Excel keeps the first occurrence. If the latest record should survive, sort the complete rows by update date first, with the newest first. Resolve tied or conflicting records explicitly. A documented rule might keep the most recently confirmed record or merge verified fields. Log both the chosen columns and the number removed.

=UNIQUE(A2:C501) returns distinct rows into a new range in current Excel and Google Sheets. It preserves the source and is useful for previewing a result.

Find repeated keys with SQL

SELECT email, COUNT(*) AS n
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

GROUP BY creates a group for each email; HAVING keeps groups whose count exceeds one. WHERE filters individual rows before grouping. Run the check on standardized keys, either by using a cleaned table or grouping by the cleaning expression.

SELECT DISTINCT email, name FROM customers; returns each email-and-name combination once. It does not guarantee unique emails: two rows with one email and different names both remain. Check the agreed key after deduplication.

Repeated imports and overlapping exports often create duplicates. Record the cause with the data owner so a fix to today's table also improves the next export.

A gap can be visible as a blank or hidden behind a plausible-looking value. Find out how the source records missing information before counting it. A recorded zero may be real, while "unknown" may be a placeholder.

Distinguish blanks, text and NULL

In a spreadsheet, an empty cell, a cell containing a space, and a formula returning "" can look alike. ISBLANK is true only for a genuinely empty cell. A space is text. COUNTBLANK also counts formulas returning empty text, but it does not count a cell containing a space. For ordinary spaces, =TRIM(B2)="" detects both empty and space-only entries.

Spreadsheet SUM, AVERAGE and COUNT skip empty cells and text in a referenced range. They include numeric zero. For amounts 48, 52, a blank, 45, a blank, 55, 50 and 46, the known amounts sum to 296. AVERAGE divides by six, giving about 49.33. Replacing the blanks with zero changes the denominator to eight and the average to 37.

In SQL, NULL marks an unknown or absent value. Zero is a known number. The empty string '' is text with no characters in PostgreSQL, MySQL, SQL Server and SQLite. Imported blanks may appear in either form, so check the stored representation:

SELECT * FROM orders
WHERE city IS NULL OR TRIM(city) = '';

Use IS NULL and IS NOT NULL. amount = NULL does not find missing amounts: comparison with NULL produces an unknown result, which WHERE does not retain.

Know the denominator

COUNT(*) counts all rows. COUNT(amount), SUM, AVG, MIN and MAX skip NULL amounts. For 40, NULL, 25, 60, NULL and 35, there are six rows but four known amounts. SUM is 160 and AVG is 40 because it divides 160 by four.

These functions cannot identify a numeric placeholder such as -1 or 999. Confirm placeholder meanings with the data owner, then count them separately or normalize them to missing. Inspect the frequencies of unusual codes as well as blank counts.

Look for patterns in the gaps

Count missing entries per column and as a share of rows. Two missing amounts out of eight is 25%. Then inspect whether gaps cluster by store, source or month. A missing field confined to one export source may point to an import problem that can be repaired.

The reason for missingness matters:

• Missing completely at random: an unrelated event, such as a random save failure, causes the gap.
• Missing at random: the chance of a gap depends on recorded information, such as older customers skipping an optional online field more often.
• Missing not at random: the chance depends on the missing value itself, such as very high earners leaving income blank.

In the income case, the average among respondents may be lower than the average for everyone. Filling gaps with one number does not remove that selection problem. Report what is missing and who the remaining values describe before interpreting an average.

Missing values need a decision tied to the question being answered. A customer without an age can still contribute to total sales. An order without an amount cannot contribute a known value to revenue.

Choose an action

Try recovery first. Check an invoice, original form or system of record. A line total derived from verified quantity and unit price is recovered information. A value estimated from other customers is an estimate, called imputation.

Preserve what a number means

Filling a category with "Unknown" keeps the row in counts while exposing the gap. Filling a numeric field with zero is justified only when missing actually means zero. A missing return count can mean zero if the source process confirms that convention; an unknown sale amount has no such meaning.

For known amounts 48, 52, 45, 55, 50 and 46, the sum is 296 and the mean is about 49.33. With two blanks changed to zero, the sum remains 296 but the average falls to 37. The extra rows now count as observations of zero.

Mean or median filling also changes the data. The median of the six known amounts is 49. Filling both gaps with 49 adds 98, making the mean of the eight entries 49.25. Those two added values were not observed. Repeated central values make the data look less spread out, so keep a flag identifying them. For strongly skewed values such as incomes, the median often describes a typical value better than the mean.

Apply the rule visibly

In SQL, COALESCE(city, 'Unknown') returns the first non-NULL argument. It fills NULL cities but leaves an empty string unchanged. NULLIF(city, 'N/A') turns exactly that placeholder into NULL. Combining normalization and filling makes the treatment explicit:

COALESCE(NULLIF(TRIM(city), ''), 'Unknown') AS city

A spreadsheet category helper can use =IF(TRIM(B2)="","Unknown",B2). A separate flag can use =TRIM(B2)="" so the original missing status remains visible after filling.

Match exclusions to the analysis

For total revenue from 1,000 orders with three missing amounts, seek the invoices first. If recovery fails, report that the total excludes 3 of 1,000 orders, or 0.3%. Do not discard a row because an unused field is blank.

If 400 of 1,000 customer ages are missing, filling with the mean would turn 40% of the column into estimates. An analysis of the 600 known ages needs a clear statement about its coverage. Check why those ages are absent: deleting rows can also distort results when those customers differ from the rest. Keep the treatment consistent and document any later change to it.

Text cleaning makes values with the same meaning comparable. Start by listing the distinct values and their counts. That reveals whether a column needs spacing changes, consistent case or a mapping from several spellings to one label.

Remove spaces and hidden characters

In Excel and Google Sheets, =TRIM(A2) removes spaces at both ends and reduces repeated internal spaces to one. It turns " Ana Silva " into "Ana Silva". =CLEAN(A2) removes nonprinting characters such as line breaks.

A nonbreaking space copied from a web page can survive TRIM. Replace character 160 with an ordinary space first:

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

Read nested functions from the inside out: SUBSTITUTE runs first, then TRIM acts on its result. Inspect cleaned examples because removing a line break can join words that should stay separate.

SQL TRIM(city) removes spaces at the ends but leaves repeated internal spaces. TRIM(' Ana Silva ') therefore retains three spaces between the names. LTRIM changes the left end and RTRIM the right. Choose a cleaning expression that matches the characters you found.

Standardize case deliberately

UPPER and LOWER work in both tools. Lowercase can be a useful email convention; uppercase can suit country codes. A spreadsheet's PROPER capitalizes words, but it cannot establish the correct spelling of a name: "mcdonald" becomes "Mcdonald" and "PT123" becomes "Pt123". Review names and keep codes in their intended case.

Text comparisons follow tool settings. Excel's COUNTIF, VLOOKUP and filters ignore case. PostgreSQL ordinarily treats "Lisbon" and "LISBON" as different text values. Spreadsheet =EXACT(A2,B2) checks identical text including case. Standardize explicitly when the same category must work consistently across tools, while preserving meaningful case distinctions in case-sensitive identifiers.

Map whole values to one category

Trimming and case changes do not fix different spellings. A mapping table can pair each variant with a standard label. Look up the complete cleaned value with exact match, and inspect values that have no match.

In SQL, CASE can express the same mapping:

CASE
  WHEN UPPER(TRIM(country)) IN
       ('NL', 'NETHERLANDS', 'THE NETHERLANDS', 'HOLLAND')
    THEN 'Netherlands'
  ELSE TRIM(country)
END AS country

The ELSE preserves unmapped values for review. Count the remaining categories after mapping; do not assume the first few rows represent all spellings.

Avoid replacement damage

SUBSTITUTE, Find and Replace, and SQL REPLACE can replace text inside a longer value. Replacing "NY" everywhere changes "Store NYC, NY" to "Store New YorkC, New York". For category labels, match the entire cell or use a mapping table. For addresses and names, inspect the intended scope of each replacement.

For cities "Lisbon", "lisbon ", "Porto", a missing value, "LISBON" and "Porto", UPPER(TRIM(city)) produces LISBON three times and PORTO twice, with one gap. Analyze the cleaned column, then use it for matching and duplicate checks. Keeping the helper while grouping the raw city would preserve the original problem.

A combined text value can hide information needed for sorting or matching. Put each required piece in its own column, while preserving the original until the split is checked.

Split at a reliable separator

A delimiter marks the boundary between parts. A comma in "Silva, Ana" separates the last name from the first name. Excel's Text to Columns and Google Sheets' Split text to columns let you choose that separator. Insert empty columns to the right before splitting so the output does not overwrite other fields.

Google Sheets can use =SPLIT(A2,","); current Excel can use =TEXTSPLIT(A2,","). Trim the returned pieces if a space follows the comma. Check rows with a missing delimiter or more than one delimiter before applying one rule to the entire column.

Do not split every name at a space. "Ana Maria Silva" has more parts than a two-word name, and a space alone does not establish which words belong to a first or last name. Use a defined source layout or keep a full-name field when the structure is uncertain.

Extract by position when the layout is fixed

For product code "PT-LIS-0042", the country occupies positions 1–2, the location positions 4–6, and the final code positions 8–11.

Position counting starts at one. Preserve the extracted code as text so its leading zeros survive. Fixed positions work only when the field layout is consistent.

For variable-length text, locate the delimiter. In "Silva, Ana", FIND(",",A2) returns 6. =LEFT(A2,FIND(",",A2)-1) takes the five characters before the comma. The subtraction excludes the comma itself. FIND is case-sensitive; SEARCH ignores case when searching letters.

Use the equivalent SQL function

SUBSTRING(code, 4, 3) extracts the middle location code in PostgreSQL; some databases use SUBSTR. Character-length functions also vary: PostgreSQL and SQLite use LENGTH, MySQL uses CHAR_LENGTH, and SQL Server uses LEN, which ignores trailing spaces. MySQL LENGTH counts bytes, so it can differ from the character count for text such as "é".

A PostgreSQL check WHERE LENGTH(code) <> 11 finds wrong-length codes. A length check alone does not establish that the letters, digits and dashes are in the correct places.

Combine parts with a clear separator

=A2&" "&B2 or =CONCATENATE(A2," ",B2) joins two spreadsheet fields with a space. =TEXTJOIN(" ",TRUE,A2:C2) joins a range while skipping empty cells. Excel CONCAT accepts multiple arguments; Google Sheets CONCAT takes exactly two.

In PostgreSQL, first_name || ' ' || last_name concatenates text. With this operator, a NULL part makes the result NULL. CONCAT handles NULL differently across dialects: MySQL propagates it, while PostgreSQL and SQL Server skip it. Treat optional parts deliberately with COALESCE and check the resulting separators.

A consistent display is useful only when the values mean the same thing. Dates need a known order, numbers need known separators, and measurements need one unit before calculations begin.

Establish the source convention

The text "03/04/2025" can mean March 4 or April 3. A date such as "25/03/2025" establishes day-first order for that entry, but a combined file can contain several formats. Confirm the convention with the data owner or export settings and check for mixed sources before converting ambiguous entries.

Use ISO 8601 date order, YYYY-MM-DD, for clear exchange: "2025-03-04" names March 4. Uniform ISO date text also sorts chronologically. Use the 24-hour clock for times, such as "14:30", and record a time zone when events span zones.

Separate stored values from display

Spreadsheets store real dates as serial numbers. In Excel's default date system and Google Sheets, January 1, 2025 is 45658 and March 4, 2025 is 45720. Formatting that numeric date as YYYY-MM-DD changes how it appears; it does not change the day recorded.

Text that looks like a date is different. Applying a date format to text does not create a real date value. Check the type and convert the text using the confirmed source layout before formatting it. Alignment can be a clue, but type checks and calculation behavior give stronger evidence because alignment can be changed manually.

Number formatting works the same way. A stored value of 0.125 can display as 0.13 with two decimal places while calculations still use 0.125. =ROUND(A2,2) changes the numeric result to two decimal places. Keep full precision through cleaning unless the field definition requires a rounded value: three thirds rounded separately to 0.33 sum to 0.99.

Read number separators correctly

"1,234.5" and "1.234,5" express the same number under different conventions. Importing with the wrong locale can change the magnitude or leave a value as text. Compare several imported values with their source, including values with decimal and grouping separators.

In Excel, =NUMBERVALUE("1.234,5",",",".") explicitly names the decimal comma and grouping point and returns 1234.5. Set the import locale correctly in other tools. Do not delete every comma without checking its role: it may separate thousands or mark the decimal part.

Convert units before combining values

A weight column containing "2.5 kg", "750 g" and "1.2 kg" mixes labels and units. Extract the number and unit, convert grams to kilograms, and store 2.5, 0.75 and 1.2 in a numeric weight_kg column.

Adding the raw numeric pieces gives 753.7, which has no consistent unit. Keep one unit in the column header and numbers in the cells. A monetary column likewise needs one currency before totals or averages have a shared meaning; record any conversion rule used.

After conversion, inspect minimum and maximum dates and measurements. Check individual ambiguous dates against the source even when they fall within the allowed period: swapping month and day can produce a valid but incorrect date.

A data type tells software how to treat a value. Numeric quantities support arithmetic, dates support date sorting and differences, text holds labels and identifiers, and Boolean fields hold TRUE or FALSE. Imports often turn intended numbers or dates into text.

Diagnose the type before changing it

Numeric text may align left by default, show an Excel warning or sort "10" before "9". Check the whole column with COUNT and COUNTA. In a referenced range containing numeric 120, text "80" and numeric 45, SUM gives 165 and COUNT gives 2. The intended total is 245, so the type error is affecting the calculation.

Changing the display format to Number does not convert existing text. Make a helper column, convert there, and compare the result with the raw entry.

Convert spreadsheet text

=VALUE(A2) parses numeric text using the spreadsheet locale. =DATEVALUE(A2) parses date text using its recognized date conventions. Confirm separators and date order first.

For a known day-first value "04.03.2025", construct a date from its parts. Explicit VALUE calls make the inputs numeric in both Excel and Google Sheets:

=DATE(VALUE(RIGHT(A2,4)),VALUE(MID(A2,4,2)),VALUE(LEFT(A2,2)))

RIGHT returns the year text "2025", MID returns month text "03", and LEFT returns day text "04". VALUE converts each part; DATE builds March 4, 2025. Validate the parts first: DATE can roll an impossible day or month into a later date instead of reporting an error.

For a value such as " 1250 kg", remove the known unit label and spaces before conversion: =VALUE(TRIM(SUBSTITUTE(A2,"kg",""))). Do not strip arbitrary letters from every field, because letters may be meaningful or may indicate an invalid entry. =IFERROR(VALUE(A2),"check") keeps failed conversions visible for review.

Keep identifiers as text

The code "00427" identifies something; it is not a quantity to add. Conversion to a number gives 427 and loses the leading zeros. Phone numbers, many postal codes and account identifiers also belong in text columns. Set the type before importing. Excel preserves only 15 significant digits in numeric storage, so a 16-digit identifier can lose information before you notice it. Formatting it as text afterwards cannot recover the lost digits.

Convert with SQL and inspect failures

In databases such as PostgreSQL, CAST(amount_text AS DECIMAL(10,2)) parses numeric text and returns a decimal with two places. CAST(quantity_text AS INTEGER) requests a whole number. ISO text can be cast to DATE in many dialects; other date layouts need a matching parser such as PostgreSQL TO_DATE, MySQL STR_TO_DATE or BigQuery PARSE_DATE.

Invalid text often stops a CAST query. SQL Server TRY_CAST and BigQuery SAFE_CAST instead return NULL for failed ordinary conversions. Count these failures separately from values already missing. For example, in SQL Server:

SELECT amount_text FROM sales
WHERE TRY_CAST(amount_text AS DECIMAL(10,2)) IS NULL
  AND amount_text IS NOT NULL;

SQLite can quietly cast "12abc" to 12 and "abc" to 0. A successful query is therefore insufficient evidence of a correct conversion. Check the original strings, converted values, counts and ranges before accepting the clean column.

A spreadsheet workflow makes each change visible and keeps enough evidence to repeat it. Use a raw sheet, a working sheet with helpers, and a checked clean table.

Set up and order the work

Keep raw data untouched. Freeze the header row and use a table or filters to select the complete data range. When sorting, move whole rows together so names, IDs and amounts remain linked.

A practical sequence is:

1. Profile the table and write a problem list.
2. Fix headers and split fields that contain several required pieces.
3. Trim text, standardize case and map category variants.
4. Convert types and unify date, number and unit formats.
5. Treat missing values, preserving flags when needed.
6. Remove duplicates using the agreed key and keep rule.
7. Validate, reconcile and spot-check the result.

This order handles dependencies. A duplicate check on raw keys can miss extra spaces. A range test on numeric text may not test the intended values. Record the effect after each step so a later mismatch can be traced to its cause.

Build and inspect helper columns

If B2 contains " ana SILVA", =PROPER(TRIM(B2)) returns "Ana Silva". Fill the formula through the full table and compare raw and cleaned names. Review spellings that PROPER cannot infer correctly.

A helper such as =UPPER(TRIM(SUBSTITUTE(B2,CHAR(160)," "))) works inside out: replace nonbreaking spaces, trim ordinary spaces, then change case. Use descriptive helper headers such as city_clean so later formulas point to the intended column.

Filter results and check values at the beginning, middle and end of the range. A formula copied through only part of the table leaves a mixed column. Look for errors as well as blanks.

Map categories with exact lookup

Keep a two-column mapping table of variant and standard label. With a prepared variant in B2, =VLOOKUP(B2,Map!$A$2:$B$50,2,FALSE) finds the matching label. The absolute references keep the mapping range fixed when filled down. FALSE requests exact match; approximate matching can return a plausible but incorrect category for an unlisted value.

XLOOKUP can use separate lookup and result ranges. Its default is exact match. An unmatched variant should stay visible as an error for review. Add or correct its mapping instead of converting every lookup failure into a familiar category.

Finalize and document

After checking helpers, copy their results and use Paste Special > Values in Excel or Paste special > Values only in Google Sheets to create the clean table. Test that it is independent before deleting source columns: formulas that still refer to deleted cells can return #REF!.

Built-in tools such as Remove Duplicates, Find and Replace and Text to Columns can change many cells at once. Log their options, selected range and affected count while the information is visible. Formula helpers show their logic directly; retain that working sheet with the log.

English formulas here use commas. Spreadsheet language and locale can change function names and separators; for example Dutch Excel uses SPATIES.WISSEN for TRIM, and decimal-comma locales often use semicolons between arguments.

Excel permits 1,048,576 rows and 16,384 columns per worksheet; Google Sheets limits cells per file. Performance can become a problem well before a hard limit. Frequently refreshed data or data already in a database often fits a saved SQL workflow better than repeated manual spreadsheet edits.

A SQL cleaning query can return usable data without changing the raw table. Each expression makes a rule explicit, and an alias names the cleaned output.

Build the result a column at a time

The following query is suitable for PostgreSQL when amount and date text have already passed conversion checks and quantity is numeric:

SELECT DISTINCT
  order_id,
  LOWER(TRIM(email)) AS email,
  COALESCE(NULLIF(UPPER(TRIM(city)), ''), 'UNKNOWN') AS city,
  CAST(amount AS DECIMAL(10,2)) AS amount,
  CAST(order_date AS DATE) AS order_date
FROM raw_orders
WHERE order_id IS NOT NULL
  AND quantity > 0;

Email is trimmed and lowercased. For city, TRIM removes end spaces, UPPER standardizes case, NULLIF turns empty text into NULL, and COALESCE supplies "UNKNOWN". The casts return numeric amounts and dates. WHERE excludes missing IDs and nonpositive or NULL quantities. DISTINCT removes repeated selected result rows.

Profile the output after each added expression. A complicated query can run successfully while a mapping or conversion is wrong.

Understand filtering order

The useful logical order is FROM, WHERE, SELECT, DISTINCT, ORDER BY. WHERE sees source columns before the SELECT expressions produce their aliases. An alias does not make a raw column clean inside the same query's WHERE clause.

To filter a raw city consistently, use WHERE UPPER(TRIM(city)) = 'LISBON', or save the cleaned result and filter that result in a later query. Filtering raw city with city = 'LISBON' can miss variants even when SELECT cleans the city it displays.

CASE checks conditions in order and returns the first matching result:

CASE
  WHEN LOWER(TRIM(status)) IN ('shipped', 'sent') THEN 'shipped'
  WHEN status IS NULL THEN 'unknown'
  ELSE LOWER(TRIM(status))
END AS status

Without ELSE, an unmatched value becomes NULL. Keep unmapped statuses visible until their meaning is resolved.

Choose between filtering and flagging

WHERE removes a row from the result. A flag keeps the row and marks an issue, for example CASE WHEN delivery_date < order_date THEN 1 ELSE 0 END AS date_issue. Check required dates separately for NULL.

Count excluded rows explicitly:

SELECT COUNT(*) FROM raw_orders
WHERE order_id IS NULL
   OR quantity <= 0
   OR quantity IS NULL;

This accounts for every reason the earlier filter rejects a row, including missing quantities. After DISTINCT, check the business key with GROUP BY and HAVING. Rows sharing an ID but holding different amounts survive DISTINCT and still need resolution.

Save the method and choose the right dialect

CREATE VIEW clean_orders AS SELECT ... saves a query that reads current source data each time the view is used. CREATE TABLE clean_orders AS SELECT ... creates a stored result in many dialects; SQL Server commonly uses SELECT INTO. Keep the query and comments so you can rebuild that result when inputs change.

Function names and behavior vary across SQL systems. Check the dialect for date parsing, character length and safe conversion. UPDATE and DELETE alter stored records: preview the same WHERE condition with SELECT and have a backup before applying either. Without WHERE, they affect every row. Cleaning through SELECT is a useful default for analysts with read-only access.

A clean table needs an explanation of how it was made. Documentation lets another person trace a reported value, judge a decision and rerun the work on the next delivery.

Write a useful changelog entry

A changelog is a dated record of changes. Each entry should identify:

• The date and author.
• The dataset and affected column or range.
• The problem and its original scale.
• The action, including formula, query or tool options.
• The number of rows or values affected.
• The reason and any source used to confirm a correction.

For example: "2025-03-10, A. Silva, orders.city: 412 values trimmed and set to uppercase with UPPER(TRIM(city)); 9 spellings reduced to 4 cities; needed for sales by city."

That entry explains both the method and its purpose. "Cleaned cities" does not identify the comparison rule, scope or count. Record entries while working so the tool's affected-row message and the reason for a decision are still available.

Record decisions as well as operations

A formula does not explain why it was appropriate. For duplicates, name the key columns and which record survived. For missing amounts, say whether they were recovered, excluded, filled or left missing. If you filled values, record the estimate rule and flag column. For a correction, identify the invoice or source record that supplied the true value.

Log flagged-but-retained rows too. A reviewer needs to distinguish a known issue you kept visible from an issue the cleaning missed. If a treatment changes later, add a dated entry explaining the new decision.

Keep the method and its history

Spreadsheet cell notes can explain one correction, while a Changelog sheet holds the complete sequence. Google Sheets version history can restore earlier file states. Excel Version History is available for files stored in OneDrive or SharePoint. History helps recover a file, but a separate log explains the intent of the changes.

SQL comments can explain a rule next to its expression. Use -- for one line or /* ... */ for a longer comment. Keep query versions in version control or storage with version history. A plain shared folder makes the current query accessible but does not establish earlier versions.

Make rerunning possible

A process is reproducible when the same raw input and rules produce the same clean output. Saved queries and retained helper formulas expose their rules. A manual cell edit needs the record ID, old value, new value and reason if someone is to repeat it accurately.

Keep the raw copy, transformation steps, mapping tables and log together. If a rule depends on a keep order, such as retaining the latest customer record, save the sort or selection rule too. Reproducing a result requires the decisions as well as the software expressions.

Share the log and a short cleaning summary with the people who use the data. Someone interpreting results needs to know that test orders were removed or that ages are missing for part of the customer base. Send recurring source errors to the data owner so they can fix the form, import or export that creates them.

Verification asks whether the cleaning fixed the problems without creating new ones. It also checks that the remaining data still answers the original question. Use whole-column tests and comparisons, then inspect individual records.

Reconcile row counts step by step

Start with the raw row count. Subtract rows removed at each stage, counting each removal once. For 1,250 raw rows, 37 duplicate copies removed and 12 later invalid rows excluded:

The expected clean count is 1,201. A different count needs an explanation before release. Check the step where the count first diverged. A filter may have hidden rows from a spreadsheet operation, or a selected range may have omitted part of the table.

If one row has several problems, do not subtract it several times. Use the actual row count before and after each operation. Keep distinct counts for rows removed and cells corrected: changing ten city cells does not remove ten rows.

Repeat the profile and rules

Run the original checks on the clean output. Compare each result with the decision recorded in the log:

• Missing counts match the values left missing, recovered or filled.
• Category lists have the intended standard spellings.
• Numeric and date minimums and maximums are valid.
• The key has no unresolved duplicates.
• Type, presence, range and consistency checks have no unexplained failures.

A zero blank count after filling does not mean no values were missing. Check the flags too. A repeated business key can remain after DISTINCT when one selected field differs, so test that key directly.

Reconcile amounts and inspect records

When no values were corrected or filled, the clean total should equal the raw total minus the amounts in removed rows. A raw total of 152,400 less 3,100 in removed duplicate orders should leave 149,300. If values were corrected or filled, include those changes separately in the reconciliation.

Compare records from several parts of the table with the raw data and, where possible, the original source. Target the rows affected by risky steps: replacements, splitting, parsing and type conversion. Inspect names for overbroad replacement, columns for shifted split output, identifiers for lost zeros, and dates for swapped day and month. A plausible date can still be the wrong date.

Check the question's coverage

Make sure the clean data still covers the required period, regions, stores, products and fields. Dropping all orders from a store because its dates use another layout changes the population being analyzed. Fix the parsing rule when possible, and report any coverage that remains missing.

Write a short report naming the source and period, problems and counts, actions taken, exclusions or fills, verification results and remaining limits. For example: "Age is missing for 12% of customers; results by age describe the other 88%." A result should be interpreted using that coverage.

Rerun both cleaning and verification when new raw data arrives. Saved rules make the process repeatable, but new categories, changed formats and new missing-value patterns still need review.