How to Check if a Cell is Blank or Not in Excel

Sometimes, a cell that appears to be blank actually holds hidden characters in them. Excel registers such hidden characters as existing values in the cell. This can create a range of issues including,

• Formula Errors: You will get inaccurate results when using count functions like COUNTA. The COUNTA function counts all non-empty cells. As the cells with hidden characters aren’t technically empty, the COUNTA function makes a count.
• Errors in Sorting Cells: You cannot accurately sort empty and non-empty cells according to the content visible in the sheet.
• Filtering Cells: Excel labels empty cells as (blanks) while using the Filter tool. However, when you have hidden characters embedded in the cells, they will not be grouped together with the empty cells.
• Issues in Analyzing Data: Excel will not locate cells with these special characters when you search for blank cells. This will be an issue when you wish to look for empty cells in your sheet.
• Tabling Error: Excel separates tables if there are blank cells in between. However, if the cells aren’t truly empty, your table will be registered as one.
•  Increased File Size: As you enter characters into your sheet, your file size grows. Keeping cells with hidden characters will increase your file size which will take up more space in your device.
• Issue with importing and exporting data: As the hidden characters in empty cells increase the file size, the process of importing and exporting data slows down. This may create difficulties in getting your spreadsheet ready.

To workaround issues such as these, it is a good idea to double-check if the cells are really empty. In this article, we will dive into the multiple ways you can check if a cell is empty in Excel, including using formulas to making special selections, so keep reading!

Use ISBLANK Function in Excel

Excel has the ISBLANK function, dedicated to returning a boolean value if the cell passed as the argument is blank. The ISBLANK function requires only one argument which is the reference cell. Here is the format to write the ISBLANK function in a formula:

=ISBLANK(value)

Here, we’re using the ISBLANK function to determine if the cells in column A are empty or not. In a new cell, we entered the following formula:

=ISBLANK(A2)

Although cell A2 looks empty, it actually has a space in it. Therefore, the ISBLANK returned FALSE.

We used Flash Fill to copy the formula to the corresponding cells downwards. Cells A2 and A5 have text and linebreak respectively so, ISBLANK returned FALSE.

Cells A4 and A6 returned TRUE as they have no hidden characters embedded in them.

Use Go to Special

You can use the Go to Special to select all blank cells in your sheet. When the cells are selected, they look different than the non-empty cells giving you the idea of which cell is blank and which cell has data in them.

1. Select the range where you want to look for blank cells.
2. Go to the Home tab of your sheet and select Find & Select.
3. Choose Go to special.
   
4. From the window, select the option next to Blanks.
   
5. Click OK. All empty cells from the range will be highlighted in your spreadsheet.
   

Sort and Filter tool

You can use the Sort and Filter tool to check all cell content under a range. You can use this method to locate any hidden characters including line breaks in your cell range. If you want to get rid of linebreaks, you can check out our other article on how you can remove break lines!

However, if you suspect having spaces in between the cells, this method will not work for you as it labels it exactly as it would label empty cells.

1. Select your range and head to the Home tab.
2. Click on Sort & Filter and choose Filter.
   
3. Select the drop-down menu next to the column header.
4. Deselect Select All.
   
5. If you see any, click on the list of items that appear to have nothing; these cells have hidden cells in them.

Use Find Tool to Locate Hidden Characters

The Find tool is a great utility to check if your cell holds hidden content. You can look for spaces and linebreaks which are the more common characters that discretely fill blank cells up. If you wish, you could also replace the characters with nothing to make the cell completely blank.

1. On your keyboard, use Ctrl + F.
2. Click the Options button.
   
3. In the Find what section,

• Enter space (“ ”) to find blank cells with spaces.
  
• Hit Ctrl + J to look for line breaks. You might see a blinking dot appear next to the Find what section.
  

4. Select the box next to Match entire cell contents.
5. Click Find All.

If your sheet has no such values, Excel will display an error message. If your sheet does in fact hold such cells, they will appear in the Find results.