How to Select Blank Cells in Excel and Delete (3 Easy Methods)

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.

Download Practice Workbook

You can download the Excel file and practice the explained methods.

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 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: How to Select Random Cells in Excel (5 Ways)

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: How to Go to Last Non Empty Cell in Column in Excel

Similar Readings

• How to Go to the End of Excel Sheet (2 Quick Methods)
• Select All Rows in Excel (6 Easy Ways)
• How to Select Data in Excel for Graph (5 Quick Ways)
• Select Only Filtered Cells in Excel Formula (5 Quick Ways)
• How Do I Quickly Select Thousands of Rows in Excel (2 Ways)

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.
• 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 a Range of Cells in Excel (9 Methods)

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.

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.
• Then, from the Delete group >> click on Delete Sheet Rows.

You can see the dataset has no blank rows.

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

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. You can also visit our website Exceldemy to explore more about Excel. Stay with us and keep learning.

Related Articles

• How to Select Column to End of Data in Excel (3 Easy Methods)
• Excel VBA to Protect Sheet but Allow to Select Locked Cells (2 Examples)
• Select All Cells with Data in a Column in Excel (5 Methods+Shortcuts)
• How to Select Multiple Cells in Excel without Mouse (9 Easy Methods)
• Select All Cells with Data in Excel (5 Easy Methods)
• How to Select Cells with Certain Value in Excel (5 Methods)