Ravindra BagaleCourses & study guides मराठी Track your progress

16. Practice Exercises with Answer Hints

16.4 Module 5: Data Cleaning

By Last updated:

  1. Remove extra spaces and non-printable characters from customer names. Hint: =TRIM(CLEAN(A2)); for non-breaking spaces add SUBSTITUTE(A2,CHAR(160)," ").
  2. Standardise "pune", "PUNE ", " Pune" to "Pune". Hint: =PROPER(TRIM(A2)).
  3. Replace "Aurangabad" with "Sambhaji Nagar" in 400 rows safely. Hint: mapping table + XLOOKUP, or Home › Find & Select › Replace with Match entire cell contents.
  4. Convert "Rs. 1,299" to the number 1299. Hint: =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"Rs.",""),",","")).
  5. Convert text dates like 03/14/2026 (US style) to real dates. Hint: =DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2)) or Text to Columns › Date: MDY.
  6. Keep only the last 10 digits of phone numbers like "+91 90000 00021". Hint: =RIGHT(SUBSTITUTE(SUBSTITUTE(A2," ",""),"-",""),10).
  7. Split "Pune-411038" into City and Pincode. Hint: Text to Columns (delimiter -) or =TEXTBEFORE(A2,"-") / =TEXTAFTER(A2,"-") (Microsoft 365 / Excel 2024).
  8. Flag outliers in Amount using the IQR rule. Hint: Q1/Q3 with QUARTILE.INC; outlier if < Q1-1.5*IQR or > Q3+1.5*IQR.

Ravindra Bagale's Tip

Many students run Find & Replace directly on the original data, and if something goes wrong they can't go back. Always keep the Raw sheet untouched, work on a copy, and after cleaning reconcile the row count and the total amount. This habit will save you at work.

Loading practice tasks...

  1. Total amount (Easy): Add up Amount (₹) in E2:E11 on sheet Data. (sheet "Data")
  2. Average qty (Easy): Average of Qty in D2:D11, rounded to 1 decimal. (sheet "Data")
  3. Active count (Easy): How many rows have Status "Active"? (sheet "Data")
  4. Max amount (Easy): Largest Amount (₹) in E2:E11. (sheet "Data")
  5. Pune count (Easy): How many rows have City "Pune"? (exact match — cleaning matters). (sheet "Data")
  6. Active amount (Medium): Total of Amount (₹) where Status is "Active". (sheet "Data")
  7. Mumbai qty (Medium): Total of Qty where City is "Mumbai". (sheet "Data")
  8. Active Pune amount (Medium): Amount where City is "Pune" and Status is "Active". (sheet "Data")
  9. Code of Priya (Medium): Code for Name "Priya Shah" on sheet Text. (sheet "Data")
  10. Share active amount (Hard): Active amount / all amount, 3 decimals. (sheet "Data")
  11. Hold count (Hard): How many rows have Status "Hold"? (sheet "Data")
  12. Above avg amount? (Hard): "High" if E2 > average amount else "Low". (sheet "Data")

See all practice tasks in the Task Hub →

Worked example — module 5 data cleaning

Why

Rohan Patil in Pune and Emma Clarke abroad both need a clear, repeatable way to use module 5 data cleaning on a Summit Schools sheet without breaking totals.

What

Module 5 Data Cleaning is the Excel idea this page teaches. We will walk one tiny example end to end.

How

  1. Open a blank sheet. In A1 type a short label for module 5 data cleaning.
  2. Enter a small made-up value for Rohan Patil using ₹ where money appears.
  3. Add a second value for Emma Clarke using $ so you see mixed currencies stay numeric.
  4. Apply the method from this lesson step by step (menu or formula as shown above).
  5. Check the result still works inside SUM or a simple check cell.

Formula / action

Follow the formula or ribbon steps shown in this lesson (keep values numeric).
Result: The demo cell shows the expected result for Summit Schools's sample row.
Meaning: Once module 5 data cleaning is correct on one row, copy the same pattern down the column.

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Module 5 Data Cleaning?
Card 1: What is Module 5 Data Cleaning? · Download PNG
Revision card 2: Tiny example — Module 5 Data Cleaning
Card 2: Tiny example — Module 5 Data Cleaning · Download PNG
Revision card 3: Checklist — Module 5 Data Cleaning
Card 3: Checklist — Module 5 Data Cleaning · Download PNG