How to Remove Blank Rows in Excel: 5 Methods That Work

The fastest way to remove blank rows in Excel is Home > Find & Select > Go To Special > Blanks, then Delete > Entire Row. It takes five seconds, and it fails in two opposite directions. Rows that look empty but contain a space, a line break or a formula returning "" survive the cleanup and keep breaking your table. Meanwhile, real records with just one empty cell can get deleted along with the true blanks. In an order file, one deleted order means one missed shipment and one invoice that never goes out.

Here are five methods, when each one fits, how to find rows that only look blank, and a walkthrough of cleaning a customer's order export without losing a single order.

Before you delete anything

Save a copy of the original. Bulk deletions can outrun the undo history, and once saved they can't be reversed.

Insert an index column at the far left and fill it 1, 2, 3 to the bottom. Whatever you sort or filter later, sorting by this column restores the original order.

Press Ctrl + End. Excel jumps to the last cell of the used range, which the XlCellType reference on Microsoft Learn describes as "the last cell in the used range". If that's far below or to the right of your data, there are rows or columns holding only formatting or stray spaces. Delete them first.

Method 1: Go To Special, then delete entire rows

Fast, but only safe on a single key column.

1. Select the data range of a column that is always filled on real rows, such as Order ID.
2. Go to Home > Find & Select > Go To Special, choose Blanks and click OK. (F5, then Special, opens the same dialog.)
3. Right-click one of the selected cells and choose Delete > Entire Row, or press Ctrl + -.

If you select the whole sheet instead of one column, every row with any empty cell is selected, including an order that's only missing a customer name. That is the source of most "Excel deleted my data" stories.

Pick the key column carefully. Notes or delivery-instruction columns are often empty on real orders; system-generated columns like order ID or SKU are the safe choice. In an Excel table the menu reads Delete > Table Rows, and it works the same way.

Method 2: A COUNTA helper column and a filter

This method counts what's in each row. In an empty column to the right, enter =COUNTA(A2:H2) and fill down. Each row shows how many of its cells contain something. Filter the helper column to show 0, select the visible row numbers, delete the rows, then clear the filter. When rows are filtered, Excel deletes only the visible ones, so the data rows are untouched.

The bonus is visibility. A row that's missing one field shows 7 instead of 8, so you can review partial records before anything is deleted, instead of discovering them gone afterwards.

Method 3: Sort the blanks to the bottom

Sorting by the key column moves every blank row to the bottom of the range, where you can select and delete them in one block. Then sort by the index column to restore the original order. Without the index column, you lose the sequence the orders arrived in.

One catch: if you click a single cell and sort, Excel treats the first blank row as the end of the table and sorts only what's above it. With blanks scattered through the data, select the full range from the header to the last row before choosing Data > Sort.

Method 4: FILTER into a clean table

In Microsoft 365 and Excel 2021 or later, you can leave the source alone and build a blank-free copy with a formula. On a new sheet, =FILTER(Orders!A2:H500, Orders!A2:A500<>"") spills only the rows that have an order ID. Add conditions to keep rows where either of two columns has a value: =FILTER(Orders!A2:H500, (Orders!A2:A500<>"")+(Orders!C2:C500<>"")). Adding the bracketed conditions works as OR.

The result updates when the source changes, which suits a file you receive in the same layout every day. Before sending it to anyone on Excel 2016 or 2019, copy and paste it as values; those versions don't have FILTER and show #NAME?.

Method 5: Power Query's Remove Blank Rows

For a file that arrives daily in the same format, Power Query does the job once and repeats it forever. Select the data, choose Data > From Table/Range, then in the Power Query Editor choose Home > Remove Rows > Remove Blank Rows, and Close & Load. Tomorrow, replace the source file and click Refresh.

To process a folder of files at once, use Data > Get Data > From File > From Folder. The files are combined into one table with a column showing which file each row came from. Power Query treats a cell containing a space as not empty, so clean the fake blanks below first.

👉 Remove Duplicates in Excel: 4 Ways and Which Columns to Pick

Rows that look blank but aren't

When a row looks empty and survives every method above, something invisible is in it. Three culprits cover nearly every case:

• Formulas returning empty text. A cell with =IF(A2="","",B2) displays nothing but isn't empty. The COUNTA reference states that empty text ("") counts as a value, and Go To Special > Blanks skips such cells too.
• Spaces and line breaks. Data copied from web pages, PDFs or email often carries a stray space or line feed.
• Non-breaking spaces, CHAR(160). Common in anything copied from a web page. According to the TRIM reference, TRIM removes the normal space (32) but not the non-breaking space (160). The CLEAN reference says CLEAN removes the first 32 non-printing characters (0 to 31).

Combine the three in a helper column. This joins every cell in the row, strips the non-breaking spaces, control characters and ordinary spaces, and leaves nothing if the row is truly empty:

=TRIM(CLEAN(SUBSTITUTE(A2&B2&C2&D2&E2&F2&G2&H2, CHAR(160), "")))

Next to it, =IF(J2="","Blank Row","Has Data") labels each row. Filter for Blank Row, delete, and the rows that contained only spaces or "" are gone too.

Why blank rows show up, and how to stop them

Blank rows are rarely typed. They come from exports that insert separator and subtotal rows between branches or dates, from tables copied out of web pages and email, and from formula-built reports pasted as values. Ask the customer or system owner whether a "raw data" or CSV export without subtotals is available; it often is. For your own entry sheets, format the range as a table (Insert > Table), which keeps rows contiguous and makes any gap obvious.

Walkthrough: cleaning a customer's daily order export

An order-desk coordinator at Crestview Foods downloads the daily order file from Brightway Retail's supplier portal. It runs to about 400 rows, with a blank row between store regions and a few rows that contain only spaces.

1. Copy and index. Save the original, insert an index column.
2. Check the range. Ctrl + End lands on row 1,048. Six hundred rows below the data hold only formatting; delete them and save.
3. Flag fake blanks. Add the TRIM/CLEAN helper and the label column. It finds 23 blank rows, five of which contained only spaces.
4. Separate partial rows. Two rows have no order ID but do have a SKU and quantity. They are highlighted, not deleted, and the coordinator emails Brightway's buyer to confirm the order numbers.
5. Delete. Filter for Blank Row, delete, clear the filter and helper columns.
6. Reconcile. The quantity total matches the original, and the row count dropped by exactly 23.

The order matters: deciding what to delete and what to check before deleting anything is what prevents the call two days later about an order that never shipped. Done by hand, this takes 10 to 30 minutes per file. With five customers sending files daily, that's close to an hour a day.

Common mistakes

• Choosing "Shift cells up" instead of "Entire row". Only that column moves up, so order IDs end up beside the wrong SKUs. Undo immediately and delete entire rows.
• Deleting manually hidden rows by accident. Filtered-out rows are protected from deletion, but rows hidden with Hide are deleted along with the selected range. Unhide everything first.
• PivotTable still shows (blank). Its source range still points at the old range. Use PivotTable Analyze > Change Data Source to reset it.
• #REF! appears elsewhere. Formulas on other sheets pointed at the deleted rows. Use Formulas > Trace Dependents before deleting.

Frequently asked questions (FAQ)

Q. Can I remove blank columns the same way?

Yes. Select the header row, Go To Special > Blanks, then Delete > Entire Column. Confirm with COUNTA that a column without a header is actually empty first.

Q. How do I put rows back in the original order?

Sort by the index column you added before deleting. Without one, the quickest route is to start again from the saved original.

Q. Is there a keyboard shortcut to delete blank rows?

There's no single shortcut, but F5 > Special > Blanks > Enter, then Ctrl + - and Entire Row, gets close.

Q. I want to fill blank cells, not delete rows.

Select the blanks with Go To Special, type = followed by the address of the cell above (for example =A2), and press Ctrl + Enter. Each blank takes the value from the cell above it.

How inline AI cleans incoming order files

For order desks that clean customer files every day, inline AI opens the file, removes true blank rows including those holding only spaces or empty text, flags partial records for review, and shows the totals before and after. With IDP connected, the downloading and merging of channel files is done by AI before anyone opens a spreadsheet.