John SerraDon’t clean data on the sheet you received. Keep three tabs: Raw, Clean, and Exclusions, plus a small Transformation_Log. Leave Raw untouched as your baseline, do all the cleaning on a copy in Clean, move every row you remove into Exclusions with a reason, and write down every rule you apply to the values that stay. Work in the same order every time: trim spaces, standardize categories, set aside unresolved blanks, set aside confirmed duplicates, then run checks that show Clean plus Exclusions accounts for every row in Raw.
The point is an audit trail. When a number looks wrong next month, you can see what changed, what was removed, and why.
Why Editing in Place Goes Wrong
Messy exports cause three problems that compound:
- Broken lookups. Invisible spaces make “Hardware” and “Hardware ” different text, so a VLOOKUP or XLOOKUP returns #N/A on values that look identical.
- Split reports. Extra spaces and inconsistent capitalization can break one category into several in summaries.
- Silent loss. If you delete a duplicate without logging it, you can’t later prove that you removed a duplicate rather than a real sale.
Undo, backup copies, and version history can help, but exports change from run to run. A preserved Raw tab gives you a baseline you control.
The Tabs
- Raw holds the export exactly as you received it. Never edit it. Once your Clean copy exists, protect it so edits are blocked. In Google Sheets, choose Data > Protect sheets and ranges, select the Raw sheet, choose Set permissions, and restrict editing to the people you name. A warning-only setting doesn’t block anyone. In desktop Excel, use Review > Protect Sheet, which locks the sheet’s locked cells. A typing attempt then gets a message that the cell is on a protected sheet. In Excel for the web, use Review > Manage Protection. Protection reduces accidents. It isn’t a tamper-proof archive, and coloring the tab doesn’t protect anything.
- Clean is the working copy, formatted as a table, with an extra column named Original_Row_ID that records each row’s position in the source.
- Exclusions holds every row you take out of Clean, with the same headings in A1:H1 plus Exclusion_Reason in I1 and Action_Taken in J1.
- Transformation_Log records the changes you make to the rows that stay, in three columns: Field, Rule, and Reason.
The rule that ties them together: every record leaves Clean only by moving to Exclusions, and every change to a value that stays is written in the log. Nothing is simply deleted or silently rewritten.
The Example Data
This is a made-up export of six records. Type it into a tab named Raw, with the headings in A1:G1 and the records in rows 2 to 7:
| Transaction_ID | Transaction_Date | Customer_Name | Product_Category | Quantity | Unit_Price | Total_Amount |
|---|---|---|---|---|---|---|
| TX-101 | 2026-10-01 | Acme Corp | Hardware | 2 | 50.00 | 100.00 |
| TX-102 | 2026-10-02 | ” Beta Supply “ | ” Hardware “ | 4 | 25.00 | 100.00 |
| TX-103 | 2026-10-03 | Charlie LLC | hardware | 1 | 150.00 | 150.00 |
| TX-104 | 2026-10-04 | Delta Inc | Services | 1 | 80.00 | 80.00 |
| TX-104 | 2026-10-04 | Delta Inc | Services | 1 | 80.00 | 80.00 |
| TX-105 | 2026-10-05 | Echo Labs | Consulting | 1 |
Don’t type the quotation marks in row 3. They only show the spaces: two before and two after the customer name (15 characters in all), and one before and one after the category (10 characters). In the last row, Unit_Price and Total_Amount are truly empty cells, not the word “blank”. The problems are the extra spaces in row 3, the lowercase category in row 4, the repeated TX-104 record in rows 5 and 6, and the missing price and amount in row 7.
Assume this system issues exactly one billing record per Transaction_ID. That rule is what makes the second TX-104 record (worksheet row 6, Original_Row_ID 5) a duplicate export error. Without such a rule, repeated IDs are only a clue, not proof. The known amounts add up to $510.00, and the missing amount is unknown, not zero.
The Five Steps
1. Copy Raw to Clean and number the rows. Duplicate the Raw tab and name the copy Clean (in Excel, right-click the tab, choose Move or Copy, and tick Create a copy; in Google Sheets, right-click and choose Duplicate). In column H, add the header Original_Row_ID and type the numbers 1 through 6 in H2:H7 as fixed values, not formulas. Then convert A1:H7 into a table (in Excel, Ctrl+T; in Google Sheets, Format > Convert to table). Check that the table includes column H. You should see six data rows and Raw unchanged.
2. Trim spaces and standardize categories. Before you change anything, check the lengths on the Clean tab: =LEN(C3) should return 15 and =LEN(D3) should return 10. Excel’s Data > From Text/CSV keeps these spaces, but some entry and import routes, including some CSV imports into Google Sheets, trim them automatically. If yours did, the lengths will already be 11 and 8 and this step does nothing for that row. To see the effect, rebuild the padded text on a disposable copy. Then, leaving column I empty as a spacer (in Excel, a formula typed directly beside a table can be pulled into the table and copied down every row), enter =TRIM(C2) in J2 and =PROPER(TRIM(D2)) in K2 and fill both down to row 7. TRIM strips leading and trailing spaces and collapses repeated inner spaces. PROPER sets title case, which is safe for this small made-up vocabulary but can damage meaningful capitalization such as “iPhone” or “SKU”. For real controlled labels, look each value up in an approved category map instead and stop on anything it can’t map. Check row 3: “Beta Supply” is now 11 characters instead of 15, and its category reads “Hardware” with 8 characters instead of 10. Then copy J2:K7 and paste it as values only over C2:D7 (in Excel, Ctrl+Alt+V then V; in Google Sheets, Ctrl+Shift+V). Delete columns J and K last (right-click the column and choose Delete in Excel, or Delete column in Google Sheets). Deleting them before pasting values throws away the cleaned results, and Clean keeps the uncleaned text. It also causes #REF! errors in any other formula that points at a deleted helper. Finally, add two lines to the Transformation_Log: Customer_Name, TRIM, “Remove stray leading, trailing, and repeated spaces”; and Product_Category, PROPER(TRIM()), “Standardize capitalization on the approved vocabulary”. Add a third for Original_Row_ID: typed values 1 to 6, “Link each Clean row to its Raw row”.
3. Set aside unresolved blanks. TX-105 has no price or amount. Don’t fill in a zero, because the amount is unknown. Copy the row’s A:H cells, paste them as values into the first empty row of Exclusions, and fill in Exclusion_Reason (“Missing transaction amount”) and Action_Taken (“Quarantined pending invoice check”). Confirm the row landed correctly, and only then delete it from Clean (right-click a cell in that row, then Delete > Table Rows in Excel or Delete table row in Google Sheets). Clean now has five rows.
4. Set aside confirmed duplicates. The second TX-104 is identical to the first, and the one-record-per-ID rule says it’s an export error. Copy it to Exclusions the same way, with the reason “Duplicate transaction row” and the action “Quarantined duplicate occurrence; retained Original_Row_ID 4 (worksheet row 5) in Clean,” then delete it from Clean. Clean now has four rows and Exclusions has two.
5. Build the checks. Do this last. Deleting a table row can shift cells beneath the table’s columns up, so any summary placed under the table before this point can move. Put the checks on Clean, to the right of the table, after the structural changes are done.
Prove Nothing Went Missing
Start with the aggregate controls, which should all return TRUE:
- Row counts:
=COUNTA(Clean!H2:H5)+COUNTA(Exclusions!H2:H3)=COUNTA(Raw!A2:A7)(4 + 2 = 6) - Known amounts:
=ROUND(SUM(Raw!G2:G7)-(SUM(Clean!G2:G5)+SUM(Exclusions!G2:G3)),2)=0($430.00 + $80.00 = $510.00) - Numbers that are really numbers:
=COUNT(Clean!G2:G5)=COUNTA(Clean!G2:G5), and the same pattern for Raw and Exclusions. It returns FALSE if any amount is text or an error. It is a type check, not proof that the amounts are right.
Then add one missing-amount count: =COUNTIF(Exclusions!I2:I3,"Missing transaction amount") returns 1. That is the number of records quarantined for a missing amount. It doesn’t track whether anyone has resolved them, so decide how you will mark an item closed.
These controls are necessary but not sufficient. Suppose the Clean tab went wrong like this: TX-101 ($100) was deleted by accident and TX-102 ($100) was pasted twice. The count is still 4, the total is still $430, and every aggregate check passes, but a customer’s transaction is missing.
The check that catches it works per source row. In Clean, list the source IDs 1 to 6 in L2:L7 as fixed values. In M2, enter and fill down to M7:
=COUNTIF(Clean!$H$2:$H$5,L2)+COUNTIF(Exclusions!$H$2:$H$3,L2)
In N2, enter and fill down:
=IF(M2=1,"OK",IF(M2=0,"MISSING","DUPLICATE"))
Every row should read OK. In the broken case above, ID 1 would show MISSING and ID 2 would show DUPLICATE. Finish by comparing the fields you didn’t change, such as date, quantity, and unit price, against Raw for a few rows.
When controls fail, treat the result as a clue. Matching totals with different counts can mean an unlogged zero-dollar row was removed or added. Matching counts with different totals can mean an edited amount, a text-formatted number that SUM ignores, or two records swapped.
When It Still Goes Wrong
| What you see | Likely cause | What to do |
|---|---|---|
| A lookup still returns #N/A after TRIM. | Web exports often contain non-breaking spaces, which TRIM does not remove. | Use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). |
| Clean plus Exclusions doesn’t match Raw. | An unlogged deletion, an overwritten amount, rounding, or amounts stored as text. | Check the row count first. If it matches, compare COUNT with COUNTA, then look at the decimals. |
| An Excel pivot table still shows the old category names. | The pivot table’s cache keeps old items. | Right-click and Refresh. If a stale item remains, check PivotTable Options > Data and the setting for retaining items deleted from the data source. |
| Clean still shows the padded or lowercase text, or a formula shows #REF!. | You deleted a helper column before pasting its values, so the cleaned results were lost, or another formula pointed at the deleted column. | Undo, then copy the helper columns and paste them as values over the originals before deleting them. |
Frequently Asked Questions
Why not just use Remove Duplicates?
The tool removes rows without keeping a record of them. If your data has several lines per order, running it on Transaction_ID alone deletes legitimate lines. Look at the duplicate candidates first, decide using a rule from the source system, and then move rows to Exclusions so there is a log.
Does TRIM work the same in Excel and Google Sheets?
For ordinary spaces, yes. Both remove leading and trailing spaces and collapse repeated inner spaces. Neither removes non-breaking spaces from web exports, so use the SUBSTITUTE version in both.
When should I automate this?
When you repeat the same steps on recurring exports, when the manual copy and logging work is tiring enough to cause mistakes, or when the file is large enough to make the sheet sluggish. Excel’s Power Query and scripts in Google Sheets are the usual next steps. Don’t pick a row-count threshold. Base it on how often you repeat the job and how costly a mistake is.
What to Do Next
A clean file is only part of a trustworthy report. For a wider set of checks on formulas, hardcoded values, and links before you share numbers, use The Small Business Data Audit Checklist. If your cleaned data will feed a comparison with bank or processor figures, why your business numbers don’t match across systems explains the usual gaps.