How to Clean a Messy Spreadsheet Without Writing Formulas

The report that's only fair if the data is complete

An analytics team is putting together a rep-performance report — who's closing what, ranked so leadership can see it at a glance. Partway through, someone notices a handful of orders have no rep attached at all: no name, just a blank cell. Left in, those orders either get silently dropped from a grouped total (which undercounts the business) or lumped into a confusing "blank" category at the bottom of the ranking, which makes the whole report look sloppy in front of the room it's for.

The fix is conceptually trivial — exclude the rows with no rep before anything gets totaled. The annoying part is that "exclude these rows" turns into a =IF(ISBLANK(...)) formula, a helper column, and a filter that has to be reapplied by hand the next time this report runs, because none of that lives anywhere except this one spreadsheet.

Why this keeps happening

Messy data isn't usually dramatic — it's rarely a completely broken file. It's a handful of rows, out of thousands, with something slightly off: a blank cell here, a duplicate-looking entry there, a date typed in a different format than the rest. None of it is enough to make the file unusable. All of it is enough to quietly skew a total if it's not dealt with before the numbers get calculated.

Because the "messy" part is small and easy to overlook, it's exactly the kind of thing a formula-based fix handles clumsily: technically correct, but rebuilt from scratch — or worse, silently missing — the next time the file changes.

The manual approach

1. Sort or filter manually to spot rows with an obvious gap — a blank cell in a column that should never be blank.
2. Write a formula (ISBLANK, COUNTIF, or similar) to flag them, usually in a new helper column nobody asked for.
3. Filter those rows out, or delete them, before running the actual report.
4. Remember to redo steps 1–3 next time, since none of it saved automatically with the file's normal use.

Why the manual version breaks

• The exclusion logic lives in a formula, not a documented rule. Six months later, nobody can look at the spreadsheet and immediately tell what "clean" was supposed to mean for this report.
• Helper columns accumulate. Each new thing that needs fixing gets its own formula column, until the sheet has more scaffolding than actual data.
• It's easy to forget a step. If the filter isn't reapplied before the numbers are calculated, the "clean" version and the "actual" version silently diverge.

Filtering out the gap, once

Here's the same rep-performance report, with incomplete rows excluded by a Filter node instead of a formula — built once, not reconstructed by hand each time the report runs.

Step 1 — start from the dataset

Whether it's a live PostgreSQL table or an imported CSV, the same Input node works either way — see importing a CSV into a reusable dataset if the data starts life as a file.

Step 2 — exclude the incomplete rows

Add a Filter node with the condition sales_rep is not blank. This is the entire "cleaning" step — no helper column, no formula to maintain, and the canvas label states the exclusion rule in plain terms, permanently, for anyone who opens this workflow later.

Step 3 — build the report on the clean subset

Everything downstream — a Summarize node computing average order value per rep, a Sort node ranking the result — now runs against only the rows that actually belong in a rep-performance report. Average, not total, is deliberate here: a straight revenue total rewards whoever has the most orders, not necessarily the strongest close rate, and this is a report about performance, not volume.

SELECT sales_rep, AVG(revenue) AS avg_order_value
FROM sales_orders
WHERE sales_rep IS NOT NULL
GROUP BY sales_rep
ORDER BY avg_order_value DESC

Knowing what to clean in the first place

Filtering out bad rows only works if you know which rows are bad — that's what a column profile is for. In the canonical dataset here, the profile shows sales_rep filled on about 98.4% of rows before any cleanup — the other 1.6% is exactly what this Filter condition removes. Reading the profile first turns "clean this spreadsheet" from a vague instruction into a specific, checkable number.

What comes out the other side

Before: a rep-performance report with a silent gap — either uncounted orders or a confusing "blank" row in the ranking, and a formula somewhere that may or may not have been reapplied correctly.

After: a workflow that states its own exclusion rule on the canvas, produces a real query that can be checked, and runs the same way every time it's rerun.

Key takeaways

• Messy data is usually a small number of specific rows, not a broken file — a single Filter condition often does the entire "cleaning" job.
• The exclusion rule lives on the canvas permanently, instead of in a formula that has to be remembered and reapplied.
• Profiling first (fill rate, in this case) turns "clean this up" into a specific, verifiable number instead of a guess.

Next

Cleaning isn't the only thing a profile can catch before you build — how to spot duplicate records using column uniqueness stats covers a different kind of issue the same profiling habit surfaces. (For the profiling step itself, see a field guide to reading a data profile report.)