Data Cleaning

Imagine you are about to cook a lovely meal. Before the fancy recipe begins you wash and chop the vegetables — you rinse off the grit, cut away the bad bits, and throw out anything rotten. Skip that step and the finest recipe in the world still tastes of mud. Data cleaning is exactly that washing-and-chopping stage, done to your spreadsheet before any analysis begins.

Why does it matter so much? Because Excel is fussy and literal. If one cell says "Mumbai " with a sneaky trailing space and the next says "Mumbai", Excel treats them as two different cities. A SUM quietly skips numbers that are secretly text. A VLOOKUP returns #N/A over an invisible space you cannot even see. Sorting jumbles up and totals come out wrong — the analysis is not broken, the ingredients were gritty.

Real Indian data arrives messy in very predictable ways:

• Names typed with double  spaces or wandering CAPITALS, such as ravi  KUMAR.
• Amounts pasted as text with rupee signs and commas, such as ₹1,20,000 — which simply will not add up.
• Phone numbers and pincodes glued together with dashes, such as 98765-43210 and 110-001.
• The same customer entered five times — rotten repeats that inflate every total.

The good news: the same handful of tools fix ninety per cent of it, and they fix a whole column in one go instead of retyping a thousand rows by hand. TRIM is your rinse under the tap — it washes off extra spaces. PROPER tidies the CAPITALS. SUBSTITUTE scrubs out unwanted characters. Remove Duplicates bins the rotten repeats. Build the habit of keeping your raw column untouched and growing the clean version in the next column, so you can always taste-test the before and after.

Two pictures of the same job: first the whole cleaning kit at a glance, then a decision path for picking the right fix for a messy column.

Good cleaning follows an order, like prepping vegetables in sequence: rinse, then chop, then bin the bad ones. Do it in the same order every time and messy data quietly becomes tidy data you can trust. Here is the same name before and after one quick pass.

Now take the toolkit one drawer at a time, starting with the two invisible troublemakers: spaces and case.

Rinse the spaces — TRIM and CLEAN. TRIM(text) removes spaces from the start and end and squeezes any run of spaces inside down to a single one, so "  Ravi   Kumar  " becomes Ravi Kumar. It is the tool you will reach for most, because copied data is riddled with spaces you cannot see. Its partner CLEAN(text) strips non-printing characters — the invisible line breaks and control codes that ride in from websites and PDFs. Chain them for a deep rinse: =TRIM(CLEAN(A2)).

Fix the case — UPPER, LOWER, PROPER. UPPER shouts everything into CAPITALS (perfect for a PAN, or a vehicle number like MH12AB1234), LOWER drops it all to small letters (perfect for email addresses), and PROPER Capitalises The First Letter Of Each Word — the label-maker for names and cities. =PROPER("ravi KUMAR") gives Ravi Kumar.

Split one column into many — Text to Columns and Flash Fill. When a single cell holds two facts — a full name, or Delhi-110001 — you must split it apart. The classic tool is the Text to Columns wizard.

For quick, by-example splitting there is an even friendlier tool: Flash Fill. Type the answer you want in the next column — the first name only, or the pincode only — then press Ctrl+E. Excel studies your example, spots the pattern and fills the entire column in a blink. It is brilliant for reformatting phone numbers, pulling out initials, or reshaping dates by example, with no formula to write.

Bin the rotten repeats — Remove Duplicates and COUNTIF. Duplicate rows inflate every total and average. To delete them outright, select the range and use Data, then Remove Duplicates, ticking the columns that define a match. To find them first — far safer — add a helper column with =COUNTIF(A:A,A2)>1; any row marked TRUE is a repeat you can inspect before deleting. Seeing beats deleting blind.

Rescue numbers trapped as text — SUBSTITUTE and VALUE. Amounts like ₹1,20,000 often arrive as text and refuse to add up. Peel off the offending characters with SUBSTITUTE(text, old, new) — =SUBSTITUTE("1,20,000",",","") deletes every comma — then wrap the result in VALUE to turn the digit-string into a real number: =VALUE(SUBSTITUTE(A2,",","")). The telltale sign a cell is text is the little green triangle in its corner and the number hugging the left edge.

Mind the blanks. A truly empty cell and a cell holding an invisible space are not the same thing — the second quietly breaks counts and filters. TRIM clears accidental spaces; for genuinely missing values decide on one rule (leave blank, or fill with 0 or NA) and apply it consistently, so COUNTA and your charts behave.

Neutralise errors — IFERROR. One bad cell can spray #VALUE! or #N/A across a whole column. Wrap a risky step so it fails softly: =IFERROR(VALUE(A2),0) hands back 0 (or any message you choose) instead of an ugly error, keeping the rest of your sheet calm.

The professional's tool — Power Query. For big files, or a clean-up you repeat every month, Excel hides a proper cleaning studio called Power Query (Get and Transform, on the Data ribbon). You perform each step once — trim, split, remove duplicates, change type — and Excel records them; next month you drop in the new file and re-run the whole recipe with a single click. Everything in this module is the hand tool; Power Query is the machine that does it for you.

Cleaning looks trivial, which is exactly why it bites. Here are the traps that quietly wreck real analysis, and the pro moves that dodge them.

Seven messy cells from real Indian data. Type a formula, press Run, and the cell turns green the moment your text comes out clean.