Excel Guide: Remove Blank Rows Or Cells

Occasionally you may find that it’s necessary to clean up your data and part of that cleaning up, may involve removing blank rows or blank cells.

In this post, we are going to show you how to remove blank rows in Excel or remove individual blank cells when needed.

What does cleaning up data mean?

 

You may often receive spreadsheets from your colleagues or need to import data from databases and other external sources. Initially, this data may not be suitable for data analysis in Excel. You will then need to clean the data in some way first.

Let’s review some of the common issues and a few ways of cleaning up or dealing with these issues.

• Extra spaces from text
• Numbers stored as text
• Duplicate values
• Errors
• Spelling Errors
• Dates stored as text
• Problems with Visual formatting
• Blank rows or cells within a data set

 

 

We are going to look at ways to delete blank rows in Excel or remove empty cells within a data range, in greater detail.

Blank rows take up additional space and can make navigation of your worksheet more difficult.

For more Excel Tips, read our guide on Forecasts in Excel here.

 

How to Delete Blank Rows In Excel Using Go To Special

 

In our source example we have a list of beverages and their corresponding prices. We would like to delete the empty rows.

 

 

• So, the first thing we need to do is select the data range of interest, which in this case is cell range A2:B8. This is the cell range that contains the data and the blanks.

 

 

• With the range selected, go to the Home Tab (Step 1 in the image) and on the Editing Group click on Find and Select (Step 2 in the image).

 

 

• Select the Go To Special… option.

 

 

• You should see the Go To Special Dialog Box.

 

 

 

• Check the Blanks option and then click Ok.

 

 

• You should now see the blank cells within the range selected.

 

 

• Now with these cells selected, go to the Home Tab and on the Cells Group click on the Delete drop down arrow.
  We will select the Delete Sheet Rows option.

 

 

• You should see the following.

 

 

• Click on any cell to deselect the selected cells.

 

 

How To Delete Blank Rows In Excel Using Filter

 

Now the method to remove blank rows in Excel that was demonstrated in the above example, works well for simple data sets. However, you may have a more complex data set such as the one shown below, where you will need to use the Filter method to remove empty rows in Excel.

In our source example row 3 is completely blank however, row 5 contains the coach data but not the sport information.

 

 

Now if we use the previous method to delete the rows, Excel will delete rows 3 and 5 which we do not want.

• So, to delete only row 3, select the range of interest which in this case is range A1:B7.

 

 

• Now with this range selected, go to the Data Tab (Step 1 in the image) and in the Sort & Filter Group select Filter (Step 2 in the image).

 

 

• You should see the following.

 

 

• Now we will begin with column A, select the drop down next to Sport and select Blanks and then click Ok.

 

 

• The result is shown below.

 

 

• Now click on the drop down arrow on column B and then select Blanks and click Ok.

 

 

• The result is the following which shows only row 3.

 

 

• Now select row 3 and right-click and select Delete Row.

 

 

• Press Ctrl-Shift-L which is the shortcut for the Filter option to turn Filter off.You will see the entire data set with the blank row removed. The row which does not contain the sport information but only has the coach details is still there.

 

 

How to Delete Blank Cells in Excel

 

You may encounter a situation where you would like to delete cells rather than remove blank rows in Excel. In our hypothetical situation, shown below we have data imported from an external source.

There are sometimes blank cells in the imported data as shown below.

 

 

• So, in order to delete cells A5, B4 and C3 select the whole range.

 

 

• Now with this range selected press F5 on the keyboard in order to see the Go To Dialog Box.

 

 

• Click on the Special… button.

 

 

• The Go To Special Dialog Box should appear. Check Blanks.

 

 

• Click Ok.
• Only the blank cells in the range will be selected.

 

 

• Right-click and select Delete…

 

 

• The Delete ?  Dialog Box should appear.

 

 

• Choose the Shift cells up option because of the way the data is arranged and click Ok.

 

 

• As a result, the blank cells will be removed.

 

 

Blank cells also count as duplicate cells. You can read our Ultimate Guide to Highlight Duplicate Values in Excel here.

 

Troubleshooting When Deleting Blank Cells

 

There may be times when you encounter problems when trying to remove blank rows or cells. Let’s look at some of the common scenarios and ways of resolving these issues.

 

Verify your Initial Range

When using the Go To Special… option you may find that cells outside your range of interest were selected, in which case press Ctrl-Z on the keyboard and re-select the range of interest.

Review the headings when using Filter

Let’s say you highlighted only your range without the headings and then pressed Ctrl-Shift-L, the filter will not be correct.

So simply press Ctrl-Shift-L to toggle the Filter and then re-select the correct range of interest with the headings included.

Check you’re selecting the right option from the Delete ? Dialog Box

When using the Delete     ? Dialog Box you will get options to either Shift cells left, Shift cells up, Entire row or Entire Column.

So, the option you choose will depend on the layout of your data.

 

When Not To Remove Empty Cells

 

There will be times when you do not want to remove empty cells from your data set. Let’s look at our example below.

We have a list of salespeople and whether they attended the training provided by the company. Now in this case because certain salespeople did not attend the training, their names were not recorded on the attendance register.

 

We do not want to lose this data, we want to have No instead of the blank entries.

 

 

• So what we would do in this case is select the data range A2:B6.

 

 

• With the range selected press F5 on your keyboard in order to open up the Go To Dialog Box and press the Special… button.

 

• Check Blanks and Click Ok. You should see the following.

 

 

• Now with these cells selected type No in the formula bar.

 

 

• Press Ctrl-Enter to populate all the blanks cells. You should see the following.

 

 

Let’s review the skills you have learned

 

• You know what data cleaning entails.
• You know how to use the Go to Special… command to identify blanks in your data set.
• You know how to delete empty rows in Excel using the different Delete options that Excel provides.
• You know how to use the Filter option to identify blanks in more complex data sets and then remove the empty rows.
• You know how to remove individual blank cells within a data set.
• You know how to identify blank cells and then populate those cells with the correct data.

 

Conclusion

There are few ways to remove blank rows in Excel. In this post we have shown you how to remove blank rows in Excel and how to remove individual blank cells. Manually removing blanks rows can be time consuming, especially when you are working with large data sets.

You will find these techniques useful if you often have to clean data sets or work with spreadsheets that multiple people have collaborated on.

Special thank you to Taryn Nefdt for collaborating on this article!