How to Remove Blank Rows in Excel the Easy Way

Remove Blank Rows Manually

Empty cells and entire blank rows in Microsoft Excel cause errors. Even if they didn’t, blank rows don’t make for consistent data in your spreadsheets. So let’s learn how to remove blank rows in Excel with the help of the different methods Excel gives you.

If there are a small number of rows, the manual way to remove rows is the quickest.

1. Select a row by clicking on the row number on the left side of the screen. To select multiple rows, press Ctrl and click on the row number.
2. Right-click anywhere in the selected row and select Delete. The empty rows will disappear and the rows below the deleted ones will move up.

This method is the simplest way to remove rows and columns that aren’t blank.

The Quick Way To Remove Blank Rows In Excel

The Delete command may be the simplest but it is not the quickest when you have several empty rows in your spreadsheet. In this case, you should use the more targeted Find & Select command on the Ribbon.

Select the range of rows and columns that have the blank cells you would like to remove. In our example, we will select the data range that extends from Column A to Column G.

1. Go to Home > (Editing Group) Find & Select > Go To Special…
2. From the options available, select the radio button for Blanks. Click on OK
3. All the blank rows in your spreadsheet get selected. Right click anywhere on these rows and select Delete like before. Excel will again display four options in a tiny dialog.
4. In this case, as there is no cell data next to your selected range, you can select Entire row. If there is some data in the adjoining cells, opt for Shift cells up as you want the non-empty rows to move up.

Remove Blank Rows With Find And Replace

Find and Replace is a lot like the Go To Special command. As the name says, it will find all instances of cells without any value and give you a quick way to select and remove them.

Press Ctrl + F to bring up the Find and Replace dialog box. You can also go to Find & Select > Find… to open it.

1. In the Find dialog, stay on the Find
2. Keep the Find what field blank.
3. Select the Match entire cell contents
4. Search Within the Sheet. Search by Rows. Look in the Values.
5. Click the Find All button to get all the blank cells.

With the blank rows are all selected, you can now use the same Delete method to remove them.

Remove Blank Rows In Excel With A Filter

It’s easy to remove blank rows in Excel with the above methods when the entire row does not carry any data. But in the real world, there are instances when few cells in the rows will be blank while others may have data.

You have to use a Filter to remove the rows with all blank cells only but preserve the rows which have a mix of data and blank cells. It is easy enough but takes a few steps more.

1. Select all columns that hold your data range.
2. Go to Ribbon > Data tab > Sort & Filter Group > Filter.
3. Start filtering for complete blank rows from columns that only have a few blank cells. Click the filter arrow in column B, uncheck (Select All), and select (Blanks) in the dropdown.
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.
6. Go back to the Data tab and switch off the Filter. The rows with a few blank cells will remain, but the entire blank rows are now gone.

Remove Blank Rows By Sorting Your Data

Don’t overlook the simple but useful sort feature to surface data you do not want. In this case, you can sort your data to reveal all the blank rows in one bunch and then delete them.

1. Select the range of data.
2. Go to the Data
3. On the Sort & Filter group, you can click on either the Sort A to Z or Sort Z to A button for ascending or descending order of the sort. In both cases, the blank rows will be at the bottom of the selected range.
4. Delete them as before.

You can also revert to the original order before the data. Add an Index (like a serial number) column before sorting. Then use the Sort dialog arrange the data according to the Cell values.

Related Courses And Certification

Microsoft Excel Course and Certification

Advanced Excel Course and Certification

Basic Computer Skills - Course and Certification