Clean data habits (why your sheet breaks)

A spreadsheet fails for one of two reasons: the formulas are wrong, or the data is messy. Beginners obsess over formulas. Professionals obsess over data.

Here are the five habits.

Habit 1 — One thing per cell

Never write Rahul Sharma - Mumbai - 9876543210 in one cell. Use three columns: Name, City, Phone.

Why: the moment you want to sort by city, or count people in Mumbai, or send emails, you have to pull that cell apart. Splitting later is painful. Splitting up front is free.

Habit 2 — Never merge cells

Merged cells look tidy and break everything. You can't sort a table with merged cells. You can't filter it. Pivot tables refuse. Formulas behave oddly.

If you want a heading centred over several columns, use Center Across Selection instead (Format Cells → Alignment → Horizontal). Same look, none of the damage.

Habit 3 — Watch out for numbers stored as text

You have a column of numbers. =SUM() returns 0. Everything looks fine. What's happening?

Numbers pasted from a website or exported from an old system often arrive as text that looks like a number.

How to spot it:

• Numbers sit on the left of the cell instead of the right. (Excel right-aligns real numbers automatically.)
• A small green triangle appears in the corner.
• =ISNUMBER(A2) returns FALSE.

How to fix it: select the column → Data → Text to Columns → Finish. That one click converts the lot.

Habit 4 — Kill invisible spaces

"Mumbai " with a trailing space is not equal to "Mumbai". Your VLOOKUP will fail and you'll never see why, because a space is invisible.

=TRIM(A2)

TRIM removes leading and trailing spaces. Get in the habit of trimming any data that came from outside your own keyboard.

Habit 5 — Keep raw data raw

Structure every workbook in three layers:

Why it matters: when someone asks "where did this number come from?" — and someone always does — you can trace it back. If you typed over the original data, you can't, and now nobody trusts the file.

The 30-second sanity check

Before sending any spreadsheet, do these three:

1. 1Press Ctrl+End. Does it jump somewhere unexpected, miles below your data? You have stray content down there — delete those rows.
2. 2Check every total against a rough mental estimate. If revenue looks like $4 million and you expected $40,000, something is wrong.
3. 3Click one random formula and press F2 to see whether its coloured ranges cover what you think they do.

Thirty seconds. Saves you from the kind of email you remember for years.