| Concept | Description |
|---|---|
| Approach and Diagnosis | |
| Preserve the Raw Sheet | Copy the sheet before editing, since manual edits leave no record of the original values |
| Diagnose Before Cleaning | COUNT against COUNTA, UNIQUE on categories, LEN checks, and a COUNTIF duplicate test |
| Fixing the Nine Defects | |
| Trailing Spaces and Capitalisation | TRIM, CLEAN, PROPER and UPPER, then Paste Special as values before deleting the source |
| Inconsistent Spellings | A two-column correction table mapped through VLOOKUP documents every substitution made |
| Numbers Stored as Text | Paste Special multiply by one, or Text to Columns, or VALUE; the symptom is left alignment |
| Duplicate Rows | Identify with COUNTIF first, then Remove Duplicates with the defining columns chosen deliberately |
| Missing Values | Go To Special finds them; leaving blank, deleting the row, and imputing each carry different costs |
| Never Fill Blanks with Zero | A zero is a real measurement and will bias every mean and coefficient computed from the column |
| Splitting Combined Columns | Text to Columns, Flash Fill with Ctrl+E, or TEXTBEFORE and TEXTAFTER for a live split |
| Dates Stored as Text | DATEVALUE, or Text to Columns with the date order set explicitly when parsing fails |
| Text Inside Numeric Columns | The literal string N/A turns a numeric column to text; replace with empty, not with zero |
| Preventing Recurrence | |
| Data Validation | List and range rules constrain future entry; Circle Invalid Data finds existing violations |
| The Cleaning Checklist | Twelve checks from preserving the raw sheet through to setting validation for the next round |
14 Data Cleaning and Preparation
Ask any working analyst where the time goes and the answer is this stage. The modelling is usually an afternoon. Getting 60 rows of field data into a state where the modelling can be trusted takes longer, and skipping it does not remove the work, it just moves the errors downstream where they are harder to find.
The RawData_Messy sheet in the sample workbook holds the same 60 farms as FarmData, before cleaning. It carries nine defects, all of them taken from the kinds of faults that actually appear in field records rather than invented for the exercise.
| # | Defect | Where |
|---|---|---|
| 1 | Leading and trailing spaces | District |
| 2 | Inconsistent capitalisation |
District, Irrigation
|
| 3 | Two spellings of the same district | District |
| 4 | Numbers stored as text | YieldQtPerHa |
| 5 | Exact duplicate rows | Two FarmID values repeat |
| 6 | Empty cells | SoilMoisture |
| 7 | Two values combined in one column |
CropSeason, as PDY-Kharif
|
| 8 | Dates stored as text | SowingDate |
| 9 | The text N/A inside a numeric column |
Rainfall |
Two extra columns appear on this sheet that FarmData does not carry: CropSeason in column L and SowingDate in column M. And because two rows are duplicated, the sheet runs to row 63 rather than row 61, so every formula below uses 2:63 where the equivalent on FarmData would use 2:61. Noticing that difference is itself the first cleaning task.
Work through them in order. Several fixes depend on earlier ones, and running Remove Duplicates before trimming spaces will miss duplicates that differ only by a trailing space.
14.1 Before Touching Anything
Copy the sheet first. Right-click the tab, choose Move or Copy, tick Create a copy, and work on the copy. The raw sheet is the only surviving record of what the field actually submitted, and once a value is overwritten by hand there is no way to recover what it was.
This is not caution for its own sake. Manual cleaning leaves no trace: a reviewer opening the file next month cannot tell which cells were edited, by whom, or why. Power Query solves that properly by recording every step as a repeatable script, and once a cleaning routine will be run more than twice, that is where it belongs. The manual techniques here are still worth knowing, because they are how a file gets inspected in the first place.
14.2 Diagnosis Before Treatment
Four checks reveal most of what is wrong with a sheet, and all four take seconds.
Count numbers against count of entries. A gap means text hiding in a numeric column.
=COUNT(J2:J63) Numeric cells
=COUNTA(J2:J63) Non-empty cells
=COUNTBLANK(J2:J63) Empty cellsList the distinct values in a categorical column. Anything that should have five districts and returns eight has spelling or spacing problems. On Microsoft 365:
=SORT(UNIQUE(B2:B63))On older versions, a PivotTable with District in the Rows area does the same job.
Check character length against what it should be.
=LEN(B2)
=SUMPRODUCT(--(LEN(B2:B63) <> LEN(TRIM(B2:B63))))That second formula returns how many cells in the column carry stray spaces. Zero means the column is clean on that count.
Look for duplicates before removing them.
=COUNTIF($A$2:$A$63, A2) > 1Fill it down and filter for TRUE. Seeing which rows duplicate, and whether they are genuine repeats or two different farms sharing an ID by mistake, matters more than deleting them quickly.
14.3 Defect 1 and 2: Spaces and Capitalisation
TRIM removes leading and trailing spaces and collapses repeated internal spaces to one. CLEAN strips non-printing characters, which turn up in data exported from older systems.
=TRIM(B2)
=TRIM(CLEAN(B2))
=PROPER(TRIM(B2)) Trimmed and title-cased
=UPPER(TRIM(E2)) Trimmed and upper-casedTRIM does not remove the non-breaking space (character 160) that arrives with data copied from web pages. That one needs:
=TRIM(SUBSTITUTE(B2, CHAR(160), " "))Build the cleaned values in a helper column, then convert them to values before deleting the original. Select the helper column, copy, then Paste Special with Values selected (Ctrl + Alt + V, then V). Without that step, deleting the source column leaves the formulas pointing at nothing and the whole column returns #REF!.
14.4 Defect 3: Two Spellings of the Same Thing
Case and spacing are mechanical. Genuinely different spellings, Kurnool against Kurnul, need a decision about which is correct, and that decision should be recorded rather than typed over.
For one or two variants, Find and Replace (Ctrl + H) is enough. Tick Match entire cell contents, or replacing Kurnul risks catching substrings inside other values.
For anything more, build a correction table on a separate sheet with two columns, the wrong value and the right one, then map through it:
=IFERROR(VLOOKUP(B2, Corrections!$A$2:$B$20, 2, FALSE), B2)Any value found in the correction table is replaced; anything not found passes through unchanged. The table itself then documents every substitution made, which a column of manually retyped values never does.
14.5 Defect 4: Numbers Stored as Text
The symptom is a column of figures that left-aligns, often with a small green triangle in the corner of each cell. SUM on that column returns zero, or a total far too low, without any error.
Three fixes, in increasing order of reliability.
The green triangle. Select the range, click the warning icon, choose Convert to Number. Quick, but it fails silently on large ranges and does nothing when the triangle is not displayed.
Paste Special multiply. Type 1 into an empty cell and copy it. Select the offending range, press Ctrl + Alt + V, choose Values and Multiply, and confirm. Every cell is multiplied by 1, which forces numeric conversion in place. This works on ranges of any size.
Text to Columns. Select the single column, go to Data then Text to Columns, and click Finish without changing anything. Excel reparses each cell and converts what it can. Counterintuitive, and the most dependable of the three.
Where a formula is preferred:
=VALUE(J2)
=NUMBERVALUE(J2, ".", ",") When decimal and thousand separators need namingNumbers carrying a stray currency symbol or thousands separator need the character removed first:
=VALUE(SUBSTITUTE(SUBSTITUTE(J2, "₹", ""), ",", ""))14.6 Defect 5: Duplicate Rows
Identify first, using the COUNTIF check above. Then Data, Remove Duplicates, and choose the columns that define a duplicate.
The column choice is the whole decision. Ticking every column removes only rows identical in all respects. Ticking FarmID alone removes every row after the first for each ID, which is right when the ID is genuinely unique and wrong when the sheet holds one row per farm per season.
Excel reports how many rows were removed and how many remain. Note that number. If it removed more than the COUNTIF check predicted, the definition of a duplicate was wrong and the operation should be undone.
Conditional formatting also flags duplicates without deleting anything: Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values. For a first look this is safer than an operation that discards rows.
14.7 Defect 6: Missing Values
Find them first. Ctrl + G, then Special, then Blanks selects every empty cell in the current region at once, and the status bar reports how many. This is far more reliable than scrolling.
What to do about them is a judgement, not a procedure, and the choice has to be defensible:
Leave them empty. Excel’s statistical functions skip blanks. AVERAGE over a column with three blanks averages the 57 values present, which is usually what is wanted. This is the right default for exploratory work.
Delete the row. Defensible only when the missing field is essential to the analysis and the rows lost are few. Deleting 3 rows from 60 costs 5 percent of the data, which is material.
Impute. Filling with the column mean or median keeps the row usable but invents data, and it shrinks the apparent variance of that column. Where it is done, record it in a separate flag column:
=IF(I2="", MEDIAN($I$2:$I$63), I2)
=IF(I2="", 1, 0) Flag: was this value imputedThe one option that is never acceptable is filling blanks with zero in a numeric column. A soil moisture of 0 percent is a real and extreme measurement, not a missing one, and it will drag every mean and regression coefficient computed from that column.
Never leave a blank where the correct value is genuinely zero, either. Recording no fertilizer spend as blank rather than 0 makes the two indistinguishable.
14.8 Defect 7: Splitting a Combined Column
CropSeason holds values like PDY-Kharif: two variables in one column, which breaks the one-variable-per-column rule and blocks any grouping by season.
Text to Columns handles the general case. Select the column, Data then Text to Columns, choose Delimited, tick Other and enter -, then set the destination. Insert blank columns to the right first, or the split overwrites whatever is next to it.
Flash Fill is faster when the pattern is obvious. Type the first expected result in the adjacent column, then press Ctrl + E. Excel infers the pattern from the example and fills the rest. It reads intent from one or two samples and is remarkably good at it, though the results should always be spot-checked, since it infers rather than parses.
Formulas keep the split live, so it updates when the source changes:
=TEXTBEFORE(L2, "-") Microsoft 365
=TEXTAFTER(L2, "-") Microsoft 365
=TEXTSPLIT(L2, "-") Spills both parts across two cells
=LEFT(L2, FIND("-", L2) - 1) Any version
=MID(L2, FIND("-", L2) + 1, LEN(L2)) Any version14.9 Defect 8: Dates Stored as Text
A date that left-aligns and refuses to respond to date formatting is text. Sorting such a column puts 1 December before 3 March, because it is sorting strings.
=DATEVALUE(M2) Text to a date serial numberThen format the result as a date, since DATEVALUE returns the underlying number.
When DATEVALUE returns #VALUE!, the text does not match the system’s expected date order. Text to Columns solves this properly: select the column, Data then Text to Columns, click through to step 3, choose Date, and pick the order the text actually uses (DMY for 03/04/2026 meaning 3 April). Excel then parses it correctly regardless of regional settings.
Build a date from parts where the text is stubbornly non-standard:
=DATE(RIGHT(M2,4), MID(M2,4,2), LEFT(M2,2))14.10 Defect 9: Text Inside a Numeric Column
The literal string N/A sitting in Rainfall is not the #N/A error. It is text, and its presence makes the whole column text as far as AVERAGE is concerned.
Find every one of them before deciding:
=COUNTIF(G2:G63, "N/A")
=ISTEXT(G2)Replace with genuinely empty cells rather than zero, using Find and Replace with the Replace with box left blank and Match entire cell contents ticked. A missing rainfall reading is missing; it is not a rainfall of zero, and the difference changes every average computed from the column.
14.11 Preventing the Next Round: Data Validation
Cleaning the same file every month means the problem is upstream. Data Validation constrains what can be typed in the first place.
Select the column, go to Data then Data Validation, and set the rule. For a fixed list of districts, choose List and point Source at the lookup range:
=DistrictLookup!$A$2:$A$6For a numeric range, choose Decimal and set the bounds. NDVI cannot fall outside 0 to 1, so anything beyond that is a data entry error by definition:
Allow: Decimal Data: between Minimum: 0 Maximum: 1Add an Input Message so the rule explains itself when the cell is selected, and an Error Alert that states what is expected. A validation rule that rejects input without saying why gets switched off by the next person who needs to enter data.
Validation applies only to values typed after the rule is created. To find existing values that break it, use Data Validation then Circle Invalid Data, which rings every offending cell.
14.12 A Cleaning Checklist
| Step | Check | Tool |
|---|---|---|
| 1 | Raw sheet preserved | Copy the tab before editing |
| 2 | Headers in row 1, one per column, no spaces | Manual |
| 3 | No merged cells | Home, Merge and Center, Unmerge |
| 4 | Stray spaces removed |
TRIM, CLEAN, LEN check |
| 5 | Categories consistent |
UNIQUE or PivotTable, correction table |
| 6 | Numeric columns genuinely numeric |
COUNT against COUNTA
|
| 7 | Dates genuinely dates | Right-alignment, DATEVALUE
|
| 8 | Duplicates identified, then resolved |
COUNTIF, Remove Duplicates |
| 9 | Missing values located and a decision recorded |
Ctrl+G, Special, Blanks |
| 10 | One variable per column | Text to Columns, Flash Fill |
| 11 | Converted to a Table and named |
Ctrl + T
|
| 12 | Validation rules set for future entry | Data Validation |
Cleaned data should agree with FarmData at the end of this. Compare the two with a row count and a few totals before moving on.