Upload a workbook and remove the empty rows from one sheet or all of them, or the rows that are empty in a column you pick. See how many go on each sheet first, then download your own file with those rows gone and everything else kept.
Nothing is uploaded. Your file is edited in your browser and never sent anywhere.
This tool lets you remove blank rows in Excel files without opening Excel: drop in an .xlsx or .xlsm, and the empty rows between the first and last rows of data are deleted, on one sheet or all of them, in your browser. A few are kept for a reason the preview names. The file you get back is your own workbook with those rows gone. The usual case is a report exported with an empty row after every record, which prints on twice as many pages as it should.
sales.xlsx downloads as sales (xlsx.com).xlsx, with nothing added inside it.The preview count and the deletion come from the same code, so the number you approve is the number you get.
A row is blank when no cell in it holds a value or a formula. Formatting doesn't count as content, and a formula always does, even one that displays nothing. That puts the tool at odds with Excel's own methods in a couple of places:
| What the row holds | This tool | Go To Special > Blanks | COUNTA |
|---|---|---|---|
| Nothing at all | Blank, removed | Selects it | 0 |
| Only a fill colour or borders | Blank, removed | Selects it | 0 |
Empty text, as Paste Values leaves behind from a ="" formula | Blank, removed | Skips it | Counts it |
A formula showing nothing, such as ="" | Kept | Skips it | Counts it |
Only the blank rows between the first and last rows with data go. Empty rows above a title and the thousands of formatted rows under a table stay where they are. Blank rows hidden by a filter or by hand are removed like any other blank row.
Some blank rows are kept on purpose, and the preview says how many and why: a row with a note or comment on one of its cells, a row with a hyperlink, a row that is part of cells merged across rows, a row holding a sparkline, and the last data row of an Excel table that would otherwise have none. Excel needs every table to keep at least one data row.
The method on most pages, Home > Find & Select > Go To Special > Blanks followed by Delete > Entire row, selects every empty cell in the range. Run it over a whole table and a customer row with no phone number is deleted along with the truly empty ones. On a 5,000-row export with patchy optional columns, that can be hundreds of real records, with no warning.
The tool keeps the two jobs apart. "Rows that are completely empty" tests every cell in the row. "Rows that are empty in one column" is the deliberate version of the Go To Special trick: it removes every row whose cell in the column you pick is empty, on one sheet, and it removes the whole row, including whatever sits in the other columns. The page says so under the option. The heading row is kept by default, and a table's header and total rows, rows holding a sparkline and a table's last data row are never picked. A cell inside merged cells counts as holding the merge's value, so a row covered by a merged label isn't mistaken for an empty one. Notes on rows removed this way go with them, and the result says how many.
The download is your workbook with the rows deleted as if you had deleted them in Excel. Everything that pointed below a removed row moves up to match: formulas on every sheet, defined names, Excel tables, chart series, merged cells, conditional formatting, data validation, hyperlinks, the autofilter, page breaks, notes and sparklines. With three blank rows removed inside it, this total:
=SUM(B2:B20)becomes =SUM(B2:B17) and still adds up the same numbers.
A formula that pointed only at cells in removed rows shows #REF! afterwards, as it would in Excel (=B7 when row 7 goes). The result says how many formulas that happened to, so you know whether to look. INDIRECT and other formulas that build a reference from text are not rewritten, which is also how Excel behaves.
These rules were checked against Excel itself. An automated test compares the tool's output with the same rows deleted in Excel, reference by reference, and a real 45-sheet workbook with 196 blank rows removed matched Excel on every formula, format, validation rule, name, table, note and sparkline.
Everything else in the file is kept byte for byte: formatting, column widths, charts, pictures, pivot tables on other sheets and the macros in an .xlsm. The file is edited, not rebuilt. Charts, pictures and shapes over the sheet move up with their rows and keep their size, where Excel would shrink a chart whose rows were deleted underneath it. Objects set to "Don't move or size with cells" stay put.
The workbook asks Excel to recalculate when it opens, so results are current. That's why Excel may ask to save changes when you close it.
For one sheet in a file you already have open, a helper column is the safe manual route. In the first empty column beside the data (G here, for data in A to F), enter:
=COUNTA(A2:F2)=0Fill it down, turn on Data > Filter, filter column G for TRUE, select the visible rows and use Home > Delete > Delete Sheet Rows. Clear the filter and delete column G. Excel deletes only the visible rows of a filtered list, so rows with data are safe. COUNTA treats empty text and ="" as filled, so rows holding them survive this method.
Power Query has a one-click version. Select the whole range, blank rows included, and use Data > From Table/Range, which makes it a table; then Home > Remove Rows > Remove Blank Rows and Close & Load. That loads a cleaned copy on a new sheet, and formulas pointing at the original don't follow it.
Sorting pushes the blanks to the bottom where one selection deletes them, and it also scrambles the order of a ledger or a dated log. Add a column numbered 1, 2, 3 first and sort back by it afterwards.
The manual routes suit a file you already have open. The tool is quicker when the blanks are spread over several sheets, or when formulas, charts and tables sit around the data.
SUM(Jan:Dec!B5) includes the sheet, when an Excel table would lose its header or total row, when the sheet has what-if scenarios, or when a removed row holds a sparkline.If the cleaned sheet is one of two lists you need to reconcile, Compare Two Columns shows which values are in both and which are only in one.
Test the whole row, not one cell. Put =COUNTA(A2:F2)=0 in a spare column, fill it down, filter that column for TRUE, then delete the visible rows with Home > Delete > Delete Sheet Rows and clear the filter. Go To Special > Blanks across the whole table selects every empty cell, so deleting entire rows from that selection also removes rows that are only partly empty.
Select that one column, press F5, click Special, choose Blanks and click OK, then use Home > Delete > Delete Sheet Rows. The whole row goes, including anything in its other columns, so scan the selection first. The same job in this tool is the Rows that are empty in one column option, which works on one sheet and keeps the heading row by default.
Something is in it that you can't see. The usual culprits are a formula returning an empty string, such as ="" or an IF with "" as its answer, text left behind by Paste Values, or a single space from an import. Go To Special > Blanks skips all of those cells, and COUNTA counts them as filled. This tool removes a row whose only content is empty text, and keeps a row with a formula even when it shows nothing.
Click the row number of the first empty row under your data, press Ctrl+Shift+Down to select to the last row, delete them with Delete Sheet Rows, then save. Saving is what resets the last cell, so Ctrl+End stops jumping to row 40,000. This tool leaves rows below the last row with data alone, so it won't fix that.
A formula pointed only at cells in the rows you deleted. =B7 turns into =#REF! when row 7 goes, while a range that runs across the deleted rows, such as SUM(B2:B20), shrinks to fit and keeps working. This tool follows the same rule and tells you how many formulas ended up as #REF!.
In this tool, yes, for completely empty rows: leave All sheets selected and the preview lists what goes on each sheet. The one-column option works on one sheet at a time. In Excel, the helper-column and Go To Special methods have to be repeated sheet by sheet, because the blank rows sit at different row numbers on each one.