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

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of How to Select Blank Cells and Delete in Excel


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.

Dataset for Selecting Blank Cells and Deleting Them


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.

Selecting All Blank Cells Manually

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.

Selecting Shift cells up option to Select Blank Cells and Delete in Excel

You can see that the dataset has no blank cells.

Result after Manually selecting and deleting Blank Cells in Excel

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.

Using Go To Special Feature

At this moment, a Go To Special dialog box will appear.

  • Click on Blank >> click OK.

Selecting Blank in Go To Special Dialog Box

You can see all the blank cells have been selected.

  • Now, delete these blank cells by following the steps of Method1.

Selected Blank Cells to Delete in Excel

You can see the dataset excluding the blank cells.

Result after using Go To Special Feature to select and delete blank cells in Excel

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.

Selecting Find Command from Home Tab to Select Blank Cells and Delete in Excel

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.

Selecting All Empty Cells Using Find & Replace Command

You can see all the blank cells have been selected.

  • Now, delete these blank cells by following the steps of Method1.

Selected Blank Cells Using Find and Replace Command

You can see the dataset now has no empty cells.

Result after using Find and Replace Feature to select and delete blank cells in Excel

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.

Dataset for Deleting Blank Rows


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.

Adding Filter from Data Tab

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.

Selecting Blank Cells

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.

Deleting Blank 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.

Clearing Filter

You can see that the dataset has no blank rows.

Sorting Dataset from Z to A

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.

Sorting Dataset from 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.

Deleting Blank Rows

You can see the dataset has no blank rows.

Dataset without 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.

Removing Blank Cell After the Last Cell with Data

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.


Related Articles


<< Go Back to Select Cells | Excel Cells | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo