Precedents and Dependents in Spreadsheets

Given a column of "similar" cells, how do we check whether the cells are used "consistently" by other cells? Any inconsistency could be a sign of error, or inconsistent design. The only quick method to check this is by the use of "Trace Dependents", or even better with "Multi-Dependents". (The slow and not so visually effective method is to use the "Find" function. Find "A1", find "A2", and so on,) For example, in this diagram, the column of cells (A1:A6) are used consistently. The dependent arrows from these cells are "consistent". In the next diagram, the arrows show many irregular patterns, indicating that the cells A1:A6 are not used consistently. To get these diagrams, 1) With "Trace Dependents". Select cell A1. Click on "Trace Dependents" under Formula Auditing. Repeat for cells A2 to A6. Alternatively, 2) With Multi-Dependents. Select A1:A6, click on "Mult...

In the previous posts, we see how we can use Excel to identify a cell with an inconsistent formula compared to its neighbors. The aim is to identify potential errors, or inconsistent designs. For LibreOffice Calc users, similar methods are available, as listed in the following table. Method Excel LibreOffice Calc Show Formula Yes Yes Show R1C1 Formula Yes (Check Options) Yes (Check Options) Formula Error Flag Yes (Check Options) - Row / Column Differences Yes (Find and Select / Go To Special) - (There is a similar function, but it needs a macro program.) Precedent Arrows    Single-cell Precedents Yes (Formula Auditing / Trace Precedents) Yes (Detective / Trace Precedents)    Multiple-cell Precedents Need a macro (e.g. VisualCheck or Power Formula Auditing ) Need a macro (e.g., VisualCheck  for LibreOffice or Power ...

Part 3 on identifying the odd man out is about precedents. To recap, the aim in finding an inconsistent cell is that the inconsistency may indicate an error, or an inconsistent design. If the cells in a block have "consistent" formulas, then each cell will have a set of "consistent" precedent arrows. This is illustrated in the following diagram. Consistent Precedent Arrows among Cells If a cell has an "inconsistent" formula, its precedent arrows will be different. This is illustrated in the following diagram, using the same example as in part 2. Inconsistent Precedent Arrows. In Excel, getting the precedent arrows for a block of cells can be quite tedious. We need to click on a cell, click "trace precedents" (perhaps a few times), and repeat for each cell in the block. However, the process can be done easily with some VBA processing. The Multi-Precedent macro allows precedent arrows to be drawn for a selected block of cells. Th...

"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 .