Search | References to blank cell

This command selects all cells on the current worksheet that reference blank cells within the current selection (which is taken to include the blank cells in the unused area that surrounds each worksheet).  This command is most useful when the entire worksheet (or equivalently a single cell) is selected.

Why would you want to use it?

Good practice in developing models dictates that formulas should not refer to blank cells.  Either the reference itself is not required and should be deleted or the cell should contain another formula or some input data.  This function allows you to locate those cells that refer to blank cells and to confirm that nothing has been deleted by mistake.  The formulas and input data can then be reinstated or the references deleted as appropriate.

Another danger surrounding blank cells is that it is possible that the user does not realize that a particular blank cell is referenced and introduces some unexpected data or formula to that cell.  This information can “leak” into the body of the model and produce unexpected results.

How to use it

Select a range (typically an entire worksheet or equivalently a single cell on the worksheet) and choose the command from the menu; the command will either select those cells on the current worksheet that reference blank cells within the selection (which is taken to include the blank cells outside the used range) or return a “No references to blank cells found” message.

Wrinkles

Any blank cells that are referenced on a different worksheet are not selected by this command.

Some people intentionally include blank cells in summary formulas for the sake of simplicity, for example “=SUM(D5:D125)”.  To avoid finding these cells, do not include them in your selection.

What you could do if you didn't have OAK

If you didn't have OAK, you could find find blank cells that are being referenced by other cells using Excel’s Go to | Special command.  Selecting the entire worksheet, then using Home | Find & Select | Goto Special | Precedents will select all the cells that are being referenced by other cells.  Using Home | Find & Select | Goto Special | Blanks now will then return all blank cells that are being referenced, which can then be checked and edited as needed.

What takes Excel two operations is done more neatly by OAK in a single action.