Excel Remove Blank Rows: Every Method Step by Step

Blank rows are the silent killer of clean spreadsheets. They break sorting, throw off pivot tables, and confuse formulas like SUMIF or COUNTA. If you've ever opened a file from a colleague and found gaps every few rows, you already know the pain.

The good news? Excel gives you several built-in ways to wipe them out, and you don't need to be a power user to pick the right one. This guide walks through every reliable method, from the classic Find & Select shortcut to Power Query and a tiny VBA macro.

You'll learn which approach fits small datasets, which one survives 100,000 rows, and which trick to avoid because it can quietly delete data you wanted to keep. Before we dig in, a quick warning. "Blank" in Excel is fuzzier than it looks.

A cell that displays nothing might still hold a formula returning "", a single space, or a non-printing character. Those cells are not technically empty, and Go To Special will skip them. We'll cover how to spot the difference further down.

Why does this matter so much? Because clean rows are the foundation of every report, dashboard, and pivot table downstream. A single rogue blank row in your source data can quietly cap a pivot at half its rows, or break a chart series, or silently drop a category from a summary.

Hours get burned hunting for the missing number, when the real fix is removing one empty row at the top of the source. Get the cleanup right once, and the rest of your work just behaves.

Each method below shows a complete workflow, but the screenshots in your head matter less than the order of clicks. If you're new to Excel, start with the Filter tab because it lets you see what's about to vanish.

If you're cleaning the same export file every week, jump straight to Power Query so you only set it up once. The tabs below give you the four most common methods side by side. Skim them, then pick the one that fits your data.

One more decision to make up front: are you cleaning the original file, or a copy? Always work on a copy when the data is shared or live. A renamed duplicate (think report_2026-05-10_clean.xlsx) is your insurance policy.

If something goes sideways, you can re-open the original and try again. This sounds obvious, but it's the single habit that prevents the worst kind of Excel mistake: a deletion you can't undo because you saved and closed the file an hour ago.

Method 1: Find & Select Go To Special. This is the original Excel trick, alive and kicking since Excel 2003. You highlight your data, hit F5, click Special, pick Blanks, and Excel selects every empty cell at once.

Right-click and choose Delete > Entire Row, and the gaps disappear. The whole thing takes about ten seconds on a sheet of 5,000 rows.

The downside is that it deletes any row touching a blank, not just the rows that are completely empty, so partial gaps will cost you data. If you've never used the Go To Special menu before, it's worth opening it once just to see what else is in there.

Method 2: Filter and delete. Filtering is the safest method because nothing leaves your sheet without you confirming. Add a filter (Ctrl+Shift+L), open the dropdown on the first column, and uncheck every value except (Blanks).

Excel hides every other row. Select the visible row numbers, right-click, hit Delete Row, then clear the filter. The trick here is that filter only inspects one column at a time, so you may need to repeat it for each column or combine with the helper method.

For more depth on this approach, check our walkthrough on how to remove blank rows in Excel using filters.

Method 3: Sort the blanks to the bottom. Select your data with the header row, open Data > Sort, and sort by any column ascending.

Blank cells cluster at the bottom (or top, depending on Excel's mood). Highlight that block of empty rows, right-click, delete. Done.

The huge caveat: sorting changes the order of your data forever. If row order matters because you're tracking a sequence, save a backup first or skip this method entirely.

A safer twist: add a sequential ID column first, sort, delete blanks, then re-sort by the ID column to restore the original order. It adds about thirty seconds and saves you from a panicked Ctrl+Z later.

Method 4: Helper column with COUNTA. This is the safest method for big sheets. Add a new column at the end of your data and type =COUNTA(A2:Z2), then drag the formula down.

The helper returns the number of non-empty cells in each row. Filter the helper column to show only zeros, select those visible rows, delete them, then remove the helper column.

Because COUNTA looks across the whole row, you only delete rows that are truly empty. It's the right tool when your data has optional fields. For a different angle on the same approach, our guide on how to delete blank rows in Excel walks through helper columns with screenshots.

Method 5: Power Query. Power Query lives under Data > Get Data > From Table/Range. Once your data loads into the Power Query editor, click Home > Remove Rows > Remove Blank Rows and hit Close & Load.

The cleaned table lands back on a new sheet. Every time the source data changes, you click Refresh and Power Query reapplies the same steps. For repeating reports, this is the gold standard.

The only thing to watch is that the destination table gets overwritten on each refresh, so don't paste extra notes into it.

The numbers above are rough but real. VBA wins on raw speed because it operates directly on the cell objects without redrawing the screen. Find & Select runs almost as fast for one-off jobs. Filter is the slowest. Power Query feels slow on the first pass but blows everything else away on the second refresh because the steps are cached.

Method 6: VBA macro. If you do this every day, automate it. Open the VBA editor with Alt+F11, insert a new module, and paste a short Sub like the one in the tab above.

Save the workbook as a macro-enabled file (.xlsm), then add the macro to your Quick Access Toolbar. From now on, you select your data and click one button. The macro finishes in a second or two even on enormous sheets.

The only friction is corporate IT policies that block macros, so check before you commit to this route. If macros are off, you can usually ask IT for an exception on a single workbook signed with a digital certificate.

Now the question that trips up almost everyone: what is a "blank" cell, really? Excel treats four things differently. A truly empty cell returns TRUE for both =ISBLANK(A2) and =A2="".

A cell holding a formula like =IF(B2=0,"",B2) looks empty but contains an empty string. ISBLANK returns FALSE there, even though Go To Special > Blanks will sometimes skip it.

A cell with a single space character is not blank at all. And a cell with a non-breaking space (Alt+0160) imported from a web page is the worst offender because it looks identical to nothing. Knowing the difference saves hours when a delete "doesn't work."

Common pitfalls. Pitfall one is the partial-blank trap from Method 1, which we already covered. Pitfall two: the Sort method changes row order. Once you sort and save, undoing it is painful unless you had a sequential ID column.

Pitfall three: Filter sometimes misses rows hidden by an earlier filter. Always check that no other filter is active before you delete.

Pitfall four: helper columns with COUNTA on sheets larger than 100,000 rows can chug for a few seconds while they recalculate. Drop the helper column the moment you're done.

Pitfall five: tracked changes or shared workbooks can block deletes silently. If a row won't go away no matter what you do, check Review > Track Changes and turn it off.

Keyboard shortcuts that pay for themselves in a week. Ctrl+A selects the entire current region around the active cell. F5 or Ctrl+G opens the Go To dialog so you can jump to Special > Blanks.

Ctrl+Shift+L toggles the filter on or off. Ctrl+- (minus) deletes the selected rows or columns without needing the right-click menu. Alt+H+F+S+B runs the Filter shortcut from the Home tab.

Memorize three of these and you'll move twice as fast through any cleanup. The shortcuts are universal across Windows versions of Excel from 2010 onwards, so they're worth burning into muscle memory.

Mac differences run deeper than just shortcuts. Power Query exists on Mac as of 2026 but with fewer source connectors. VBA on Mac is functional but the editor is older and clunkier than Windows.

If you're on Mac and your team is on Windows, stick to Power Query so the workbook behaves the same on both sides.

Excel for the Web is the awkward middle child. It supports basic find and filter, so Methods 1 and 2 work fine in a browser. Power Query is unavailable in the browser version, and macros don't run at all.

If you need a fully cleaned dataset and your only access is the web app, do the cleanup with the Filter or Helper Column method, then download a copy. Microsoft has been promising browser Power Query for years, but as of 2026 it still hasn't shipped.

Mobile Excel for phones is even more limited. The iPad and Android apps support basic editing and Find & Replace, but Go To Special is missing entirely, and there's no Power Query button. For any serious cleanup, treat mobile as read-only and do the work on a desktop.

If you find yourself needing the same trick for duplicate values rather than blanks, the same Power Query button sits right next to it (Home > Remove Rows > Remove Duplicates), and our guide to remove duplicates in Excel covers the full workflow.

Stuck on the error "can't find anything to delete"? Excel throws this when Go To Special doesn't find any cells matching your criteria. Nine times out of ten, it means your blank cells aren't actually blank.

They contain formulas returning "", single spaces, or non-breaking spaces from a web copy-paste. Run a quick TRIM on a helper column with =TRIM(A2), then use Find & Replace (Ctrl+H) to replace nothing with nothing.

That collapses invisible characters into actual blanks, after which Go To Special works as expected.

What about removing blank columns instead of rows? Same playbook, different axis. Select your columns, open Go To Special, choose Blanks, then right-click and pick Delete > Entire column.

The helper-column trick becomes a helper-row trick using =COUNTA(A2:A1000) across the bottom of each column. Filter doesn't work on columns the same way, so for a column-wise cleanup, Go To Special and the helper-row formula are your two practical options.

Need more on the same theme? Our reference on Excel delete blank rows covers column variants in the second half.

Prevention beats cleanup. If you control the data import, fix the source. CSV files often have trailing blank lines that Excel reads as empty rows. Open the CSV in a text editor first and trim the bottom.

If you import via Power Query, set up a step to remove blanks during the import so the destination sheet is always clean. If users paste into a template, lock the cells around the entry area so they can't add blank rows by mistake. Five minutes of prevention saves an hour of cleanup down the line.

One last tip: after any large cleanup, save the file under a new name and run a sanity check. Compare row count, totals, and a few spot rows against the backup.

If you spot a problem, you have a clean rollback. Spreadsheets are unforgiving, and a careful save habit is what separates analysts who sleep well from analysts who get pinged on Sunday morning.

Pick the method that fits your data, run it on a copy first, and you'll never fear a messy export again. The methods in this guide cover every Excel version from 2010 to 2026 across Windows, Mac, and the web app.

You've now got six different tools in your kit. The first time you use each one, it'll feel slow. By the third or fourth time, you'll pick the right method for the job in under five seconds. That's the real win.

If you only remember one thing from this guide, let it be this: identify the kind of blank you're dealing with before you delete anything. Fully empty rows, partially empty rows, formula-blank cells, and invisible non-breaking spaces all need slightly different handling. The wrong method on the wrong type of blank will either do nothing or quietly delete your real data.

Test on a small copy, watch what happens, then scale up. That habit alone will save you more time than memorizing every keyboard shortcut combined.

For a different angle on the same problem, our walkthrough on how to remove empty rows in Excel covers screenshots and a few extra examples that complement what you just read.