Connecting Technology and Business.

Visualize Formula Linkages In Cells

​You workbook might contain many worksheets and each worksheet a lot of formulae. Or, you have received such a workbook from elsewhere. You are now interested in knowing how certain cells are related to the others in the same sheet or in other sheets. Excel has a very remarkable tool that will help you analyse this.

Under the Formulas tab of the ribbon you would see Formula Auditing cluster. This contains the necessary tools for this analysis.

First choose the cell that you want to analyse. This must contain a formula that connects to the content of one or more cells (which further can connect to other cells retrospectively or subsequently). Click the Trace Precedents command to see which cells link to this cells retrospectively. (Click Trace Dependents to see which cells link to this cell subsequently). Arrows are displayed to indicate the relationships of the cells. Boxes indicate an array of cells that are linked to the current cell. Click Trace Precedents further to see where the precedence goes until the original cell that determines the value of the current cell is reached through arrows. Precedent or dependent cells that lie in other worksheets or workbooks are indicated by a small worksheet icon at the tip of the arrow.

Trace dependents.png 

The figure above shows a sample worksheet with arrows of precedencies and dependencies of a selected cell.