How to Remove Blank Rows in Excel
(3 Easy Methods)

Choosing the right method to remove blank rows in Excel is crucial – a wrong move can lead to lost data.

Method 1: Remove all rows that contain only blank cells

Method 2: Remove all rows that contain one or more blank cells

Method 3: Remove a few rows (quick method)

Method 1:
Remove empty rows that contain only blank cells

Using this method, a row is only considered blank if there are only empty cells in the row.

Only one blank row to remove - example

As you can see in this data set, only one row is completely blank, while the other two highlighted rows are partially blank.

This method removes the entire row 6 – but not row number 2 and 4.

Step 1) Select columns A, B, and C by dragging the cursor from A to C while holding down the left mouse button.

Step 2) Go to the Data tab and click the Filter button.

Click the Filter button from the data tab

Now, you can see the filter buttons next to each of the column headers.

Step 3) Click the Filter button in column A. Then clear all the checkmarks by unchecking the (Select All) checkbox.

Set filtered rows to blank rows only

Step 4) Put a checkmark in the (Blanks) checkbox at the bottom and click OK.

Set filtered rows to blank rows only

Now, only rows that have empty cells in column A are visible.

Two empty rows and three partially blank rows visible from autofilter

Step 5) Repeat steps 3 and 4 for columns B and C.

This leaves only rows that are 100% empty.

Your file should look like this by now:

How your data set should look after hiding blank rows with autofilter

Step 6) Now select all the blank rows. Drag the cursor from the top visible row to the bottom visible row while holding down the left mouse button.

Step 7) In the Home tab, click the drop-down arrow below the Delete button and select Delete Sheet Rows.

Delete blank rows that are visible after filtering

Step 8) In the Data tab, remove the filter by clicking the Filter button again.

From data tab - remove filter to show full data set with no blank rows

And your blank rows are gone! Well done🏆

Before deletingAfter deleting

Note that you deleted all rows that contained only blank cells.

The rows that are partially empty are still visible👀

If you want to remove them too, check out method 2 below.

Method 2:
Remove rows that contain one or more blank cells

This method deletes rows in your data set if they contain one or more blank cells.

Visual example of the rows that will be removed

As you can see, all three highlighted rows are considered blanks, as they all contain at least one empty cell.

Step 1) In the Home tab, click the Find & Select button on the right side of the Ribbon.

Click Find and Select from Home tab

Step 2) Click Go To Special.

Click Go To Special to select all the blank cells in the data

Step 3) Select Blanks and click OK.

This selects only the blank cells in your data.

Select blanks in go to special dialog box

Step 4) From the Home tab, click the arrow below the Delete button and choose Delete Sheet Rows.

Delete Sheet Rows after selecting the blank rows

And that’s how to remove rows that contain one or more blank cells.

Well done🙌

Before deletingAfter deleting

Protip: Skip steps 1 and 2 by using the shortcut F5 to bring up the Go To dialog box and then click the Special button.

Kasper Langmann, Co-founder, Spreadsheeto

Method 3: Remove a few blank rows individually

If you need to get rid of a small number of blank rows, you can do it manually – one blank row at a time.

Right-click on the single row number for the row you want to delete.

Select Delete.

Right click to delete blank rows individually

Then it’s gone!

Pretty easy right?

That way, you can assess whether the row is to be considered a blank row or not.

Obviously, this method takes way too long in big spreadsheets⏳

BONUS VIDEO:
How to delete blank rows

Want to see it in action? I’ve got your back👍

Here’s my video walkthrough of both method 1 and method 2.

You may also be interested in

Author

Written by Kasper Langmann, co-founder of Spreadsheeto and a certified Microsoft Office Specialist.

This tutorial reflects over 40 hours of dedicated research and writing, based on my 10+ years of professional Excel experience.

Last updated on February 12th, 2024.

One last thing before you go: make sure to sign up for my free Excel course!