How to Remove Blank Rows in Excel

To remove blank rows in Excel is to turn a gap-riddled export into a solid block of data. Unlike deleting rows one by one, the methods below select every empty row first, then clear them in a single action. This post covers Go To Special, filtering, sorting, and Power Query in Excel, the Google Sheets equivalent, and a browser method for CSV files.

Go To Special selects every blank at once

Go To Special is the standard fix because it finds every empty cell in your selection in one step, no scrolling required.

Select the columns that hold your data — click the first column header and drag across to the last. On the Home tab, open Find & Select > Go To, or press Ctrl+G, then click Special. Microsoft's own documentation lists this exact path for finding cells by condition [1]. In the dialog, choose Blanks and click OK. Excel now has every empty cell in that range selected at once.

With the blanks still highlighted, go to Home > Delete > Delete Sheet Rows, or press Ctrl+minus and pick Entire row. Every empty row disappears and the rows below shift up to close the gap.

One warning about scope. Go To Special deletes the entire row for any cell it flagged. If a row is empty in the columns you selected but holds a value in a column you left out, that row still gets deleted. Select the full width of your data before you run it. And if the dialog reports that no cells were found, the "blank" cells are not truly empty — they hold a space, a non-printing character, or a formula returning "". Clear those first with Find & Replace.

Filter for blanks when your rows are a table

When your data is formatted as an Excel table, a filter is the safer route because it isolates the blank rows before you touch anything.

Click any cell in the table, then use the filter arrow at the top of a column that should always contain a value — an ID or an email column works well. In the AutoFilter menu, clear (Select All), scroll to the bottom of the list, and check (Blanks). Microsoft describes this precisely: clear (Select All), "and then at the bottom of the list of values, select (Blanks)" [2]. The (Blanks) option only appears when the column actually contains an empty cell, which is a useful check in itself.

The sheet now shows only the blank rows. Select those visible rows and press Ctrl+minus to remove them from the table [2]. Turn the filter off and the remaining rows sit in their original order with the gaps gone. If you delete a row you meant to keep, Ctrl+Z restores it immediately.

The filter method wins over Go To Special when you want to eyeball the empty rows before committing, or when only some columns are blank and you need to confirm the whole row is junk rather than a partially filled record.

Sorting pushes blank rows to the bottom

Sorting is the quick-and-dirty option: it drags every empty row to the end of the range, where you can select and delete them in one block.

Select your range, open Data > Sort, and sort by the column most likely to be populated. Blank cells always sort to the bottom, so all the empty rows collect below your real data. Scroll down, select the block of now-contiguous blank rows by their row numbers, right-click, and choose Delete.

Use this method with care. Sorting rearranges every row, and once you have sorted, the original order is gone unless you added an index column first. If the sequence of your rows carries meaning — a timeline, a manual ranking, an export whose order matches another system — add a column of 1, 2, 3… before you sort so you can sort back afterward. This is the same discipline that keeps duplicate removal in Excel from scrambling records you need to trace later.

Power Query removes blank rows on every refresh

Power Query is the option to reach for when the blank rows come back every time you re-import the file, because it records the cleanup as a repeatable step.

Select your range, go to Data > From Table/Range to load it into the Power Query Editor. On the Home tab, open Remove Rows > Remove Blank Rows. Microsoft's Power Query guide gives the command verbatim: "Select Home > Remove Rows > Remove Blank Rows" [3]. Click Close & Load and the cleaned table lands on a new sheet.

The advantage is durability. Power Query saves that step as part of the query, so the next time the source file updates and you hit Refresh, it strips the blank rows again with no manual work. For a report you rebuild weekly from the same messy export, this pays for the extra setup on the second run. The same guide also covers filtering out null or blank values through the AutoFilter's "Remove empty" option when you only want to clean a single column [3].

Why Ctrl+End still lands past your last row

You run Go To Special, the gaps close, and the sheet looks right — then Ctrl+End jumps to row 40,000, the vertical scrollbar is a sliver, and the file is still several megabytes. Nothing went wrong with the deletion. Excel is reporting a different thing than you are looking at.

The reason is what gets stored. Microsoft's documentation is precise about it: "Excel stores only the part of each worksheet that contains data or formatting," and "empty cells may contain formatting that causes the last cell in a row or column to fall outside of the range of cells that contains data" [5]. That is the whole mechanism. A row you emptied is not the same as a row Excel has stopped tracking — if anything was ever applied to those cells, a fill colour, a border, a number format inherited from a whole-column selection, the sheet still counts them.

The fix is a delete-and-save, not a clear alone. Click the row heading below your last row of real data, press Ctrl+Shift+End to extend the selection to the far corner, and on the Home tab use Clear > Clear All. Do the same for the columns to the right. Then save the workbook and close it — Microsoft lists the save as its own step, and it is the one people skip. The last cell is recalculated when the file is written, so Ctrl+End keeps reporting the old corner until you save and reopen [5].

This is worth knowing before you blame the blank-row method you used. Go To Special and the filter both delete rows properly; neither one resets the used range, because that is a property of the saved file rather than of the delete. It is also the clearest argument for not taking a CSV through Excel at all when the file is only passing through. A CSV has no formatting layer, so there is nothing that can linger invisibly in an empty row — the blanks in the text are the only blanks there are, and stripping them in the browser leaves no phantom range behind to reset.

Remove blank rows in Google Sheets

Google Sheets has no Go To Special, but its filter clears blank rows in a few clicks — worth knowing if your file lives in a browser tab rather than a desktop app.

Select your data, then click Data > Create a filter. Open the filter menu on a column that should always be filled, and uncheck the value shown for empty cells so only populated rows remain visible. Google's help states the mechanism directly: "To hide data points, uncheck the box next to the data point" [4]. Select the visible blank rows by their row numbers, right-click, and choose Delete rows. Finish with Data > Remove filter.

If you would rather not delete anything in place, the FILTER function returns a clean copy in a new range and leaves the original untouched — handy when you want the source data preserved. The same is true of UNIQUE when the rows you want gone are repeats rather than blanks; deleting duplicate rows in Sheets walks through when to use the destructive menu command and when to let a formula write a clean copy instead. Either way, the browser approach means the sheet never has to touch a desktop install, which matters when the data is personal.

Strip blank rows from a CSV without opening Excel

If the file you are cleaning is a CSV headed for another system, opening Excel at all is often the wrong move — it can mangle leading zeros, dates, and long numbers on the way in and out.

Blank rows in a raw export are usually a symptom, not the whole problem: a CSV that has empty rows tends to have trailing commas, stray whitespace, and duplicate records too. A CSV file is plain text, so you can clean it as text without ever loading it into a spreadsheet grid. Sigmera's browser-based cleaning tools run entirely on your device — the file is read in the tab and never uploaded to a server, which keeps names, emails, and phone numbers off the wire and makes the cleanup GDPR-safe by default.

The practical order for an import-bound file is: drop the empty and duplicate rows, standardize the fields the importer validates, then export. You can remove duplicate rows in the browser, replace text in a single CSV column without letting the swap loose on the whole sheet, and — because CSV is the format most importers expect — skip the Excel round trip that would have reintroduced formatting damage. Our walkthrough on cleaning a CSV before a CRM import covers the sequence that causes the least rework, and every tool stays free to preview; only exporting a finished file counts against a plan, which you can check on the pricing page.

There is also a quick way to confirm the blanks are gone without scrolling the file: turn the CSV into a chart and check the row counts per category. Empty rows that survived a filter show up as a gap or a stray bucket long before you would spot them in the grid. And if the cleaned file has to go back to someone who works in a workbook, a CSV to Excel converter writes the .xlsx directly, so the rows you removed stay removed and the columns stay as typed.

Use Go To Special or a filter when the data belongs in Excel and stays there. Use the browser route when the file is really a CSV in transit — which, for most people fighting blank rows in an export, is exactly what it is. If blank rows turn out to be the first of several problems in the same export, a csv cleaner that runs in the browser puts each of those passes on its own page, so you can work through them in order without uploading the file once.