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]. If that weekly export arrives as a stack of monthly CSVs, the same editor can combine a folder of CSV files into one table first, so a single Remove Blank Rows step covers every file. And if the weekly report goes to people who only read it, a CSV to PDF converter turns the cleaned export into numbered A4 pages, switching to landscape once the table is wider than five columns.

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.

When deleting blank rows breaks your formulas

The other thing that goes wrong after a clean delete is downstream. The gaps close, the data looks right, and a column of totals somewhere else on the sheet now reads #REF! in every cell.

The cause is the shape of the formula, not the deletion. Microsoft's guidance on the error is direct about it: when a formula references each cell individually, separated by a comma, "and delete a referenced row or column, Excel can't resolve it, so it returns the #REF! error" [6]. A formula written as =SUM(B2,B3,B4,B5) names four specific cells; delete the row holding B4 and there is no longer a cell for that argument to mean. A formula written as =SUM(B2:B5) names a region instead, and Excel simply shrinks the region when a row inside it disappears.

That difference decides whether a blank-row cleanup is safe. Before deleting, click a cell in the block you are about to remove and use Formulas > Trace Dependents to see what reads it. If the arrows point somewhere, look at how those formulas are written. Range references survive; comma-separated cell lists do not.

If it has already happened, undo it rather than repairing it — Microsoft's first remedy is exactly that: if you accidentally deleted rows or columns, select Undo on the Quick Access Toolbar or press Ctrl+Z to restore them [6]. Chasing #REF! values cell by cell afterwards is slower and loses the original references. And when the formulas cannot be rewritten, do not delete in place at all: filter the blanks out of view, or copy the populated rows to a fresh sheet and leave the original as it stands.

None of this applies to a CSV, which is the quiet argument for cleaning the file before it ever becomes a workbook. Plain text holds no formulas and no references, so there is nothing for a removed row to break — the rows below simply move up, and the file that lands in the importer is the file you looked at.

Remove only fully blank rows with a helper column

Go To Special and a single-column filter both have the same blind spot: they decide a row is blank by looking at one cell, or at any empty cell in the selection. On an export where some records are missing a phone number or a company name, that rule deletes real rows along with the empty ones. When you want to remove only the rows where every column is empty, count the filled cells per row first.

Add a column to the right of your data and enter =COUNTA(A2:F2) in its first row, widening the range to cover every column of the record, then fill it down. Microsoft defines what COUNTA measures plainly: it counts "cells containing any type of information, including error values and empty text", and it "does not count empty cells" [7]. A result of 0 therefore means nothing in that row holds a value. Filter the helper column for 0, select the visible rows, press Ctrl+minus, then clear the filter and delete the helper column. Rows that are missing a single field have a count of 1 or more and survive.

One detail cuts the other way. Because COUNTA counts empty text, a row full of formulas that return "" scores above 0 and is kept, even though it looks blank. That is usually what you want — those rows are wired to something — but if they are leftovers, paste the range as values first so the empty strings become truly empty cells.

If you would rather not delete anything at all, Excel for Microsoft 365, Excel 2021, and Excel 2024 can build the clean copy with a formula. FILTER returns the rows of a range that meet a condition, with the syntax =FILTER(array,include,[if_empty]) [8]. Point the include argument at the helper column — =FILTER(A2:F500, G2:G500>0, "No data") — and the result spills into a new range with every fully blank row left out, while the source stays exactly as it was. Keep the third argument: Microsoft notes that when nothing matches and if_empty is omitted, "a #CALC! error will result" [8]. Because the output recalculates, rows that later fill in or empty out are picked up automatically, which makes this the formula-side counterpart to the Power Query step above.

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 — in a workbook, Excel's Replace dialog and its lookup-table alternative handle that step — 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. If the blanks turned out to be one symptom among several, the full Excel data cleaning order sets out which pass to run first and why deduplicating before you normalize the matching fields leaves duplicates behind.