Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Select & Delete Blank Cells in Excel (3 Quick Ways)

This article illustrates how to select blank cells in excel and delete them. You can delete blank rows or columns from your dataset without any harm. But, deleting blank cells may never be a good idea as it can mess up the dataset. Even then, sometimes you may need to do this. That’s why we will show you how to do that in excel.


Download Practice Workbook

You can download the practice workbook from the download button below.


3 Ways to Select and Delete Blank Cells in Excel

Consider the following dataset with some blank cells due to missing data.

Excel dataset with blank cells which we will select and delete in this article

Follow the methods below to learn how to select those blank cells and delete them.


1. Select Blank Cells & Delete Manually

  • First and foremost, you can select those blank cells manually and then delete them. This way you can keep some blank cells and delete the rest of them so that your data do not get messed up.
  • Hold the CTRL key and select the blank cells. They will look as follows after selection.

manually selected blank cells

  • Now press CTRL+- to delete them. You can also delete them by selecting Delete >> Delete Cells from the Cells group in the Home tab. After that, a small dialog box will pop up asking you to choose how to shift other cells. Choose one as required and click OK.

choose option to shift cells after deleting the blank cells

  • After that, the dataset will look as follows. It seems that there is nothing wrong with the dataset after deleting the blank cells. But you can see the difference by comparing the dataset with the earlier one.

chaged dataset after deleting the blank cells

Read More: How to Select Random Cells in Excel (5 Ways)


2. Find Blank Cells & Delete with Go To Special Command

  • Selecting blank cells manually to delete them is not a practical way if you have a large dataset. In that case, you can use the Go To Special command.
  • First, select the data range (B5:F13). Then press ALT + H + FD + S to open the Go To Special dialog box. You can also select it from the Find & Select command from the Editing group in the Home tab. Then mark the radio button for Blanks and click OK.

select blank cells using the Go To Special command

  • After that, you can delete the selected blank cells using the CTRL+- shortcut as earlier.

Read More: How to Go to Last Non Empty Cell in Column in Excel


Similar Readings


3. Select Blank Cells & Delete Using Find and Replace Command

Alternatively, you can select the blank cells using the Find command in excel.

  • First, select the data range (B5:F13). Then press CTRL + F to open the Find dialog box. Next clear anything in the Find What Then check the ‘Match Entire Cell Contents’ checkbox. Next, click on FindAll. Now press CTRL + A to select all found blank cells.

use the Find command to select blank cells

  • After that, you can delete the selected blank cells using the CTRL+- shortcut as earlier.

Read More: How to Select a Range of Cells in Excel (9 Methods)


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 and delete blank cells in excel. Which method do you prefer? Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo