Power Formula Auditing: The Power Tool for every Excel User

Figure 38. Precedents for Offset Formula

---

Vlookup & Hlookup formulas
The Vlookup function allows us to search a table based on a value in the first
column, and return a value from some other column along the same row as the
search value. An
illustration
is given at Microsoft support (
VLOOKUP function
).
The Hlookup function is a similar function which search based on the first row,
instead of the first column.
The next figure shows the trace-precedents arrows for vlookup and hlookup
formulas.
The arrows indicate the table used in the search, but does not pinpoint the cell
value that is returned. For example, the vlookup formula returns the birthdate in
cell B3.
Trace-precedent does not go into the results for formula evaluations.
Figure 39. Precedents for Vlookup /Hlookup Formula

---

Formulas that cross worksheets
In some complex situations, people organize their Excel model across a few
worksheets. It is possible to trace precedents across worksheets. However, it
is very difficult.
Consider a very simple example where one worksheet is used for a company’s
profit model for one year. Over a few years, there will be a few worksheets,
one for each year. In addition, there is a total worksheet that sums number
across the yearly worksheets.
Consider this very much simplified model where the purpose is solely to see
what happens when precedents are traced across worksheets. There are three
worksheets: All-years, 2016 and 2017, as shown in the following figure.
Notice the cross-worksheet formula (=‘2016’!C5+’2017’!C5).

---

Figure 40. Multi-Worksheet Model
What happen when we trace precedents from the All-years worksheet? We
select cell C5, then we click on “Trace Precedents”. The result is shown in the
next figure.

---

Figure 41. Cross-worksheet Precedent
Notice that the precedent arrow is a dash arrow and the beginning of the arrow
shows a table, indicating a worksheet. We can identify the precedent by
carefully double-clicking on the arrow. This will bring up a panel that shows
the precedent cells, as shown in the next figure.
Figure 42. Precedent Cells across Worksheets
Double-clicking on a cell reference in the panel will bring us to that cell (in its
own worksheet).
Tracing across worksheets is usually very difficult.

---

10. A Special Chapter for Spreadsheet Teachers
As teachers, when we ask students to develop spreadsheet solutions, how do
we check the correctness of the answers?
Checking other people’s spreadsheet model is well recognized as a difficult
and tedious task.
One way used by teachers to reduce the difficulty is to have a spreadsheet
template where students must fill in formulas and values at specially
designated cells.
The student answers are then compared with the correct solution, e.g., we can
overlay worksheets and check the corresponding values.
Even then, a common problem is that students may simply enter the expected
value instead of the correct formula. For example, a value of 10 may be
entered instead of a formula, say, =A1*B2. The current recommended check is
to turn formulas on and off, and visually check for values where formulas
should be. (The fast way to toggle formulas on and off is ctrl+`)
Power Formula Auditing is very suitable for doing this type of check: a value
when a formula should be. The precedent arrows drawn by “Multi-Precedents”
will show clues. There will be missing arrows, and /or the chain of arrows
will be disconnected.
The next figure shows a table to calculate yearly balance with different interest
rates for each year.