By: Ben Richardson Last updated:
February 17th, 2022
Reviewed By: Maximillian Hindley
News & tips
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.
We cover how to delete data in our Introductory Excel Course.
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.
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.
In our source example we have a list of beverages and their corresponding prices. We would like to delete the empty rows.
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.
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.
Blank cells also count as duplicate cells. You can read our Ultimate Guide to Highlight Duplicate Values in Excel here.
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.
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.
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.
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.
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.
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.