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

 

An Infographic reviewing the common ways of cleaning up data in Excel.

 

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.

 

Screenshot showing the source data which contains a list of beverages and their corresponding prices.

 

  • 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.

 

Screenshot showing the cell range A2:B8 highlighted.

 

  • 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).

 

Screenshot showing the Find and Select option on the Editing Group on the Home Tab.

 

  • Select the Go To Special… option.

 

Screenshot showing the Go To Special... option highlighted.

 

  • You should see the Go To Special Dialog Box.

 

 

Screenshot showing the Go To Special Dialog Box.

 

  • Check the Blanks option and then click Ok.

 

Screenshot showing the Go To Special Dialog Box with the Blanks option checked and highlighted.

 

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

 

Screenshot showing the blank cells within the range of interest highlighted.

 

  • 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.

 

Screenshot showing the Delete Sheet Rows option highlighted.

 

  • You should see the following.

 

Screenshot showing the range with the cells still selected.

 

  • Click on any cell to deselect the selected cells.

 

Screenshot showing the range with the blanks removed.

 

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.

 

Screenshot showing the source data for the Filter example.

 

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.

 

Screenshot showing the cell range A1:B7 selected and highlighted.

 

  • 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).

 

Screenshot showing the Filter option in the Sort & Filter Group on the Data Tab.

 

  • You should see the following.

 

Screenshot showing the Filter arrows added.

 

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

 

Screenshot showing the Blanks option selected.

 

  • The result is shown below.

 

Screenshot showing the blank rows after the first filter has been applied.

 

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

 

Screenshot showing Blanks selected from the second filter.

 

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

 

Screenshot showing the data after the second filter has been applied.

 

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

 

Screenshot showing the Delete Row option.

 

  • 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.

 

Screenshot showing the 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.

 

Screenshot showing the source data for the delete blank cells example.

 

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

 

Screenshot showing the range A1:C6 selected.

 

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

 

Screenshot showing the Go To Dialog Box.

 

  • Click on the Special… button.

 

Screenshot showing the Special... button highlighted.

 

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

 

Screenshot showing the Go To Special Dialog Box with Blanks checked.

 

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

 

Screenshot showing the blank cells in the range selected.

 

  • Right-click and select Delete…

 

Screenshot showing the Delete...option highlighted.

 

  • The Delete ?  Dialog Box should appear.

 

Screenshot showing the Delete ? Dialog Box.

 

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

 

Screenshot showing the Shift cells up option checked.

 

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

 

Screenshot showing the blank cells within the data set have been 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.

 

Screenshot showing the source data.

 

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

 

Screenshot showing range A2:B6 selected and highlighted.

 

  • 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.

 

Screenshot showing the Go To Special Dialog Box with Blanks checked.

 

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

 

Screenshot showing No in the Formula Bar and in cell B3.

 

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

 

Screenshot showing the data range with the No entries added instead of blanks.

 

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!

 

About Ben Richardson

Ben is a director of Acuity Training which he has been running for over 10 years.


He is a Natural Sciences graduate from the University of Cambridge and a qualified accountant with the ICAEW.


He previously worked as a venture capitalist and banker and so had extensive experience with Excel from building financial models before moving to learn SQL, Microsoft Power BI and other technologies more recently.