Precedents and Dependents in Spreadsheets

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

To recap, the aim in finding an inconsistent cell is that the inconsistency may indicate an error, or an inconsistent design. Excel provides a convenient way to identify a cell that is inconsistent with its neighbors. It puts a green triangle at the upper left corner. This is shown in the following diagram. There are two disadvantages. 1. Inconsistent cells at the end of a column or row are not highlighted. 2. The green triangle (or error flag) is also used to indicate other types of errors. The different errors are listed in Home/Options/Formulas/Error Checking Rules. There is another to identify inconsistent cells. This is done by first selecting the "consistent" block of cells (C1:C6 in this case), then select Home /Find and Select / Go To Special / Column Differences. The result is as shown. "Column Differences" Note that two cells are highlighted (C4 and C6). This method may be harder to use if the block of cells stretches over a screen...

In Excel, very often, we expect a block of cells to have "consistent" formulas. For example, if cell C1 is "=A1+B1", and cell C3 is "=A3+B3", then we expect cell C2 is "=A2+B2". Sometimes, a cell may have an inconsistent formula. This could mean an error, or an inconsistent design, or a special case, and could warrant more attention. So, how do we find the odd man out? There are many ways to do this in Excel. First we explore the textual methods, by looking at formulas. Normal Formulas We can also show formulas with the R1C1 format (Choose File/Options/Formulas/R1C1 reference style). R1C1 Formulas To look for an inconsistent cell within a block of cells, the R1C1 format is easier for us to notice the difference, usually. In later posts, we will explore other more visual methods.