How To Delete Blank Rows In Excel: Step-by-Step (2022)
Everyone hates empty rows in their data.
Fortunately, there are 2 ways to remove all the blank rows in your spreadsheet – in just a few clicks.
Which method fits you best comes down to this really important distinction:
1) “I want to delete rows that contain 1 or more blank cells”
2) “I want to delete rows if they only contain blank cells”
Let’s dive in🤿
Oh, and click here to download the Excel file I’m using in this guide.
How to delete blank rows if 1 or more cells are blank
This method deletes rows in your data if they contain 1 or more blank cells.
That means rows like the 3 ones shown in the screenshot would be considered as blanks, and then removed.
If that’s the kind of empty rows you want to remove, follow these steps:
1. In the Home tab, click the ‘Find & Select’ button on the right side of the Ribbon, so you can start to find blank rows.
2. Select ‘Go To Special’.
3. Select Blanks and click OK.
This select only the blank cells in your data.
Now, you need to delete entire rows instead of just the selected cells.
4. From the Home tab, click the arrow below the Delete button and choose ‘Delete Sheet Rows’.
And that’s how to delete blank rows in Excel.
Well…
If blank rows = rows that contain 1 or more blank cells⚠️
This is quite important, as you can accidentally remove empty rows that shouldn’t have been removed if you just blindly use this method.
If you mean blank rows must be completely empty before they should be deleted, use the method below instead.
PRO TIP: Shortcuts
You can use these shortcuts to open the ‘Go To Special’ dialog box instead of the method above:
- Press F5 (for Windows and Mac)
- Press Ctrl + G and click the ‘Special’ button (for Windows and Mac)
And continue with the remaining steps from above to remove blank rows.
How to delete blank rows if all cells are blank
This is what I’d call the proper way to remove blank rows. Although it’s a bit more cumbersome.
It’s more restrictive, meaning that it generally will remove less rows than the method above✂️
This way, a row will only be considered to be blank there are only empty cells in the entire row (within the columns of the data set).
In the screenshot, only 1 row is completely blank, while the other 3 highlighted rows are partially blank.
Now, to remove completely blank rows with this method, follow these steps:
1. Select the relevant columns (A, B, and C).
Make sure you select the entire columns. If you don’t, you won’t filter the entire dataset and you won’t be successful when you remove blank rows in a few seconds.
2. Go to the Data tab and click the Filter button.
And you can see the filter buttons next to each of the column headers.
3. Click the first filter button and make sure there’s only a checkmark in the Blanks checkbox.
You do that by unchecking the ‘Select All’ option before putting the checkmark in Blanks.
Now, only rows that have empty cells in column A are visible.
If you used method 1, row 2 would be considered a blank row and would be deleted🤔
4. Repeat step 3 for all other columns in the data set.
In this case, it’s only column B and C.
So, for each data column, go into the filter, uncheck ‘Select all’ and make sure there’s a checkmark in Blanks.
This leaves only the real empty rows.
5. Select all the blank rows.
6. Click the drop-down arrow below the ‘Delete’ button in the Home tab, and select ‘Delete Sheet Rows’.
And that’s how to remove blank rows in Excel -> the proper way.
7. Remove the filter by clicking the Filter button.
And your blank rows are gone!
Keep in mind the rows partially empty rows are still visible.
Pretty cool, huh?😎
Video guide: Delete blank rows
Want to watch how to remove blank rows on video instead of reading?
Then see my Youtube video and learn everything you need to know about this proper way to delete blank rows.
Delete 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.
Just right-click on the single row number for the row you want to delete.
And select Delete.
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⏳
That’s it – Now what?
That’s how to delete blank rows in Excel.
Both ways are fine for deleting blank rows. One is quick and one is thorough.
But be aware that with the quick method (first method), you might remove blank rows that shouldn’t be deleted.
So, while the quick for deleting blank rows works in some scenarios, I definitely recommend learning the “hard” way.
The reason why you’re deleting blank rows in the first place is to ensure there are no gaps in your data.
This helps with calculations!
But you know what also helps with calculations?
Learning the absolute best functions Microsoft Excel has to offer.
Click here to read more about my free training that teaches you IF, SUMIF, VLOOKUP, and pivot tables.
It’ll make you an Excel wizard in just 30 minutes.
Other resources
The filter can do more than help to remove blank rows in Excel. Read all about it here.
Getting rid of blank rows is a part of data cleaning. Other tools that help you clean your data are: Find and replace, text functions, and splitting data.