Search | Unreferenced cells

Top  Previous  Next

This command selects all cells within the current selection that are not referenced by any formulas within the workbook.  These cells therefore constitute the outputs of a model.

Why would you want to use it?

Best practice in the development of spreadsheet models dictates that the model should only calculate those outputs that are required and that all other calculations are intermediate steps towards the final outputs.  Unreferenced cells are therefore, by definition, part of the output of a model or should be removed from the model.  You can use this command to help confirm that only those cells that are intended as outputs are unreferenced.  Those cells that have unintentionally been left unreferenced can be identified and fixed; any extraneous formulas can also be removed from the model.

How to use it

Select a range of cells, or just one cell to search the entire worksheet, and choose the 'Unreferenced cells' option on the Search pane.  You can choose using the checkboxes below to limit your search to cells which contain: formulas, constant numeric values, constant text values, constant logical values or some combination of these.

Selecting formulas only will cause OAK to ignore cells which contain inputs, which is often a useful feature.  In order to check for unreferenced inputs, select the checkboxes that correspond to the type or types of input you wish to check, e.g. constant numeric values.  Note that limiting the types of cells you wish to check will speed up the search.

If no checkboxes are selected then OAK will search for all unreferenced cells.  This means that selecting every checkbox is equivalent to leaving all checkboxes unselected.

After you have clicked 'Search', the command will then either select those cells in the current selection that are not referenced by any formulas in the workbook or return a “No unreferenced cells found in selection” message.

Note that neither the OAK unreferenced cell command nor the alternatives given below are able to detect if a cell is referenced via Excel’s offset or indirect functions.  This means that a cell referenced via the offset or indirect functions will be shown as an unreferenced cell even though it does have dependents.

Wrinkles

This operation can take a long time.  The reason is that every formula in the selection must be tested for dependents.

What you could do if you didn't have OAK

If didn't have OAK, and  wished to check whether a single cell has been referenced, then Excel contains two commands that will find the dependents of that cell.

The first is the go to dependents command which is run by selecting Home | Editing | Find & Select | Goto Special and then the dependents option.  This will either highlight the cells that depend on the cell that was selected or will display a “No cells were found” message if the cell is unreferenced.

The second command is the trace dependents command which is run by selecting Formulas | Formula auditing | Trace dependents.  This command will draw blue arrows from the selected cell pointing to dependent cells on the same worksheet.  Dependent cells on different worksheets are indicated by a broken arrow pointing to a worksheet symbol.

Double clicking on the arrow will bring up a list of dependent cells on other worksheets.  If the cell is unreferenced a message stating “The Trace Dependents command found no formulas that refer to the active cell” will be displayed.