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
- 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.
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.
- sales-practice-data.xlsx · sheets Sales Data, Sales Clean and Summary
- sales-practice-data.csv · the same raw data as CSV
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.
- Copy the raw data
- Profile it
- Fix each problem
- Validate
- 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:
| Check | Formula (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:
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.
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?
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:
| Option | What it does here | Effect on total revenue |
|---|---|---|
| Delete the rows | Loses 4 real orders | Understates by $357 |
| Fill with the average order ($448.68) | Invents $1,794.70 of revenue | Overstates by $1,437.70 |
| Leave blank and flag | Totals ignore them | Understates by $357 |
| Recalculate from other columns | revenue = units × unit_price | Exact |
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?
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:
| Order | Raw value | Problem | Correct value |
|---|---|---|---|
| SO-10017 | NY | Abbreviation | New York |
| SO-10039 | TX | Abbreviation | Texas |
| SO-10083 | CA | Abbreviation | California |
| SO-10087 | N. Carolina | Shortened name | North Carolina |
| SO-10115 | florida | Lowercase | Florida |
| SO-10125 | california | Lowercase | California |
| SO-10185 | "Texas " | Trailing space | Texas |
=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?
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.
"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.
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?"
| # | Issue | Rows affected | Action | Revenue impact |
|---|---|---|---|---|
| 1 | Exact duplicate rows | 3 | Removed with Remove Duplicates (all columns) | −$853 |
| 2 | Blank revenue | 4 | Recalculated as units × unit_price | +$357 |
| 3 | Inconsistent state names | 7 | Mapped abbreviations; TRIM + PROPER | $0 |
| 4 | Date stored as text | 1 | Converted 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?
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.
- Explain the difference of one order and $649.
- Why did "california" (lowercase) get counted, but another California order didn't?
- Which cleaning step fixes it?
See solution
- 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.
- 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.)
- 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.
Create your account in 30 seconds, mark your lessons and download your certificate at the end.