How to Delete Every Blank Row in Excel at Once

You pasted a report or an export into Excel and every few lines there is an empty row, so sorting and formulas break. Deleting them one at a time takes forever. The wrong assumption is that Excel has a "delete blank rows" button. It does not, but it can select every blank cell in a column in one go, and deleting the rows behind those cells clears the whole sheet in a few clicks. The trick is picking the right column first, so a row with one missing value does not get thrown out with the truly empty ones.

Select one column that is filled in every real row

Pick a column that never has gaps in real data, such as an ID, a date, or a name. Click its letter at the top to select the whole column, or drag from the first data cell down to the last one. Do not select the entire sheet. Go To Special selects blank cells, not blank rows, so if you include a column with optional values, rows that are only missing that value will be deleted too.

Open Go To Special and pick Blanks

On the Home tab, click Find & Select, then Go To Special. You can also press Ctrl + G (or F5) and click Special in the dialog that opens. This works on Windows and Mac. In the Go To Special dialog, select Blanks and click OK. Every empty cell in your selected column is now highlighted. If Excel says no cells were found, the column has no true blanks; the empty-looking cells probably contain a space or a formula that returns an empty string, and the note at the end covers that.

Delete the rows behind the selected cells

With the blank cells still highlighted, press Ctrl + Minus on Windows or Command + Hyphen on Mac. The Delete dialog opens. Choose Entire row and click OK. All the blank rows disappear and everything below shifts up. You can instead right-click any highlighted cell, choose Delete, and pick Entire row in the same dialog. Do not press the Delete key on the keyboard; that only clears cell contents and leaves the rows in place.

Check the result before you save

Scroll through the sheet and confirm no real row is missing. If one is, press Ctrl + Z (Command + Z on Mac) once and everything comes back, then go back to step one and pick a more reliable column. A quick check is to note the last row number before and after: the difference should match the number of blank rows you expected.

If no column is filled in every row. Add a helper column at the right edge and, in the first data row, type a formula like =COUNTA(A2:F2) with the range covering that row's columns, then fill it down. A true blank row scores 0. Click any cell in the data, go to Data, click Filter, open the arrow on the helper column, clear (Select All), check 0, and click OK. Select the visible row numbers, press Ctrl + Minus, then click Filter again to turn it off and delete the helper column.

If the blank cells contain a space or an empty string. Go To Special skips them because they are not truly empty. Select the column, press Ctrl + H, put a single space in Find what, leave Replace with empty, and click Replace All. Cells with a formula that returns "" need the formula removed or the values pasted over the formulas first.

If the data is a formatted Table. The Delete dialog does not appear inside a Table. Right-click a highlighted cell, choose Delete, then Table Rows. Only rows that are blank in the selected column are removed.