Precedents and Dependents in Spreadsheets

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.