Lesson 5 of 16 · Level 2: Intermediate

Data Cleaning: Duplicates, Errors and Missing Values

  • ⏱ 13 min read
  • ❓ 4 review questions
  • ✍️ Exercise with solution
  • 📥 Downloadable template
In this lesson
  1. Step 1: Never clean the original
  2. Step 2: Profile the data before fixing anything
  3. Step 3a: Remove duplicate rows
  4. Step 3b: Handle missing values
  5. Step 3c: Fix inconsistent text
  6. Step 3d: Fix dates stored as text
  7. Step 4: Validate the result
  8. Step 5: Keep a cleaning log
  9. The same cleaning in Python
  10. Summary
What you'll learn
  • How to profile a raw file in minutes and list every problem before fixing anything.
  • How to find and remove duplicate rows safely.
  • Four ways to handle missing values, and why "recalculate" beats "delete" or "fill with the average" here.
  • How to fix inconsistent text (abbreviations, capitalization, extra spaces) and dates stored as text.
  • How to validate the result and document it in a cleaning log.
📥 Practice this lesson

Clean the Sales Data sheet (203 raw rows) yourself, then compare your result with the Sales Clean sheet, which is the answer key. The Summary sheet already has the profiling formulas used below.

Data cleaning means finding and fixing the problems in a dataset (duplicates, blanks, typos, wrong formats) so your numbers are correct. It's not glamorous, but it's where analysts earn trust: one duplicated order or one "TX" instead of "Texas" can change a total, a ranking or a decision. The raw sheet in the practice file contains four kinds of deliberate errors. In this lesson you'll find all of them, fix them and prove the result is clean.

203 → 200rows after removing 3 duplicate orders
$90,231 → $89,735total revenue before and after cleaning
24 → 17different state spellings reduced to the 17 real states
  1. Copy the raw data
  2. Profile it
  3. Fix each problem
  4. Validate
  5. Log every change

Step 1: Never clean the original

Right-click the Sales Data tab and choose Move or Copy → Create a copy (Excel) or Duplicate (Google Sheets). Rename it Sales Work. All fixes happen there. If something goes wrong, the raw data is still intact, and anyone can audit what you changed by comparing the two sheets.

Step 2: Profile the data before fixing anything

Profiling means measuring the data's health with a handful of checks. Write them down first; it's tempting to fix the first error you see and miss the others. The Summary sheet runs these on the raw data:

CheckFormula (raw sheet, rows 2–204)Result
Rows=COUNTA('Sales Data'!A2:A204)203
Unique order IDs=SUMPRODUCT(1/COUNTIF(A2:A204,A2:A204))200
Blank revenue cells=COUNTBLANK(J2:J204)4
Dates stored as text=SUMPRODUCT(--ISTEXT(B2:B204))1

203 rows but only 200 unique IDs means 3 duplicate rows. For text columns, look at the list of distinct values: sort the column, scan the filter dropdown or build a quick pivot table. The state column has 24 different spellings in the raw data (counting uppercase and lowercase as different), but the business only ships to 17 states. Here's the full error inventory:

Errors found in the raw sheet, by type

Inconsistent state names7
Blank revenue4
Duplicate rows3
Date in another format1

Fifteen problem cells or rows out of 203 rows. That's a realistic error rate for a manual export, and enough to make the raw total wrong.

Step 3a: Remove duplicate rows

A duplicate row is the same record entered twice, usually from a double export or a copy-paste. In the raw data, orders SO-10029 ($118), SO-10049 ($35) and SO-10064 ($700) each appear twice, adding $853 of revenue that never happened.

  • Excel: click inside the data → Data → Remove Duplicates → keep all columns checked → OK. Excel reports "3 duplicate values found and removed; 200 unique values remain."
  • Google Sheets: Data → Data cleanup → Remove duplicates.
Same ID, different values? Stop and investigate

Remove Duplicates with all columns checked deletes only rows that are identical in every column. If two rows share an order ID but have different units or dates, that's not a simple duplicate; it could be a corrected order or two different line items. Find out which version is right before deleting anything. Check with =COUNTIF($A$2:$A$204,A2)>1 to flag every repeated ID.

1. You find two rows with order ID SO-20455: one with 3 units, one with 5 units, same date. What should you do?

  • Investigate which record is correct (or whether both are valid line items) before deleting either
  • Run Remove Duplicates on the order_id column only
  • Keep both and add up the units

They're not exact duplicates: the values differ, so one may be a correction or both may be real. Deleting by ID alone could remove the correct record. Ask the data owner or check the source system.

Step 3b: Handle missing values

Four orders have a blank revenue: SO-10025, SO-10052, SO-10142 and SO-10179. You have four options, and the right one depends on the situation:

OptionWhat it does hereEffect on total revenue
Delete the rowsLoses 4 real ordersUnderstates by $357
Fill with the average order ($448.68)Invents $1,794.70 of revenueOverstates by $1,437.70
Leave blank and flagTotals ignore themUnderstates by $357
Recalculate from other columnsrevenue = units × unit_priceExact

The data dictionary says revenue equals units × unit_price, and both are filled in for all four rows. So you can recover the true value instead of guessing. In a helper column of the Sales Work sheet:

=IF(J2="", H2*I2, J2)

Fill it down, copy the column, paste it back over column J as values (Paste Special → Values) and delete the helper. The four recovered revenues are $89, $90, $89 and $89, which add up to $357.

Recalculating isn't always possible. If a survey answer is blank, there's nothing to recompute; then you choose between excluding the row, imputing (filling with a reasonable estimate such as the median of similar rows) or flagging it, and you always say which you chose and how many rows it affected.

2. Why is filling the four blank revenues with the overall average a bad idea in this dataset?

  • Because averages can't be used with currency
  • Because Excel can't calculate an average with blanks in the range
  • Because the average ($448.68) is pulled up by large wholesale orders, while the missing orders were small; it would add $1,437.70 of fake revenue

The four missing orders were worth $357 in total. Four times the average is $1,794.70, which overstates revenue by $1,437.70. When the true value can be calculated from other columns, do that instead.

Step 3c: Fix inconsistent text

Seven state values don't match the standard full name:

OrderRaw valueProblemCorrect value
SO-10017NYAbbreviationNew York
SO-10039TXAbbreviationTexas
SO-10083CAAbbreviationCalifornia
SO-10087N. CarolinaShortened nameNorth Carolina
SO-10115floridaLowercaseFlorida
SO-10125californiaLowercaseCalifornia
SO-10185"Texas "Trailing spaceTexas

=PROPER(TRIM(D2)) fixes capitalization and spaces, but watch out: PROPER turns "CA" into "Ca" and "TX" into "Tx". Abbreviations need a mapping. The cleanest approach is a small two-column table (raw value → correct value) on a sheet called Map, and one formula that uses it when there's a match and PROPER(TRIM()) otherwise:

=XLOOKUP(TRIM(D2), Map!$A$2:$A$5, Map!$B$2:$B$5, PROPER(TRIM(D2)))

With CA, TX, NY and N. Carolina in the map, this single formula fixes all seven rows. If you prefer Find & Replace, tick "Match entire cell contents"; otherwise replacing "CA" would also hit the "Ca" inside "Carolina" and "California".

3. What does =PROPER(TRIM("TX")) return?

  • "Texas"
  • "Tx"
  • "TX"

PROPER capitalizes the first letter of each word and lowercases the rest, so "TX" becomes "Tx". It can't expand abbreviations; you need a lookup table or Find & Replace for that.

Step 3d: Fix dates stored as text

Order SO-10149 (row 153 of the raw sheet) has the date typed as the text "10/15/2025", while every other date is a real date. Text dates sort in the wrong place and disappear from month groupings. Find them with a helper column =ISTEXT(B2) filtered to TRUE, then replace the value with a real date: =DATE(2025,10,15), or =DATEVALUE(B153) if your spreadsheet uses U.S. date settings.

Prefer ISO dates (YYYY-MM-DD)

"10/15/2025" means October 15 in the U.S. but would be invalid in countries that write day first, and "03/04/2025" is ambiguous everywhere. Dates like 2025-10-15 are unambiguous and sort correctly even as text, which is why databases and CSV exports use them.

Step 4: Validate the result

Cleaning isn't done until you've proven it. Run these checks on the cleaned sheet (now rows 2–201):

The total reconciles exactly: $90,231 (raw) − $853 (duplicates) + $357 (recovered blanks) = $89,735.

An outlier is not an error

Order SO-10186 (21 office chairs to Arizona for $5,229) is more than 11 times the average order. It looks suspicious, but units × price checks out and it's a wholesale order. Deleting real but unusual values is one of the worst cleaning mistakes. Investigate outliers; only remove them if you can show they're wrong. You'll learn to detect them formally in the next lesson.

Step 5: Keep a cleaning log

A cleaning log is a short table of what you changed, how and why. It makes your work reproducible and defends your numbers when someone asks "why doesn't this match the raw export?"

#IssueRows affectedActionRevenue impact
1Exact duplicate rows3Removed with Remove Duplicates (all columns)−$853
2Blank revenue4Recalculated as units × unit_price+$357
3Inconsistent state names7Mapped abbreviations; TRIM + PROPER$0
4Date stored as text1Converted to a real date (2025-10-15)$0

The same cleaning in Python

Once you know the steps, you can automate them. This pandas code (covered fully in Lesson 14) turns the raw CSV into the clean table in a few lines and produces exactly the same 200 rows and $89,735 total:

import pandas as pd

df = pd.read_csv("sales-practice-data.csv")
df = df.drop_duplicates()
df["order_date"] = pd.to_datetime(df["order_date"], format="mixed")
state_fix = {"CA": "California", "TX": "Texas", "NY": "New York",
             "N. Carolina": "North Carolina"}
df["state"] = df["state"].str.strip().replace(state_fix).str.title()
df["revenue"] = df["revenue"].fillna(df["units"] * df["unit_price"])
print(len(df), df["state"].nunique(), df["revenue"].sum())   # 200 17 89735.0

Notice the order of operations in the state line: strip spaces, then map abbreviations, then title-case. Title-casing first would turn "CA" into "Ca" and the mapping would miss it, the same trap as PROPER in Excel.

4. After cleaning, which check best proves that no revenue values were typed incorrectly?

  • Checking that the total looks reasonable
  • Sorting revenue from largest to smallest
  • Counting rows where revenue ≠ units × unit_price, which should be 0

A total can "look reasonable" and still be wrong, as the raw file shows. A rule-based check against the definition in the data dictionary tests every single row. In the clean data, the count of mismatches is 0.

Practice exercise

On the raw Sales Data sheet, =COUNTIF(D2:D204,"California") returns 27 and =SUMIF(D2:D204,"California",J2:J204) returns $14,818. In the clean data, California has 28 orders and $15,467 of revenue.

  1. Explain the difference of one order and $649.
  2. Why did "california" (lowercase) get counted, but another California order didn't?
  3. Which cleaning step fixes it?
See solution
  1. The missing order is SO-10083, typed as "CA". It's a wholesale order of 11 Bluetooth speakers at $59: 11 × $59 = $649. $14,818 + $649 = $15,467.
  2. COUNTIF and SUMIF ignore uppercase vs. lowercase, so "california" matched "California". They don't understand abbreviations, so "CA" didn't match. (pandas, by contrast, is case-sensitive: there, "california" would also be missed.)
  3. The state mapping step: XLOOKUP against the Map table (or Find & Replace with "Match entire cell contents") turns "CA" into "California", and TRIM + PROPER handles the lowercase version.

Summary

  • Always clean a copy and keep the raw data untouched.
  • Profile first: count rows, unique IDs, blanks, text dates and distinct text values.
  • Remove only exact duplicates; investigate rows that share an ID but differ.
  • For missing values, recalculate when you can; otherwise exclude, impute or flag, and say which.
  • Map abbreviations before applying TRIM and PROPER, and use real dates (ideally ISO format).
  • Validate with rule-based checks and keep a cleaning log: $90,231 − $853 + $357 = $89,735.

Next lesson: Descriptive Statistics: Mean, Median, Standard Deviation and Percentiles. You'll summarize the clean orders with a few numbers and learn why the average can mislead.

🎓 Save your progress and get a free certificate

Create your account in 30 seconds, mark your lessons and download your certificate at the end.

Enroll for free