When is a blank cell not  blank cell? Answer. When a blank is the result of a formula.

Formulas such as =IFERROR(S10/$D10,""), may result in blank cells that are not actually blank.

A blank returned by a formula such as this is actually a zero-length string (""), which is different than an genuine empty cell.

These cells can sometimes be difficult to deal with because Excel doesn't treat them as 'blank' cells.

This can cause issues for you when you have a worksheet with formulas that return zero-length strings.

Some issues that may result from these 'non-blank' blank cells are...

• Using CTRL + arrow key to jump to next blank cell in a row or column may not work because Excel will not recognize these 'blank' cells and go right on past them;

• Using F5, Goto, Special..., Blanks does not recognize these cells as blanks.

• Autofilter DOES treat these cells as blanks, so for a single column it's easy to find and select these blanks. However, if the blanks are scattered all over your worksheet, using Autofilter on each column will be very cumbersome.

• Using  Find (CTRL+F) and Replace (CTRL+H) doesn't ALWAYS find these blanks.

I said that "Find and Replace doesn't ALWAYS find these blanks".

Here's the trick you need to know to find (and replace, if you want) these zero-length blank cells.

If you want to limit the search area, select that range first, otherwise select a single cell to search the entire worksheet.

1) Open the Find and Replace  dialog (CTRL+F if you just want to Find or CTRL+H if you want to find and replace );

2) Clear anything in the 'Find what' field;

3) In the 'Look in' field, select Values;

4) Click the Find All button. All 'found' cells will be listed at the bottom of the dialog with only one item selected;

5) Press CTRL+A to select all items in the 'found' list. All items will be highlighted in the dialog and all cells on the worksheet will be selected;

6)  At this point, if you want to replace these cells with something (e.g. zeros) type the value in the 'Replace with' field and click Replace All;

7) When you click the Close button on the dialog, you will be back to the worksheet with all of the cells still selected. If you want, you can add a background fill color to highlight theses cells to make them easier to identify .

I struggled with locating these 'blank' cells many times before I discovered that the trick was to search in 'Values' not 'Formulas'.

To share this tip with your friends and
colleagues, choose one of these options...

To get more tips like this one every week...

Sign up for my FREE twice-weekly Newsletter

'Spreadsheet Tips From An Excel Addict'