Excel: Determining Cell Dependencies

Last reviewed: November 30, 1994
Article ID: Q32766

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
Microsoft Excel for the Macintosh, versions 1.5, 2.2, 3.0, 4.0
Microsoft Excel for OS/2, versions 2.2 and 3.0

SUMMARY

In Microsoft Excel, you can determine the relationship of a cell or range of cells to other cells on your worksheet using the Info window, the Select Special command on the Formula menu, or the Auditing option on the Tools menu. These relationships can take the form of precedents or dependents. Precedents are the cells referred to in the formula contained in the active cell; dependents are the cells with formulas that refer to the active cell. Determining these relationships can be useful when you attempt to track down a circular reference or when you want to delete a range of cells but don't want to alter formulas or values.

Note that external references will not be displayed as precedents or dependents; these will be displayed only when they exist on the active worksheet.

MORE INFORMATION

The following describes the three methods of determining a cell's dependencies.

To use the auditing option on the Tools menu (Microsoft Excel version 5.0)

Use the Auditing option on the Tools menu to trace precedents and dependents. Microsoft Excel 5.0 will trace precedents from an external sheet, but not dependents from another sheet.

To use the Info Window

Highlight the range of cells that you would like to delete.
In Microsoft Excel version 3.0 and earlier: from the Windows menu, choose Show Info.
In Microsoft Excel version 4.0: from the Options menu, choose Workspace. Enable the Info Window display option.
From the Info menu, choose Dependents (or Precedents).
Select All Levels.
From the Window menu, choose your worksheet name.
Press the TAB key to move to different cells in your selected range. As you move the active cell, the Info window will be updated to display information for the currently selected cell.

If any cells show dependents, then you know that the values of other cells are dependent on the values of the cells to be deleted. If any cells show precedents, you know that among the cells to be deleted are formulas that refer to other cells.

To use the Select Special command

A second method of determining a cell's dependents and precedents is to use the Select Special command. For example, to find all of the cells on your worksheet that are referred to by other cells, do the following:

Select the entire worksheet.
a. If you are using a mouse, click the empty box located
```
      directly above the row number 1 and to the left of the column
      header A.
```
b. If you are using a keyboard, press SHIFT+CTRL+SPACEBAR.
From the Formula menu, choose Select Special.
Select Precedents and select All Levels.
Choose OK or press ENTER.

This process will highlight all the cells that are referred to by other cells on the worksheet. If the cell(s) to be deleted are included with those highlighted, do not clear them because other cells and/or calculations are dependent on their values.

Additional reference words: 1.0 1.00 1.03 1.04 1.06 1.5 1.50 2.0
2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 3.00 4.0 4.00
KBCategory: kbusage
KBSubcategory:

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.