How to Remove Blank Rows in Excel

Why should you remove blank rows in Excel?

Blank rows hint at incomplete or missing data. A blank row or even a blank cell in the wrong place can make Excel misunderstand data ranges and cause errors when a function is applied. Also, blank rows can be a visual annoyance. 

Blank rows are not always bad, but it is easy to remove them. All Excel beginners should learn a few methods to remove blank rows in their spreadsheets. 

---

Download your free practice data set to try out what you'll learn in this tutorial:

---

---

How to remove blank rows in Excel manually

When there are a small number of rows, you can quickly spot the blank rows and remove them with a selection.

1. Highlight the entire row by selecting the row number on the left side of the screen. You can select multiple rows by pressing Ctrl and clicking on the row number.

2. Right-click anywhere on this row and select Delete. The empty rows will disappear. Those rows below the deleted ones will be moved up to replace the deleted space. The row numbers will also update to stay in sequence. 

Use a simple sort to find and remove blank rows in Excel

Sorting your data will not only help to organize your data but this can also bring out blank rows you do not need. A simple sort will bunch all blank rows together and help you quickly remove them. 

Select the range of rows in the spreadsheet.

1. Go to the Data tab.
2. Go to the Sort & Filter group. Here you can select either the Sort A to Z or Sort Z to A button to sort in ascending or descending order. Blank rows will be sorted at the bottom of the selected range (indicated by colored rows in the screenshot below).

3. To get back the original order, you can add an index column before you sort the data. Re-sort the data according to this index column and then delete it. 

Use the Find command to search and remove blank rows in Excel

Using the simple delete command is enough when you just have a few blank rows in your spreadsheet. But it gets cumbersome if you have to scroll around to find and then select multiple blank rows. 

The Find and Replace command is under the Find & Select button on the Home tab. Use it to find all empty rows in a sheet or the entire workbook and then delete them in one go. You can also use the Ctrl + F keyboard shortcut to bring up the Find and Replace dialog box.

---

Note: Avoid this method on sheets where only a few cells are empty instead of entire rows. You wouldn’t want to delete an entire row and lose those cells with data. 

---

1. Open the dialog in the Find tab. Keep the Find what field blank.
2. Select the Options button and check the box for Match entire cell contents.
3. For this example, we use these options to limit the search: within the Sheet, search By Rows, and look in the Values.
4. Click the Find All button to get all the blank cells.
5. All blank rows are selected. Right-click on any selected row and choose Delete from the menu.

Use Excel’s Find & Select command to remove blank rows quickly

The Find & Select command is just under the Find and Replace commands. It also works in a similar way. 

Use the Find & Select command to quickly select all blank rows and remove them in one click. 

---

Note: Again, avoid this method on sheets where only a few cells are empty instead of entire rows. 

---

1. Select the range of rows and columns on the spreadsheet that have all the blank cells. You can select the entire range of data cells on your sheet too. In our example, we will select the data range that extends from Column A to Column F. Blank cells are shown in yellow for easier readability. 
2. Go to Home > (Editing Group) Find & Select > Go To Special.

3. In the Go to Special dialog, select the radio button for Blanks. Click on OK.

4. All blank rows in the selected range are highlighted. Right click anywhere on these rows and select Delete from the context menu. Excel will display four options in a tiny dialog to decide how to handle the empty spaces after deleting the blank rows.

5. In our example, select the Entire row as the whole row is blank. If there is some data in the adjoining cells, you can opt for Shift cells up to make the non-empty rows move up into the vacant space. But you should only shift cells if all the data line up as they are meant to be. 

---

Tip: That’s why a more complex spreadsheet will need more advanced methods of selecting and removing blank rows. 

---

Use a simple filter to remove blank rows in Excel 

All of the above methods will help you manage simple blank rows where all cells are empty. But real-world Excel sheets can have rows where only some rows are blank while others have useful data (Row 8 and Row 11). 

In those cases, the above methods won’t be able to differentiate between the two kinds of rows and will also remove the rows with some blanks (and data). You have to be more selective about the rows you remove with the help of a filter. 

In the screenshot below, you only want to remove Row 4 and Row 6, while Row 8 and Row 11 will have to be preserved. 

1. Select all columns that hold your data range.
2. Go to Ribbon > Data tab > Sort & Filter Group > Filter.
3. Move across the columns. Click the Filter dropdown for each column. Uncheck Select All then check Blanks for rows that have only some blank cells.

4. Go across the other columns and repeat the filter for blanks in the other columns to selectively find those entire blank rows.
5. Select the filtered rows. Go to the Home tab and click Delete > Delete Sheet Rows. Alternatively, select and right-click on the rows which are completely blank. Then, choose Delete from the context menu as before. 

6. Go back to the Data tab and switch off the Filter. 

Use an advanced filter to remove blank rows in Excel 

In Excel, you can use an advanced filter with a criterion or a condition that matches all values against it and extracts a subset of the entire data. In this example, it will extract all rows which do not have any rows with the blank cells. 

The advanced filter will extract the same data to another part of the sheet minus the blank cells. This method requires some initial setup. But it can also extract the specific data you want from a large table.

In the screen below, we have a data range in cells A1 to D10. We are going to use the Advanced Filter to extract a subset of this range without the blank cells in an extract area H1 to K10. A criterion area is also set up at F1. You have to give the filter a criterion and it should include the column header it will compare against (Salary).

1. Type “<>” (Less than...greater than) in the criterion cell F2.
2. Go to Data > Sort & Filter > Advanced Filter. 
3. Select Copy to another location. Enter the List range of your data and range of cells where you want to extract the results to. 

In this example: 

• The List range is $A$1:$D$10. 
• The Extract range where we will copy the cells to is $H$1:$K$1. 
• The Criterion range is $F$1:$F$2.  

The extracted data on the right does not contain blank cells. 

To extract rows that have blank cells, you can use the equal sign. Type it as =”=” as the criterion. As you can see, we´ve extracted records for Carl Johnston and Chester Lloyd from the table. 

Other ways to remove blank rows in Excel

We hope this resource helped you find your own go-to method to quickly locate and remove blank cells from a spreadsheet. As a beginner, you can rely on these simpler ways to handle blank rows.

When you graduate to Excel power user status, there are other means to solve blank row problems, using add-ins or advanced built-in tools like Power Query.

Get certified!

No matter where you are on the Excel skill continuum, you can always learn more!

Take your skills to the next level with the Excel Basic and Advanced course today.

Level up your Excel skills

Become a certified Excel ninja with GoSkills bite-sized courses

Start free trial