Theory
One column, two facts crammed in
Meera imports a customer list a supplier emailed her. Every cell in column A reads like Riya Shah, 9876543210, the name and phone stuck together in one box. And several customers appear two or three times.
She cannot sort by name or dial a number while they are glued together and duplicated. Real-world data is almost always messy like this.
Two cleanup tools fix it: one splits the crammed column, the other removes the repeats. This is the unglamorous but essential craft of cleaning data.
Theory
Sorting a mixed drawer
Imagine a drawer where every item is a rubber-banded bundle of a pen and a note stuck together, and some bundles are exact duplicates. To use anything, you first snap the bands to separate pens from notes (Text to Columns), then throw out the identical repeat bundles (Remove Duplicates). Only then is the drawer usable. Messy data always needs this snap-and-dedupe pass before real work begins.
Follow along
Split 'name, phone' into two columns
- Make sure the columns to the RIGHT are empty The split spills into them; if they hold data it gets overwritten.
- Select the crammed column, Data > Text to Columns The wizard opens.
- Choose Delimited, then pick the delimiter (comma) The preview shows the split at each comma; space and tab are other options.
- Finish Name lands in one column, phone in the next. Now sortable and usable.
Theory
Remove Duplicates
Data > Remove Duplicates deletes rows that are exact repeats. You choose which columns define a duplicate:
- Tick only 'Phone' and two rows with the same number are treated as the same customer, even if the name is spelt differently.
- Tick 'Name' and 'Phone' and a row must match on both to count as a duplicate.
Excel keeps the first occurrence and removes the rest. Choosing the right defining columns is the whole skill, pick too few and you delete real distinct rows.
Quiz
Meera runs Text to Columns on column A, but column B already had her stock notes. What happens to those notes?
- They get overwritten by the split data spilling from column A
- Excel automatically shifts them right to protect them
- Nothing, the notes stay safe
- The split fails with an error
Show the answer
They get overwritten by the split data spilling from column A
Text to Columns spills the split pieces into the columns to the right, overwriting whatever is there. Meera should insert blank columns first, or move column B's notes away. This 'clear space to the right' requirement is the classic Text to Columns gotcha, and a real cause of accidental data loss.
Think first
Which columns define a duplicate?
Meera has customers where the SAME person appears with two spellings of their name but the SAME phone number. If she wants to treat them as one customer, which column(s) should she tick in Remove Duplicates, and why not tick both name and phone?
Show the answer
Tick Phone only. If she ticks both Name and Phone, the two differently-spelt rows would NOT match (names differ), so both survive, the opposite of what she wants. Ticking Phone alone treats any repeated number as the same customer, catching the misspelt duplicates. The lesson: the columns you tick DEFINE what 'duplicate' means, choose them for the real identity.
Watch out
Where marks leak
Forgetting Text to Columns needs empty space to the right (it overwrites). Not realising Remove Duplicates permanently deletes (keeps only the first), do it on a copy if unsure. And misunderstanding that the chosen columns define a duplicate, tick too many and real duplicates survive, too few and distinct rows vanish. Exams frame this as 'what determines whether two rows are duplicates?' (answer: the selected columns).
Theory
This is why databases exist
Notice WHY this mess happened: one column held two facts (name AND phone), and the same customer was stored many times. Unit 3's normalization is precisely the science of designing data so this never occurs, one fact per column, each customer stored once. Cleaning messy spreadsheets is the pain that databases were invented to prevent. Next lesson closes Unit 2: consolidating several sheets into one summary.
Summary
Key takeaways
- Text to Columns splits one column into several using a delimiter (comma, space, tab) or fixed width.
- It overwrites columns to the RIGHT, so clear space first.
- Remove Duplicates deletes repeated rows, keeping the first occurrence.
- The columns you select DEFINE what counts as a duplicate, choose them for real identity.
- Both tools clean messy imported data before real analysis.
- Memory hook: snap the bundles apart (split), throw out the repeats (dedupe).