Excel Data Cleaner

The Five Cleanup Rules, and the Order They Run In

Cleanup is a single pass over the data rows, and the order matters because the rules interact. For each cell: if Fill blank cells is on and the cell is blank, it becomes your fill text and nothing else touches it. Trim leading/trailing spaces then strips whitespace from both ends, and Collapse repeated spaces replaces every run of whitespace inside the value with a single space - which also flattens tabs and line breaks that came in from a paste. Both only apply to text cells; numbers, dates, and TRUE/FALSE pass through unchanged. Once every cell is settled, Remove empty rows drops rows where every cell is blank, and Remove duplicate rows runs last, so it compares the already-cleaned values.

Text Case and Column Scope

Text case can be left at None or set to UPPERCASE, lowercase, or Title Case. Title Case lowercases the whole value first and then uppercases the first letter of each word, so it normalizes SHOUTED imports well but flattens internal capitals - MCDONALD becomes Mcdonald. If you leave the case column list empty the change applies to every text cell in the sheet; tick specific columns to confine it to, say, City and Country while leaving product codes alone.

Reading the Report

Nothing is written until you press Apply Cleanup, and the six counters that appear are the evidence for what happened: rows retained and removed, how many of those removals were duplicates and how many were empty rows, how many cells had whitespace changed, and how many blanks were filled. If a counter is zero, the rule found nothing - that is the fastest way to check whether the padding you suspected was really there. The preview below shows the first 30 cleaned rows; the download writes every row to a single sheet.

Edge Cases Worth Knowing

Non-breaking spaces from HTML pastes are whitespace to the collapse rule but are not always what Excel's own TRIM removes, so a value that looked untrimmable in a formula usually cleans here. A row that looks empty but holds a formula returning an empty string reaches this tool as a blank and is removed. Date cells arrive as the Excel serial number underneath them and the download writes values without number formats, so reapply a date format afterwards. On a very large sheet everything is held in browser memory at once - a few hundred thousand rows is fine on a desktop, but split a multi-million row export first.

When a Different Tool Fits Better

If duplicates are the whole job - choosing match columns, keeping the first or last occurrence - use Remove Duplicates in Excel, which explains those rules properly. If you only want empty rows and empty columns gone with a count of each, Remove Blank Rows is a single-click version. To replace one specific wrong value everywhere rather than normalizing shape, use Excel Find and Replace, and to keep the cleanup as a live formula in the sheet instead of a downloaded copy, the TRIM formula generator writes it for you.