Chapter 19: Making Your Formulas Error-Free
19
If you select a multicell range before displaying the Go to Special dialog box, the command operates only within the
selected cells. If a single cell is selected, the command operates on the entire worksheet.
You can use the Go to Special dialog box to select cells of a certain type, which can often
help you identify errors. For example, if you choose the Formulas option, Excel selects all of
the cells that contain a formula. If you zoom the worksheet out to a small size, you can get
a good idea of the worksheet’s organization.To zoom a worksheet, use the zoom controls on the right side of the status bar or press Ctrl
while you move the scroll wheel on your mouse.Selecting the formula cells may also help you spot a common error, namely, a formula that has been replaced acci-
dentally with a value. If you find a cell that’s not selected amid a group of selected formula cells, chances are good
that the cell previously contained a formula that has been replaced by a value.
Viewing formulas
You can become familiar with an unfamiliar workbook by displaying the formulas rather
than the results of the formulas. To toggle the display of formulas, choose Formulas ➪
Formula Auditing ➪ Show Formulas.You may want to create a second window for the workbook before issuing this command.
This way, you can see the formulas in one window and the results of the formula in the
other window. Choose View ➪ Window ➪ New Window to open a new window.You can also press Ctrl+` (the accent grave key, typically located above the Tab key) to toggle between Formula view
and Normal view.
See Chapter 4, “Working with Excel Ranges and Tables,” for more information about this command.Tracing cell relationships
To understand how to trace cell relationships, you need to familiarize yourself with the fol-
lowing two concepts:Cell precedents Applicable only to cells that contain a formula, a formula cell’s precedents
are all of the cells that contribute to the formula’s result. A direct precedent is a cell that you
use directly in the formula. An indirect precedent is a cell that isn’t used directly in the formula
but is used by a cell that you refer to in the formula.