Precedents and Dependents in Spreadsheets

Excel's "Formula Auditing" is meant for every spreadsheet user. As long as you need to understand a spreadsheet model, you will find "formula auditing" useful. (A spreadsheet model refers to the content inside the cells and their connections, e.g., a spreadsheet model may be a financial model about interest rates and returns.) "Few tools are available for understanding and debugging spreadsheets, but they are needed because spreadsheets are being used for large, important business applications. The key to understanding spreadsheets is to clarify the data dependencies among cells. [i.e., the precedents and dependents]. "  "There is greater potential benefit from auditing tools, which are intended to aid in understanding and debugging a completed spreadsheet."   "Teachers of spreadsheets should consider devoting a portion of the course to spreadsheet auditing techniques and tools, and should emphasize arrow-type tools." ...

"Trace Precedents", a function in Excel, can be used to trace precedents of one cell, one level further up the precedent "tree" or chain with each click. Multi-Precedent is a specially developed macro that allows us to trace precedents of many cells together. The following picture shows multi-precedent at work, tracing precedents for two cells together. It also shows "Less-Multi-Precedent" at work. This multi-precedent macro is described in this book+software:   Influence charts: How to easily gain a powerful overview of your Excel spreadsheet model .

Trace precedent is a basic function. It draws an arrow from one cell to another cell when the first cell appears in the second cell's formula. That means the second cell makes use of the first cell's value. When enhanced with VBA progamming, trace precedent can do some interesting stuff. For example. it can trace everything in a worksheet and give a different color to separate groups. A sample result is in the following diagram. This colorful set of precedents and groups can be easily done by users of any level. The details are in this book:   Influence charts: How to easily gain a powerful overview of your Excel spreadsheet model