Precedents and Dependents in Spreadsheets

What makes a spreadsheet a spreadsheet, and not a word document or a database or a calculator? What is the most basic and defining feature of a spreadsheet? "The key feature of spreadsheets is the ability for a formula to refer to the contents of other cells, which may in turn be the result of a formula."  "The ability to chain formulas together is what gives a spreadsheet its power."                                ( https://en.wikipedia.org/wiki/Spreadsheet ) Yes, it is this feature of chaining formulas. A formula refers to a cell that itself may be another formula. Spreadsheet users could design complex chains of formula. However, once built, there is no easy way to see the chains of formula. That is not a good situation. Imagine a city that has built a network of roads but has no map of the roads. This is where "trace precedent" and "trace dependent" come in.  If you are at o...

Tracing precedents and dependents need not be all hard work. They can be used to create some puzzles. A word is hidden in this spreadsheet. It has something to do with UK's election. (This spreadsheet has no macros.) Precedent Hidden Word

I want to trace precedents in Google Sheets. It is not possible to do that in Google Sheets. The closest way is to open the file with Excel or LibreOffice Calc. But there are compatibility problems with special formulas and macros. (Update: Please see the comments for this post)

In a survey, it was found that spreadsheet users who are every experienced or experts use "formula auditing tools" frequently, on average. With the measuring scale of "never use, rare use, occasional use, frequent use and daily use", an average of frequent use means that many use formula auditing tools daily. Furthermore, 51% of them use formula auditing tools to evaluate their spreadsheet models. In contrast, less than 10% of the less experienced spreadsheet users use formula auditing tools to evaluate spreadsheet models. A major component of "formula auditing tools" is for tracing precedents and dependents. Data are from this paper: Baker, Kenneth R., et al. "Comparison of characteristics and practices amongst spreadsheet users with different levels of experience."  arXiv preprint arXiv:0803.0168  (2008).

If you are working on a spreadsheet, you may want to save the precedent arrows to your xls file. When you rework on your file, you can continue where you were before. Excel does not allow that. In contrast, LibreOffice Calc allows this. One up for Calc. In Excel, we can do that with the Excel extension  VisualCheck for Excel (  Influence charts: How to easily gain a powerful overview of your Excel spreadsheet model ). Some buttons in VisualCheck draw a different version of precedent arrows and these can be saved to a file.

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