Cleaning Data: The Unglamorous 80 Percent

Data reporters like to say that 80 percent of the work is cleaning. Nobody has measured it, and it feels low. Agency data arrives with three spellings of the same company, dates typed four ways, and a subtotal row parked in the middle like a car on a lawn. Every one of those problems changes your numbers.

First, protect the original

Save the file exactly as it arrived and never edit it. Name it so you can't confuse it: checkbook_RAW_received_2026-10-02.xlsx. Work in a copy.

Then open a plain text file next to it, your data diary, and write down each step as you take it. "Trimmed spaces in Vendor. Merged ACME PAVING, Acme Paving Inc. into Acme Paving. Deleted row 8, a duplicate of row 4 (same date, vendor, amount, check number 10442)." When the agency sends a corrected file next month, the diary lets you redo everything in twenty minutes. When an editor asks how you got a number, you hand it over.

A messy file, and what it does to a total

Here is how the first quarter of the Willow Bend checkbook might really arrive. The true total of the six real payments (July through September) is $205,400.

Row	Date	Vendor	Amount	Problem
2	7/3/2025	ACME PAVING	48200	Case differs
3	07/15/25	Blue Ridge Fuel	6450	Leading space
4	2025-08-02	Acme Paving Inc.	$112,750.00	Name variant; amount stored as text
5	Aug 19 2025	Northline Office Supply	1180	Date as text
6	07/15/25	Blue Ridge Fuel	6450	Duplicate of row 3
7	9-5-2025	Blue Ridge Fuel	9320	Trailing space
8	Sept 22, 2025	Harper Engr.	27500	Abbreviation
9		SUBTOTAL Q1	205400	Totals row inside data

Run =SUM(D2:D9) on this and you get $304,500. Here is the arithmetic. SUM skips the text "$112,750.00." It counts the duplicate $6,450. And it counts the subtotal of $205,400, which already contains everything above it.

• Numeric data rows: 48,200 + 6,450 + 1,180 + 6,450 + 9,320 + 27,500 = 99,100
• Plus the subtotal row: 99,100 + 205,400 = 304,500
• Correct answer: 205,400. You would have overstated spending by $99,100, or 48 percent.

Note that two errors pushed the total up and one pushed it down. Errors do not politely cancel out. Find each one.

Names

A pivot table groups exact text. "Blue Ridge Fuel" with a trailing space is a different vendor to the computer. Fix names in this order:

1. Spaces. Add a column with =TRIM(B2). It strips leading and trailing spaces and collapses double spaces.
2. Case. =PROPER(TRIM(B2)) gives Title Case; =UPPER(TRIM(B2)) gives capitals. Pick one.
3. Variants. Sort by the cleaned name and read down the list. "Acme Paving," "Acme Paving Inc." and "Acme Paving, Inc" sit next to each other. Add a Clean Vendor column and type the standard name, or use Find and Replace (Edit, Find and Replace in LibreOffice and Google Sheets) on the cleaned column.
4. Check. Build a quick pivot of Clean Vendor with Count. Every vendor should appear once.

Be careful with merges. "Harper Engr." and "Harper Engineering" are probably the same firm. "Valley Medical" and "Valley Medical Supply" might be two businesses. Check the vendor ID, tax ID or address if the agency gave you one, and ask if it didn't. Write the decision in the diary.

For files with thousands of names, OpenRefine, a free desktop program, can group similar spellings for you to review. It suggests. You decide.

Dates

A real date is a number underneath (LibreOffice and Excel count days from a starting date) displayed in a date format. Text that looks like a date can't be sorted by time or grouped by month.

• Test: change the cell format to a plain number. A real date turns into a five-digit number. Text stays as text.
• Convert: =DATEVALUE(A5) handles many text formats, depending on your locale settings. For stubborn ones, split the parts with Data, Text to Columns (LibreOffice) or Data, Split text to columns (Google Sheets), then rebuild with =DATE(year,month,day).
• Day-month swaps: in a file from a system set to day-month order, 03/07/2025 is July 3. Look for a date with a first number over 12. If you find 25/07/2025, the whole column is day-month.
• Two-digit years: "07/15/25" is presumably 2025. Confirm against the range in the record layout.

After converting, sort by date and look at both ends. A payment dated 1925 or 2205 is a typo, and it will wreck a month-by-month chart.

Numbers stored as text

Spreadsheets align numbers to the right and text to the left by default. A left-aligned amount is a warning. Fix it by removing dollar signs and commas with Find and Replace, then converting with =VALUE(D4), or by re-importing the CSV and setting the column type to number.

A quick test for the whole column: compare =COUNT(D2:D500) (counts numbers) with =COUNTA(D2:D500) (counts anything). If they differ, some cells are text.

Duplicates

Flag rows where every key field matches another row. In a new column:

=COUNTIFS($A$2:$A$500,A2,$B$2:$B$500,B2,$D$2:$D$500,D2)

Any result above 1 is a possible duplicate. Filter to those and look. Two identical fuel purchases on the same day can be real: two trucks filled up. A check number or invoice number settles it. If the agency's data has no unique ID, ask what system field would serve as one. Google Sheets also has Data, Data cleanup, Remove duplicates, but use it only after you have looked, since it deletes without asking why.

Totals rows

Agencies love subtotals. Filter the vendor or description column for words like total, subtotal, sum and balance. Delete those rows from your working copy (they live on in the raw file) and recompute totals yourself. Then compare your total with theirs. If they match, both of you probably got it right.

Tables trapped in PDFs

Budgets and audits arrive as PDFs. Try, in order:

1. Ask for the spreadsheet. The PDF was made from one.
2. Tabula. Free, open source, for Windows, Mac and Linux. You draw a box around a table and export CSV. It works only on text-based PDFs. If you can't select the words in your PDF viewer, it is a scan.
3. OCR for scans. OCR tools turn page images into text, and they confuse 5 with S, 0 with O and 1 with l. Check every OCR'd number column against the printed totals.

After any extraction, count rows and compare with the document. If page 14 has 31 line items, your sheet should have 31 rows for page 14. Then sum each column and match the PDF's printed totals.