How to Delete or Remove Blank Rows in Excel

When you have a worksheet filled with blank rows or cells, it can make it look really messy. Apart from that, it also prevents the built-in tools such as sort, subtotals, e.t.c from performing its functions correctly. Therefore, you will need to do some cleaning up and remove blank rows in your worksheet.

Although, it may seem like a huge task, especially when you have a large dataset, the blank spaces and rows can however be removed easily using the ‘Find & Select’ option in Excel.

On the other hand, much care should be taken when deleting blank cells and rows this way. This is because a blank cell may be located in a row that has other useful data and by selecting delete ‘Entire rows’, you lose all all the other data in the rows that have blank cells.

Again, if you select ‘Shift Cells Up’, it will move the data below to the next cell up thereby giving that row another person’s data.

For example, if you have this table below with 11 rows and you want to delete the blank rows in7 and 8.

Using the Find & Select option, this what you will get:

What happened here is that the blank rows in row 7 and 8 have been deleted. Also, by choosing ‘Shift Cells Up’, row 5 and 11 that had blank cells are now having other distributors data. The Distributor Name ‘Deacon Craig’ now has Finland as Country and ‘Brynne Mcgowan’ now has Vanuatu and no longer Finland as it’s Country.

Also, if you select delete ‘Entire rows’, the entire row of ‘Zahir Fields’, ‘Decon Craig’ and ‘Levi Douglas’ would be deleted from the dataset.

So, in this tutorial, we will see how to delete/remove blank cells using ‘Find & Select’ option, as well as another easy method of deleting blank cell and rows without deleting useful data or mixing up the data in the dataset.

Remove or Delete Blank Rows or Cells in a Worksheet

To do this:

#1. Select the entire data table in the worksheet

#2. Go to HOME tab and click on the ‘Find & Select’ option

#3. Select ‘Go to Special’ option

Press Ctrl + G to bring up ‘Go To’ dialog box, then click on ‘Special’

#4. In the ‘Go to Special’ dialog box, select ‘Blanks’

#5. Click ok

The blank cells will be highlighted. Check to confirm that it is only the Blank cells or rows that have been highlighted.

#6. Press Ctrl + – to pop up the ‘Delete’ dialog box

In Home tab, select ‘Delete’ under the Cells option in the ribbon, and click on Delete

#7. Select ‘Shift cells up’ to remove blank spaces and move data up to close the spaces or select delete ‘Entire Row’ to remove all the blank rows.

Now you have a clean dataset.

Remove Blank Rows Without Losing Any Data in Excel

Here, we will see a quick way to delete blank rows and cells without having to lose any data or mix up the data in the Excel sheet.

You can only notice any loss of data when you have a small dataset. But where you have very large dataset, you would have lost dozens of rows with good data without even realizing it or would have mixed up the data and end up having wrong information.

So, if you have blank cells across different columns, it means that you will only need to delete only the rows that have no single data in any column at all.

And do this, you will need to use a Key Column in the dataset to determine if row is empty or not. You can either use an existing column in the dataset or you create a new ‘Blank’ column. Here we will create a new blank column.

#1. Create a Key Column as the last column in the dataset.

#2. Insert the formula in the first cell of the new column:

=COUNTBLANK(A4:F4)

This will count blank cells in the specified range which is the first to last column in the data table.

#3. Copy the formula to the entire column.

Now the key column has been added to the table. Next, you add an AutoFilter to the data table

#4. Go to Data tab and click on ‘Filter’ option

Next is to apply filter to the new Blank column.

#5. Click the arrow in the column header, uncheck ‘Select All’ checkbox, and check 11 checkbox.

11 is the total number of columns with blank cells which means that there are 11 columns in the dataset. So you select all the rows with no data in it.

#6. Click ok.

#7. Press Ctrl + Home to select and press down-arrow key to go to first data row

#8. Then press Ctrl + Shift + End to select the entire data table

#9. Right-click on selected cell and select ‘Entire Row’ or Ctrl + –

#10. In ‘Delete entire sheet row’ dialog box, click ok.

#11. Then go to Data tab and select ‘Clear’ option to clear the applied filter

Now, all the blank rows have been removed and rows with blank cells will still be there. There will be no mix-up of data or losing any useful data.

Have you tried this method yet? Kindly share in the comment section.