Ravindra BagaleCourses & study guides Track your progress

Chapter 32 Free

32. Cleaning Dirty Data in Excel

By

Friends, dirty data is normal. CSVs from shops, banks, clinics, IT desks and lorries arrive with spaces, text numbers, odd dates and duplicate rows. In this chapter we clean each mess with Why → What → How, easy examples, a practice workbook, and Power Query for the weekly repeat.

About this data: Practice names (Priya Sharma, Amit Patil, Emma Wilson, Omar Hassan, Sofia Müller, Ananya Iyer, Chen Wei…), cities (Pune, Nashik, Manchester, Dubai, Kolhapur, Berlin, Hyderabad…) and amounts in ₹ $ £ € are fictional. Safe for class. Downloadable workbooks include an About this data sheet explaining each column.
Download dirty practice .xlsx Download solution .xlsx

At deploy, files go to s3://ravindrabagale-cards/excel/dirty-data/ (public S3 downloads).

What you will learn

  • Spot every common dirty-data pattern in real exports.
  • Fix them with formulas (LibreOffice-safe fallbacks) and with menus.
  • Use Power Query so Monday’s cleanup refreshes itself.
  • Document rules on an About sheet so juniors do not guess.

A Monday morning story

Priya’s Pune kirana CSV, Omar’s Dubai bank desk sheet, Ananya’s Hyderabad IT tickets and Sofia’s Berlin lane weights all land in one inbox. Your manager asks for one Pivot by city before lunch. This chapter is the cleaning path that makes that Pivot honest.

Ravindra Bagale’s Tip

Clean in this order: (1) spaces/hidden chars (2) types and dates (3) split units/currency (4) categories and codes (5) duplicates (6) outliers (7) shape wide→tall. Skipping order creates new bugs.

1. Extra spaces and non-breaking spaces (TRIM, CLEAN, SUBSTITUTE CHAR(160))

Friends, spaces are the number-one silent killer of VLOOKUP. Always TRIM, and remember CHAR(160) from web pages.

Why: Lookups and pivots fail when Priya Sharma and  Priya Sharma  look the same but are not.

What: Ordinary spaces, double spaces, and the invisible web space CHAR(160) that TRIM alone does not remove.

How:

  1. Select a helper column next to the dirty names.
  2. Type =TRIM(A2) for normal spaces.
  3. If VLOOKUP still fails, use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
  4. Add CLEAN when imports from old systems bring control characters: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))).
  5. Copy the helper column → Paste Special → Values over the source when you trust it.
BeforeFormula / actionResultMeaning
Priya[NBSP]Sharma =TRIM(SUBSTITUTE(A2,CHAR(160)," "))Priya SharmaRetail order name from a Pune kirana CSV pasted from WhatsApp Web.

2. Numbers stored as text

If SUM looks too small, suspect text-numbers before you blame the formula.

Why: SUM ignores some text-numbers, and charts look empty even when you “see” digits.

What: Digits that Excel stores as text (green mark, left-aligned, or leading apostrophe).

How:

  1. Check with =ISTEXT(B2) or look for the green corner.
  2. Use =VALUE(B2) or multiply by 1: =B2*1.
  3. Or select the column → Data → Text to Columns → Finish (quick coerce).
  4. For many rows, add a helper, paste values, then delete the text column.
BeforeFormula / actionResultMeaning
'1250=VALUE(B2)1250Bank fee line from Nashik branch export saved as text.

3. Text dates and mixed date formats

Never trust a date that still looks like text. Confirm with ISNUMBER, then lock the format.

Why: India often sends DD/MM/YYYY while a US file sends MM/DD/YYYY. Excel may swap day and month quietly.

What: Dates stored as text, or real dates typed under the wrong regional format.

How:

  1. Ask: is this cell a real date or text? Try =ISNUMBER(C2).
  2. If text and your PC is DMY: =DATEVALUE(C2) can work for 15/03/2026.
  3. Safer for mixed files: Data → Text to Columns → Date → pick DMY or MDY.
  4. Or split with LEFT/MID/RIGHT and rebuild with =DATE(year,month,day).
  5. Format the result as DD-MMM-YYYY so everyone reads the same day.
BeforeFormula / actionResultMeaning
3/15/2026 (US text)Text to Columns → Date → MDY15-Mar-2026Manchester shop export opened on an Indian Windows locale.

4. Inconsistent case and spelling

Fix categories before any Pivot. Clean words first, then count.

Why: PivotTables treat delivered, Delivered and DELIVERED as three statuses.

What: Mixed UPPER/lower and small spelling drift in categories.

How:

  1. Pick one style for categories (Title Case is fine for status).
  2. Use =PROPER(TRIM(D2)) or =UPPER(TRIM(D2)).
  3. Build a small mapping table for spellings (e.g. delivrd → Delivered) and look it up.
  4. Replace values in Power Query when the list is long.
BeforeFormula / actionResultMeaning
delivered =PROPER(TRIM(D2))DeliveredLogistics status from a Berlin lane CSV.

5. Duplicate rows (Remove Duplicates, UNIQUE, COUNTIF flags)

Never delete duplicates blind. Flag, read two sample rows, then remove.

Why: Double invoices inflate sales. Healthcare and banking files often paste the same line twice.

What: Exact duplicate rows, or the same key repeated with tiny differences.

How:

  1. First create a flag: =IF(COUNTIF($A$2:A2,A2)>1,"DUP","") on a key column.
  2. Filter the flag and review before deleting.
  3. Data → Remove Duplicates on the key columns you choose.
  4. On Microsoft 365, =UNIQUE(range) builds a clean spill list (LibreOffice: use Remove Duplicates or Advanced Filter).
  5. Keep a backup sheet named Before_Dedup.
BeforeFormula / actionResultMeaning
Priya Sharma row twiceCOUNTIF flag then Remove DuplicatesOne Priya row keptPune retail orders pasted twice from email.

6. Blanks and missing values

A blank is data too. Write the rule in the About sheet.

Why: Blank city or blank status breaks grouping and confuses averages.

What: Empty cells that mean “unknown”, “zero”, or “not applicable” — you must decide.

How:

  1. Count blanks: =COUNTBLANK(E2:E100).
  2. Decide a rule with the business: fill city from another column, type Unknown, or leave blank on purpose.
  3. For numeric blanks that should be zero in totals, use =IF(F2="",0,F2) in a helper.
  4. Do not fill medical or bank blanks with guesses without a written rule.
BeforeFormula / actionResultMeaning
City blank for RaviFlag or fill from branch masterNashik or UnknownBanking desk row missing city.

7. Merged cells

Merged cells are for title banners, not for data tables.

Why: Merged region labels look pretty but filters, sorts and pivots break.

What: Two or more cells joined into one visual block.

How:

  1. Select the merged block → Home → Merge & Center (click to Unmerge).
  2. Fill the empty cells downward (Home → Fill → Down) so every row has its Region.
  3. Never merge inside a data table you will Pivot.
  4. Use Center Across Selection for titles if you only need the look.
BeforeFormula / actionResultMeaning
West MH merged over two citiesUnmerge + Fill DownWest MH on each rowKolhapur clinic patient summary.

8. Combined columns to split (Text to Columns, Flash Fill, TEXTSPLIT)

Split before you SUM. Units never belong inside a number column.

Why: Quantity and unit stuck together (5 kg) stop numeric totals.

What: One cell holding two facts that belong in two columns.

How:

  1. Easy menu: Data → Text to Columns → Delimited → Space.
  2. Flash Fill (Ctrl+E): type 5 beside 5 kg, fill down.
  3. Formula fallback (works in LibreOffice too): =VALUE(LEFT(A2,FIND(" ",A2)-1)) and =TRIM(RIGHT(A2,LEN(A2)-FIND(" ",A2))).
  4. Microsoft 365: =TEXTSPLIT(A2," ") — keep the LEFT/FIND fallback for class PCs without it.
BeforeFormula / actionResultMeaning
5 kg=VALUE(LEFT(A2,FIND(" ",A2)-1))5Healthcare supply weight from Kolhapur clinic.

9. Split columns to join

Prefer & when students have mixed Excel versions.

Why: First name and last name in two columns need one email label or one print line.

What: Joining text with &, CONCAT, or TEXTJOIN.

How:

  1. Simple and universal: =A2&" "&B2.
  2. Modern Excel: =CONCAT(A2," ",B2).
  3. Lists with commas: TEXTJOIN on Microsoft 365; on older Excel join with & or use a helper.
  4. TRIM the parts first so you do not create double spaces.
BeforeFormula / actionResultMeaning
Priya | Sharma=A2&" "&B2Priya SharmaIT ticket owner full name in Hyderabad desk.

10. Currency symbols and thousand separators in text (₹ $ £ €)

Never SUM a column that still shows currency characters. Clean, then format as currency.

Why: Text like ₹1,250.00 will not SUM until symbols and commas are removed.

What: Money pasted as text with symbols, commas, or European decimal commas.

How:

  1. Strip symbols with SUBSTITUTE (nest for many): remove ₹ $ £ € and spaces.
  2. Remove thousands commas: =SUBSTITUTE(x,",","") for US/India style.
  3. For 1.250,50 € (EU): remove €, replace . (thousands) with empty, replace , with ., then VALUE.
  4. Keep a separate Currency column (INR/USD/GBP/EUR) so you do not mix ₹ and $ in one sum.
  5. Example: =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"₹",""),",","")).
BeforeFormula / actionResultMeaning
₹1,250.00=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"₹",""),",",""))1250Pune kirana invoice amount pasted from PDF.

11. Units inside cells (“5 kg”)

Write the unit in the header, not inside every cell, once the column is clean.

Why: Same as split — but people forget and multiply wrong (kg vs g).

What: Numbers glued to units.

How:

  1. Split qty and unit into two columns (see section 8).
  2. Convert units in a third column if needed: grams to kg = qty/1000.
  3. Validate with MAX/MIN — a “5000 kg” rice bag is probably grams mislabeled.
BeforeFormula / actionResultMeaning
5000 gqty/1000 → kg5 kgLogistics weight from Nagpur hub.

12. Wrong data types

IDs and phone numbers are text, even when they look like digits.

Why: A phone number stored as a real number drops the leading zero. An ID becomes 4.5E+11.

What: Excel guessing Number vs Text vs Date incorrectly on import.

How:

  1. On import, set the column type in Text to Columns or Power Query to Text for IDs and phones.
  2. Prefix with apostrophe only as a quick fix: '012345.
  3. Use custom format if you must keep numeric storage but show leading zeros.
  4. Check TYPE/ISTEXT/ISNUMBER on a sample of 10 rows.
BeforeFormula / actionResultMeaning
StaffCode S01 vs 1Force Text on importS01 keptBanking staff codes from Dubai desk.

13. Error values (IFERROR / IFNA)

IFERROR is a seatbelt, not a steering wheel. Fix the data too.

Why: #DIV/0! and #N/A scare managers and break dashboards.

What: Formula errors from zero hours, missing lookups, or bad VALUE() parses.

How:

  1. Fix the cause first (do not hide every error blindly).
  2. Wrap display formulas: =IFERROR(D2/C2,"check hours").
  3. For lookups, prefer IFNA when you only want to catch #N/A.
  4. Keep a raw calc column and a clean display column if auditors need both.
BeforeFormula / actionResultMeaning
Cost/0 hours=IFERROR(D2/C2,"check hours")check hoursIT ticket cost rate in Hyderabad.

14. Hidden characters

If LEN looks too big, something invisible is hiding.

Why: Same as section 1 — tabs, line breaks and CHAR(160) survive a quick glance.

What: Invisible characters from PDF, HTML and old mainframe exports.

How:

  1. Measure length: =LEN(A2) vs =LEN(TRIM(A2)).
  2. Use CLEAN for codes 0–31.
  3. SUBSTITUTE CHAR(160), CHAR(9) tabs, and CHAR(10) line breaks as needed.
  4. In Power Query: Transform → Format → Clean / Trim.
BeforeFormula / actionResultMeaning
Fatima + NBSP + KhanTRIM+SUBSTITUTE CHAR(160)Fatima KhanMumbai retail loyalty paste.

15. Inconsistent codes

One master list for codes. Everything else is a defect.

Why: S01, s01 and S1 look like three staff members.

What: Codes with different case, padding, or obsolete values (S99).

How:

  1. UPPER or PROPER the code column.
  2. Pad with TEXT or RIGHT: ="S"&RIGHT("00"&number,2) when needed.
  3. Match against a staff master with exact match only.
  4. Flag unknowns: =IF(COUNTIF(Master[Code],A2)=0,"bad code","").
BeforeFormula / actionResultMeaning
s01=UPPER(A2)S01Nashik banking timesheet codes.

16. Outliers

Outliers are questions, not automatic deletes.

Why: One row with 1,000,000 ₹ or 40 hours can break averages and charts.

What: Values far from the normal range — sometimes real, sometimes typos.

How:

  1. Sort the column or use MIN/MAX.
  2. Compute a simple fence: flag if value > 3× median (classroom rule of thumb).
  3. Talk to the owner before deleting — Priya’s 1,000,000 may be a real annual invoice.
  4. Keep an OutlierFlag column instead of silent deletes.
BeforeFormula / actionResultMeaning
Cost_INR 1000000OutlierFlag = Cost highReview with managerIT ticket cost typo vs real project fee.

17. Header rows in the middle

One header at the top. Everything else is a data row — or junk.

Why: A repeated header or a “Total” row in the middle breaks sorts and pivots.

What: Batch titles pasted again inside the data.

How:

  1. Filter the first column for the word Batch or Total and delete those rows.
  2. In Power Query: filter rows where SKU column does not equal SKU.
  3. Reload a clean extract from the source system when possible.
BeforeFormula / actionResultMeaning
Row repeating Batch/SKU/City…Filter out header-like rowsOnly real SKUs leftRetail stock paste from Pune + Manchester.

18. Wide vs tall data (unpivot)

Wide is for reading by humans; tall is for PivotTables.

Why: Months across columns (Jan–Apr) make PivotCharts painful.

What: Wide layout vs tall layout (Store, Month, Sales).

How:

  1. Prefer tall data for analysis: one row per store-month.
  2. In Power Query: select month columns → Transform → Unpivot Columns.
  3. Rename Attribute → Month, Value → Sales.
  4. Close & Load to a new sheet.
BeforeFormula / actionResultMeaning
Store | Jan | Feb | MarUnpivot month columnsStore | Month | SalesPune kirana + Manchester shop monthly sales.

19. Power Query cleaning checklist

If you clean the same file every Monday, stop pasting formulas — build a query.

Why: Repeating the same TRIM/VALUE fixes every week wastes time. Power Query remembers the steps.

What: A refreshable cleaning pipeline: Get Data → transform → Close & Load.

How:

  1. Data → Get Data → From File / From Folder (or From Clipboard for class demos).
  2. Remove top junk rows; promote headers.
  3. Transform → Format → Trim / Clean on text columns.
  4. Replace values for status spellings; set data types (Date, Decimal, Text).
  5. Remove duplicates on business keys; unpivot wide months if needed.
  6. Home → Close & Load To… Table on a new sheet.
  7. Next week: Data → Refresh All — steps replay automatically.
BeforeFormula / actionResultMeaning
Weekly dirty CSVPower Query steps savedClean table on RefreshBanking + retail weekly ops pack.

Practice tasks (12)

Use dirty-data-practice.xlsx. Check ideas against dirty-data-solution.xlsx. Avoid XLOOKUP / TEXTJOIN / TEXTSPLIT in checks so LibreOffice class PCs keep up — fallbacks are in the lesson.

  1. On Raw_Import, create Name_Clean with TRIM + SUBSTITUTE CHAR(160) for customer names.
    Hint

    Start with Priya in row 2 — LEN should drop.

  2. Convert Amount_Text for Priya into a real number 1250.
    Hint

    Strip ₹ and commas, then VALUE.

  3. Parse Qty_Unit “5 kg” into Qty=5 and Unit=kg with LEFT/FIND (no TEXTSPLIT).
    Hint

    FIND the space.

  4. Normalise Status to Title Case with TRIM.
    Hint

    PROPER(TRIM(...)).

  5. Flag duplicate Priya rows with COUNTIF.
    Hint

    Same name + date + amount is a clue.

  6. Fix the US date 3/15/2026 so it becomes 15-Mar-2026 on an Indian PC.
    Hint

    Text to Columns → MDY.

  7. Unmerge Region on Merged_Trap and fill West MH down.
    Hint

    Unmerge, then Fill Down.

  8. Remove the middle header row and the Total row on Mid_Headers.
    Hint

    Filter text “Batch” / “Total” in column A carefully.

  9. Unpivot Wide_Months into Store, Month, Sales (Power Query or manual copy).
    Hint

    Four month columns become rows.

  10. Wrap the #DIV/0! on Errors_Outliers with IFERROR.
    Hint

    Show “check hours” when hours are 0.

  11. Flag StaffCode S99 as invalid using a small master list S01–S05.
    Hint

    COUNTIF against the master.

  12. Write one business rule on About this data for blank cities.
    Hint

    Unknown vs fill-from-branch — pick one and document it.

Quick quiz

Q1. TRIM alone always removes the web non-breaking space CHAR(160).

Q2. Best first check that a date cell is a real date?

Q3. ₹1,250.00 as text — what is a LibreOffice-safe cleanup path?

Q4. Why are merged cells bad inside a data table?

Q5. Wide month columns should usually become…

Q6. IFERROR should be used to…

Q7. Duplicate cleanup — safest classroom habit?

Q8. “5 kg” in one cell — good analysis shape is…

Loading practice tasks...

  1. Total amount (Easy): Add up Amount in D2:D11.
  2. Average qty (Easy): Average of the Qty column, rounded to 2 decimals.
  3. Delivered rows (Easy): How many rows have Status "Delivered"?
  4. Blank check (Easy): Count blank cells in C2:C11.
  5. Delivered amount (Medium): Total of D where C is "Delivered".
  6. Pune delivered qty (Medium): Total of E where B is "Pune" and C is "Delivered".
  7. Largest pending (Medium): Largest D where C is "Pending".
  8. Row 2 value (Medium): Multiply the first-row Amount (₹) by the first-row Qty.
  9. Look up Ananya Iyer (Medium): The Amount for "Ananya Iyer".
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share delivered (Hard): Share of D that is Delivered, rounded to 3 decimals.
  12. Trim test name (Hard): Trim " Priya Sharma " (spaces outside).

See all practice tasks in the Task Hub →

Quick revision cards (sample of 3)

Approved Excel card style: standalone, no tip/photo section, QR to this lesson. Full card set can expand at deploy.

Revision card: spaces TRIM CHAR(160)
Card 1 — Spaces & CHAR(160)
Revision card: currency text to number
Card 2 — Currency text → number
Revision card: unpivot Power Query
Card 3 — Wide → tall in Power Query

Marathi / Hindi plan

English chapter is live. Marathi twin planned at /mr/excel/ch32-cleaning-dirty-data-in-excel/ with the same structure, absolute /excel/... and /mr/excel/... links, and keep teacher tabs EN/MR/HI on both. Hindi remains available in the teacher tabs on the English URL (site pattern).