Data Cleaning, Sorting, and Analysis Tips in Google Sheets
July 24th, 2026
Most spreadsheet problems aren't formula problems — they're data problems. A VLOOKUP that "randomly" fails, a SUM that comes back short, a dropdown full of typo'd variants of the same value: nine times out of ten the formula is fine and the data feeding it is dirty. This post covers the toolkit for fixing that: cleaning messy imported text, locking down bad input before it happens, reshaping data without touching the source, and catching errors before your users ever see one.
The messy import: a familiar starting point
Imagine you export a customer list from an old CRM into a CSV and paste it into Sheets. Column A, Name, looks fine at a glance but is quietly full of problems:
| A (Name) | B (Email) | C (Status) |
| --- | --- | --- |
| John Smith | JOHN@EXAMPLE.COM | active |
| Jane Doe | jane@example.com | Active |
| Bob Lee (with a hidden control character) | bob@example.com | ACTIVE |
A =COUNTIF(C2:C4, "active") will undercount, because "active", "Active", and "ACTIVE" are different strings to COUNTIF's exact-match cousins, and a stray non-printable character in "Bob Lee" can make it fail to match anything at all in a lookup. Before you analyze this data, you clean it.
TRIM: collapsing extra spaces
TRIM removes leading and trailing spaces and collapses repeated internal spaces down to one:
=TRIM(A2)
Applied to John Smith, this returns John Smith. Applied to Jane Doe (multiple internal spaces plus trailing ones), it returns Jane Doe. This matters more than it looks: two cells that appear identical on screen — "John Smith" and "John Smith " — are not equal as far as =A2=B2 is concerned, and a trailing space silently breaks exact-match lookups like VLOOKUP or MATCH against a cleaned list.
CLEAN: stripping invisible characters
TRIM only handles ordinary spaces. Data pasted from PDFs, web scrapes, or older export formats often carries non-printable ASCII control characters that are invisible on screen but present in the cell. CLEAN strips those out:
=CLEAN(A2)
The two are usually combined, since real-world imports have both problems at once:
=TRIM(CLEAN(A2))
Run that down column A in a helper column, then paste the results back as values (paste special → values only) to replace the original messy text once you're satisfied. This is the single most useful one-line habit for any "data pasted from somewhere else" workflow.
Stopping bad data at the source with data validation
Cleaning formulas fix data after the fact. Data validation prevents bad data from being entered in the first place — cheaper than any cleanup formula, because it never has to run. Select the Status column, then go to Data → Data validation, and set criterion to "List of items" with active, inactive, pending. Anyone typing directly into that column now gets a dropdown instead of a free-text field, and a stray "actve" typo becomes impossible rather than something you have to catch later with COUNTIF and TRIM.
Data validation can also restrict to a number range, a date, or a custom formula — for example, requiring an email-shaped value:
=REGEXMATCH(A1, "^[^@]+@[^@]+\.[^@]+$")
Used as a custom formula validation rule on the Email column, this rejects entries that don't look like an email address at all, catching mistakes at entry time instead of during analysis three weeks later.
SORT and FILTER: reshaping without touching the source
Once the data is clean, you often want a different view of it — top customers, only active accounts, a specific date range — without disturbing the original range that other formulas or people depend on.
SORT returns a sorted copy:
=SORT(A2:C100, 3, TRUE)
This sorts the range by column 3 (Status) in ascending order, leaving A2:C100 itself untouched — you get a new, ordered array spilling out from whatever cell holds the formula.
FILTER returns only the rows matching a condition:
=FILTER(A2:C100, C2:C100 = "active")
The two combine naturally: filter first, then sort the filtered result, or wrap one inside the other:
=SORT(FILTER(A2:C100, C2:C100 = "active"), 1, TRUE)
That single formula produces an alphabetically sorted list of only the active customers, recalculating automatically whenever the source data changes — no manual re-filtering, no separate "clean copy" sheet to maintain by hand.
IFERROR, IFNA, and ISERROR: failing gracefully
Even clean, validated data produces formula errors sometimes — a VLOOKUP with no match, a division by a blank cell, a QUERY against a range that's temporarily empty. Left alone, these show up as #N/A, #DIV/0!, or #REF! directly in front of whoever's reading the sheet, which looks broken even when it isn't.
IFERROR catches any error type and substitutes a fallback value:
=IFERROR(A2/B2, "N/A")
If B2 is zero or blank, this returns "N/A" instead of #DIV/0!. It's the general-purpose safety net — wrap it around any formula that might fail for more than one reason.
IFNA is the narrower tool: it only catches #N/A, letting other error types (which usually indicate an actual bug in the formula, not a legitimate "not found" case) surface normally:
=IFNA(VLOOKUP(A2, CustomerList, 2, FALSE), "Not on file")
This is the better choice specifically for lookups, because a #REF! or #NAME? error from a broken formula reference will still show up loudly instead of being silently swallowed alongside the expected "no match" case.
ISERROR doesn't replace a value — it tests for one, returning TRUE or FALSE, which is useful when you need to branch on whether an error occurred rather than just hide it:
=IF(ISERROR(A2/B2), "Check this row", A2/B2)
That pattern is handy in a status or QA column: flag rows with problems for a human to look at, rather than either showing raw error codes or silently hiding the fact that something went wrong.
Putting it together
A realistic cleanup-to-analysis pipeline for the messy import above looks like this: TRIM and CLEAN raw text columns into a staging area, apply data validation to any columns that get manually edited going forward, then build a FILTER/SORT view on top of the cleaned range for reporting, with IFERROR or IFNA wrapped around any lookup or calculation formula that touches the result. Each step is a small, separate formula — but stacked together, they turn an unreliable CSV export into a spreadsheet that's actually safe to build a dashboard on top of.