Excel for data cleaning: first steps

Look before you clean

Cleaning without looking is how datasets get damaged. Start by copying the file and working on the copy, so the raw data is always recoverable. Then inspect the shape of the data: how many rows and columns, what the headers say, and what one row actually represents. A row might be a person, an order, a visit or a month, and the analysis you can do depends entirely on that.

Check the data types column by column. Numbers stored as text will not sum. Dates typed by hand arrive in several formats. A quick way to find these is to sort each column and read the top and bottom of the list: oddities tend to surface at the extremes.

Standardise the values that should match

The most common cleaning problem is the same value written several ways. Nairobi, nairobi, NAIROBI and Nairobi County are one place to the analysis and four to the computer. Fix these consistently rather than one at a time.

• Trim stray spaces with TRIM, and remove non-printing characters with CLEAN.
• Use SUBSTITUTE to strip units such as kg or KES out of numeric columns.
• Standardise case deliberately: PROPER for names, UPPER for codes.
• Build a small lookup table of accepted values and use it to check the data.

Find and handle duplicates

Remove Duplicates in the Data tab shows how many rows it would delete, but it deletes based on the columns you select. Decide first what makes a row unique: a reference number, or a combination of columns. Inspect duplicates before deleting, because sometimes they are legitimate repeat transactions rather than errors.

Decide what blanks mean

A blank cell can mean the value is zero, unknown, not applicable, or simply not recorded. Treating all four as the same thing silently changes your results. Add a rule to your notes and follow it consistently: for example, leave genuinely unknown values blank, and record not applicable as NA with a separate note.

If you fill blanks, do it visibly. Filling with zero is a modelling decision, not a cleanup step, and it should appear in your write-up.

Work with tables and formulas you can audit

Converting the range into an Excel Table gives you structured references, automatic expansion and readable formulas. Keep transformations in separate columns next to the original rather than overwriting it, so you can trace each change back.

• One transformation per column, named clearly, for example clean_city or amount_kes.
• Avoid hard-coded row numbers in formulas; use table references or INDEX and MATCH.
• Add a check column that flags rows failing your rules, then filter on it.

Document the pass

A short change log makes the rest of the project far easier, and it is essential for any analysis you must defend. Record what you did to each column, what you decided about blanks and duplicates, and anything you left unresolved. If a result later looks strange, the log usually explains why in minutes instead of hours. From here, the natural next steps are pivot tables for description and charts that show the finding rather than decorate it.