17.4 Data Cleaning and Preparation

Q29. How do you remove duplicates?

Data › Data Tools › Remove Duplicates, choosing the key columns; or first identify them with Conditional Formatting › Duplicate Values or =COUNTIF($A$2:A2,A2)>1; or =UNIQUE() in Microsoft 365; or Power Query › Remove Duplicates for repeatable cleaning. I always standardise values (TRIM/UPPER) first and keep the raw data untouched.

Q30. How do you handle missing values?

First find them (Go To Special › Blanks, COUNTBLANK, filters). Then decide by business meaning: leave blank and flag, fill down for repeated labels (Go To Special › Blanks, =A2, Ctrl + Enter), use a default like "Unknown" for text, or impute carefully (median) only when justified. I never replace missing numbers with 0 blindly because it distorts averages.

Q31. What is data cleansing and what steps do you follow?

Making data accurate, consistent and usable. My steps: keep a raw copy → check structure (headers, blank rows, merged cells) → fix types (text numbers, dates) → standardise text (trim, case, mapping) → remove duplicates → handle missing values and outliers → validate with counts and totals → document the steps (or record them in Power Query).

Q32. How do you standardise mixed date formats?

Identify which rows are text vs real dates (ISNUMBER). Use Text to Columns with the correct date order (DMY/MDY), DATE(RIGHT(),MID(),LEFT()) for text patterns, or Power Query › Change Type › Using Locale. Then apply dd-mm-yyyy formatting.

Q33. How do you split "Pune-411038" into city and pincode?

Text to Columns with - as delimiter; Flash Fill (Ctrl + E); =LEFT(A2,FIND("-",A2)-1) and =MID(A2,FIND("-",A2)+1,6); or TEXTBEFORE/TEXTAFTER in Microsoft 365.

Q34. What is Flash Fill and its limitation?

Flash Fill (Data › Data Tools › Flash Fill, Ctrl + E) fills values by recognising a pattern from examples. It is fast but static – it does not update when the source changes – and it can guess wrong on irregular data, so I verify results.

Q35. How do you find outliers in Excel?

Sort and inspect, use conditional formatting (top/bottom or above average), compute z-scores =(x-AVERAGE)/STDEV.S, or the IQR rule with QUARTILE.INC: outlier if below Q1 − 1.5×IQR or above Q3 + 1.5×IQR. Then verify with the business before removing anything.

Q36. How do you compare two lists or columns to find mismatches?

=COUNTIF(ListB,A2)=0 or =ISNA(MATCH(A2,ListB,0)) to find items missing from list B; =EXACT(A2,B2) for case-sensitive row-wise comparison; Conditional Formatting › Duplicate/Unique values; or Power Query anti-joins.