ArumSoft Learning
한국어

Excel Basics · Interactive lesson 11 of 12

Cleaning data

Real data is rarely tidy — stray spaces, one name crammed into a single cell, exact duplicates, and blank gaps are the norm, not the exception. Excel has a built-in tool for each of these problems.

BeginnerInteractive

Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.

Written tutorialHow to Clean Up Data in Excel

Skill check

Try these first:

  • Use Find and Replace to swap one value for another across a sheet.
  • Remove exact duplicate rows.
  • Split "Smith, John" into separate last-name and first-name columns.
  • Trim stray spaces from a cell, and spot a blank one.

All familiar? Skip ahead to M12 — Navigation & Shortcuts.

Teach

What you're learning

Find and Replace
Ctrl+H — swaps every occurrence of one value for another across a whole sheet in one pass.
Remove Duplicates
Deletes rows that repeat exactly, keeping the first occurrence.
Text to Columns
Splits one cell's content into several columns using a delimiter — a comma, space, or other character.
TRIM()
Strips leading, trailing, and doubled-up spaces — the classic cause of two "identical-looking" values that Excel treats as different.
Identify blanks
Select the range, then Home → Find & Select → Go To Special → Blanks highlights every empty cell at once.

Where to find it in Excel

Data›Data Tools

Swipe sideways to see the whole window.

  1. 1Data tab.
  2. 2Text to Columns splits one column at a comma or space.
  3. 3Remove Duplicates deletes repeated rows (row 4 repeats row 3).
  4. 4TRIM removes extra spaces. Find & Replace (Ctrl+H) and Go To Special › Blanks are on the Home tab.

Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.

Guided practice

A messy contact list — extra spaces, one combined name, a duplicate row, and a blank. Run each tool below on it.

🧹 Contact List — unedited
Start with "TRIM spaces" — look closely at the Name column first, the stray spaces are hard to see.

Real-world exercise

Clean a messy export

Take a genuinely messy dataset — a CSV export, a copy-pasted list — and run it through all four tools in order: trim stray spaces, split any combined columns, remove exact duplicates, then find and fix the blanks. A worksheet is only as trustworthy as the data underneath it.

Spot a mistake or have an idea for this lesson? Send feedback