How do you clean and prepare messy data in Excel?

The strategic answer first: for anything you will do more than once, use Power Query. Manual cleaning is a one-off; a Power Query transformation is recorded as steps, re-runs on refresh, and documents itself. That distinction is what interviewers want to hear.

Common problems and their fixes:

• Duplicates — Data → Remove Duplicates, or COUNTIF to identify them first if you want to inspect before deleting.
• Whitespace and non-printing characters — TRIM() and CLEAN(). Invisible trailing spaces are the single most common cause of a lookup failing on values that look identical.
• Numbers stored as text — the green triangle, Text to Columns with no delimiter, or VALUE(). These break SUM silently.
• Splitting and combining — Text to Columns, Flash Fill for pattern-based extraction, TEXTSPLIT and TEXTJOIN in newer versions.
• Inconsistent case and spelling — PROPER(), UPPER(), LOWER(), and a lookup table mapping variants to a standard value.
• Blanks and errors — Go To Special to select them in bulk, and IFERROR() to control what displays.
• Inconsistent dates — usually the hardest, because a text date and a real date look alike. DATEVALUE() or Power Query's locale-aware type conversion.

Note: Always keep the raw data untouched on its own sheet and clean into a copy, so you can trace anything back.