Remove empty rows

How to remove empty rows from a spreadsheet

1. Drop the file. Pick the sheet if the workbook has several. Nothing is uploaded.
2. Read what was removed. The counts above the table say how many empty rows went, how many cells were trimmed, and how many repeated rows were dropped if you asked for that.
3. Turn on the extras you want. Collapsing internal spaces, dropping empty columns and removing duplicate rows are each optional and each reported separately.
4. Download the clean sheet. CSV for a script, .xlsx to carry on in Excel. The header row is kept and never treated as a duplicate.

Why the built-in methods leave rows behind

The two answers you will find are Go To Special → Blanks → Delete Rows and Filter → (Blanks) → delete. Both define "blank" as Excel does: a cell with nothing in it at all. A cell holding a single space is not blank to Excel, and neither is one holding a non-breaking space or a zero-width space. Those rows look empty on screen and survive both methods, which is why the job never seems to finish.

Here a row is empty when every cell in it is empty after invisible characters are stripped and surrounding whitespace is trimmed. That is the definition a person means.

• The non-breaking space (U+00A0) arrives with anything copied from a web page or a Word document.
• The zero-width space (U+200B) and the byte-order mark (U+FEFF) come out of PDF extractions and some exports; they are invisible even when you click into the cell.
• A plain space is often left behind by a formula that returned "" and was then pasted as values.

Go To Special also has a footgun

Selecting blanks and deleting rows acts on every blank cell in the selection, not only fully empty rows. On a sheet where one column has gaps, that deletes rows that hold real data elsewhere. This page only removes a row when the whole row is empty, and tells you how many that was before you download anything.

The optional extras

• Trim spaces around every cell is on by default and is usually what you also wanted — it is the reason two copies of the same value do not match.
• Collapse runs of spaces turns Ada  Byron into Ada Byron. Off by default, because it changes values.
• Remove entirely empty columns deals with the trailing columns an export leaves behind.
• Remove duplicate rows compares rows after the cleanup, so two rows differing only by a trailing space count as the same. The header row never counts as a duplicate.

When the same export needs the same cleanup every month

A tab is the right tool the first few times. A cleanup that runs on a schedule, with a record of what it removed and something that flags the month the file arrives in a different shape, is a job.

TableDI 2 is a desktop app for file work you redo every period: it keeps your sources, rules and delivery as a job, so next month you drop in the new files and run it again. Everything runs on your own machine.