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 about "odd man out", the focus is on a cell with a formula that is inconsistent with the neighboring cells' formulas. That is, the cell uses other cells (in its formula) differently from its neighboring cells. In this post about "odd woman out", the focus is on a cell that is used differently from its neighbors. For example, for a column of cells (A1:A10), A1 appears in C1's formula, A2 appears in C2's formula. and so on until A10 appears in C10's formula, except for A4 which does not appear in C4's formula. A4 is the odd one out. The terms "odd man out" and "odd woman out" have no gender meaning. They are used simply to differentiate the oddness. How can we use Excel to identify the "odd woman out"?

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.

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

"Is it possible to format the trace precedents and dependents arrows? I want to change colours of the arrows." In Excel, the answer is no. For LibreOffice Calc, the answer is yes. (See a later post on this) But, in Excel, you can do some programming in VBA to do your own precedent and dependent tracing with the colors of your choice. For example, this diagram with different arrow colors was made with VBA macros. Each color indicates a group of cells that are connected.  The diagram shows 3 colors, indicating 3 groups of cells that are not connected to one another. Precedent Arrows with Different Colors This is done with the VisualCheck software described in this book: Influence charts: How to easily gain a powerful overview of your Excel spreadsheet model

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

Influence charts: How to easily gain a powerful overview of your Excel spreadsheet model   Books about Excel don’t tell you how to gain an overview of the model in a spreadsheet. They talk about how to cut and paste, how to write formulas, and how to use special formulas. But do you know how all these formulas are organized in a spreadsheet model? Normally, people will have a very hard time doing this, as they have to look at formulas one at a time.  This unique Excel book shows you how you can quickly and easily gain an overview of an Excel model, with just a few clicks. The automatically generated overview, from the specialized software (VisualCheck) that comes with the book, has many powerful uses, as illustrated in the book. This book is meant for all types of Excel users. It is meant for both novice and expert users. Whether you create spreadsheets or you work with other people’s spreadsheets, this book will be very useful for you. T...