In this article, we will extensively describe how to select blank cells in Excel and delete them. We will go through 3 easy methods to do the task.
Blank cells in the right places cause no harm to our data. However, if blank cells are kept in the wrong places in a dataset, then we must select and delete them. There are some easy techniques to do the task. Here, we will describe these techniques.
In the following image, you can see that the first dataset has several blank cells. Next, after selecting and deleting blank cells from the first dataset, we get the second one. So let’s dive into the following article to do the task.
How to Select Blank Cells in Excel and Delete: 3 Easy Ways
In the following dataset, you can easily notice several discrete blank cells. Now, using this dataset, we will describe how to select blank cells in Excel and delete them with 3 easy methods.
Here, we used Microsoft Excel 365. You can use any available Excel version.
1. Select Blank Cells & Delete Manually
This method works fine for a small dataset with a few blank cells.
If you have a larger dataset with numerous blank cells, this is not the appropriate method. For this follow Method 2 or Method 3.
- First of all, we will select one blank cell >> keep pressing the CTRL.
- Select the other blank cells one by one.
- Then, go to the Home tab >> from the Delete group >> select Delete Cells.
Here, you can use the keyboard shortcut CTRL+- keys to delete the selected blank cells.
After that, a small dialog box will pop up asking you to choose how to shift other cells.
- Here, we select Shift cells up >> click OK.
You can see that the dataset has no blank cells.
Read More: Select All Cells with Data in ExcelÂ
2. Find Blank Cells & Delete with Go To Special Command
This is a quick and handy method to select blank cells and delete them. Also, it is appropriate for all types of datasets.
- Select the entire dataset excluding the column headers.
- Go to the Home tab >> from the Find & Select group >> select Go To Special.
At this moment, a Go To Special dialog box will appear.
- Click on Blank >> click OK.
You can see all the blank cells have been selected.
- Now, delete these blank cells by following the steps of Method1.
You can see the dataset excluding the blank cells.
Read More: Select All Cells with Data in a Column in ExcelÂ
3. Select Blank Cells and Delete Using Find & Replace Command
This is also a handy and useful way to do the task.
- We will select the entire dataset excluding the column headers >> go to the Home tab.
- Then, from the Find & Select group >> select Find.
Here, instead of the above steps, you can use the keyboard shortcut CTRL+F to bring out the Find and Replace dialog box.
At this point, a Find and Replace dialog box will appear. Here, keep the Find what box empty.
- Then, click on Find All. This will bring out all the empty cells.
- Now, click on the first empty cell >> press CTRL+A to select all the empty cells.
- Click Close.
You can see all the blank cells have been selected.
- Now, delete these blank cells by following the steps of Method1.
You can see the dataset now has no empty cells.
Read More:Â How to Select Cells with Certain Value in Excel
How to Delete Blank Rows in Excel
In the following dataset, you can see that we have several blank rows. Now, we will describe how you can delete blank rows in Excel. We will go through 2 ways to do the task. So let’s go.
1. Use of Filter Feature to Delete Blank Rows in Excel
In this method, we will use the Filter feature to delete blank rows in Excel.
- We will select the entire dataset >> go to the Data tab >> click on the Filter.
Therefore, you will see filter icons in the column heading of the dataset.
- Now, click on the Filter icon of the Phone Number column.
- Click on Select All, this will unselect all the options.
- Then, click on Blanks >> click OK.
You can now see the blank rows only.
- To delete these blank rows, select the blank rows >> go to Home.
- Then, from the Delete group >> select Delete Sheet Rows.
You can see the blank rows have been deleted. Now, we will clear the filtered dataset.
- To do so, we will go to the Data tab >> click on Clear.
You can see that the dataset has no blank rows.
Read More: How to Select Random Cells in Excel
2. Applying Sort Feature to Delete Blank Rows in Excel
You can use the Sort feature to delete blank rows in Excel. This is another easy and quick method to do the task.
- Select the entire dataset >> go to the Data tab >> select sort Z to A.
Therefore, all the blank rows will appear at the bottom of the dataset. Now, we will delete these blank rows.
- Now, we will select these blank rows >> go to the Home tab.
- Then, from the Delete group >> click on Delete Sheet Rows.
You can see the dataset has no blank rows.
Read More: How to Select Highlighted Cells in ExcelÂ
When Should You Avoid Removing Empty Cells in Excel by Choosing Blanks?
You can get rid of blank cells by using one of the earlier described methods. However, Removing empty cells could be harmful to organized data. Removing blank cells can mess up the structure of the dataset.
Thus please keep in mind to remove blanks from your worksheets carefully. Also, follow the warnings given below to avoid this from happening:
1. Remove Empty Rows and Columns Rather Than Blank Cells
For a structured dataset, where its rows and columns have related information, removing blank cells will mess up the dataset. For this case, you must only delete empty columns or empty rows.
2. Does Not Apply to Excel Tables
It is not possible to remove a single blank cell from an Excel Table. Therefore, you will only be allowed to remove Table rows only.
To delete blank cells from an Excel Table, you will have to convert the table to a range, and then delete the blank cells.
3. Can Affect Named Ranged and Formulas
When we modify cell references of Excel formulas, many of the formulas can get adjusted, but not all of them. Therefore, when we delete a blank cell which was a cell reference for a formula or Named Range, the formula or Named Range might get broken.
Thus, after deleting blank cells, check whether the formulas or Named Ranges are working properly or not.
How to Remove Blank Cells after the Last Cell with Data in Excel
In Excel, blank cells with formatting or characters that aren’t readable can create a lot of problems. For instance, you can end up with a file that is considerably larger than necessary or some pages that are blank.
In order to avoid these problems, we shall delete (or clear) any empty rows and columns that contain spaces, formatting, or unidentified invisible characters.
How to Access a Sheet’s Last-Used Cell
Click on any cell on the sheet and press Ctrl + END to navigate to the last cell on the sheet with data or formatting.
If the mentioned shortcut only chooses the last cell containing your data, all subsequent rows and columns are blank. In this case, you don’t need to delete any blank cells.
However, if it leads you to a visibly empty cell, you should be aware that Excel does not regard that cell as blank. That cell might contain a custom formatting, non-printable character, or an accidental keystroke. Thus, the cell is not blank at all.
Remove Blank Cells After the Last Cell with Data
Now, we will show how you can delete blank cells after the last cell with data. Follow the steps.
Here, we will show you how you can remove blank cells after the last row of your dataset.
- Click on cell B11, which is the first blank cell after the last row of our dataset.
- Press CTRL+SHIFT+ENTER.
This will select the range of cells between cell B11 and the last used cell.
Now, we will delete these ranges of cells.
- To do so, we will go to the Home tab >> click on Clear >> select Clear All.
You will see that blank cells after the last cell with data will be deleted.
Here, you can do the same to delete blank cells after the last column of your dataset.
In that case, click the cell of the first empty column to the right of your dataset. The other steps will be the same.
Things to Remember
- You must not delete blank cells unless the row-wise relation among data is not important. Otherwise, deleting blank cells can mess up your entire dataset.
- It will be wise to make a copy of your dataset before applying the methods to avoid any loss in the data structure.
Download Practice Workbook
You can download the Excel file and practice the explained methods.
Conclusion
Now you know how to select blank cells in Excel and delete them. Thank you for going through this article. Do you have any further queries or suggestions? Please let us know in the comment section below.
