Microsoft Office Tutorials and References
In Depth Information
Chapter 4: Fixing Formula Boo-Boos
Excel 2013 has an approach to hunting down circular references. The
Formulas tab on the Ribbon has a section named Formula Auditing. In this
section is an Error Checking drop-down list that shows any circular
references. See Figure 4-5.
The drop-down will list circular references, and clicking on one will take you to
the listed cell with the circular reference. This allows you to get to them easily
instead of having to review all your formulas. Hey, now that’s a timesaver!
Mending broken links
Formulas can reference external workbooks. As an example, a formula
could be written like this: =’C:\Inventory\[Inventory.xlsx]Engine
Parts’!$D$8. The formula uses the value found in the external workbook
Inventory.xlsx. What if the workbook is not found?
When a formula references an unfound workbook, a dialog opens to navigate
to an appropriate workbook elsewhere. Figure 4-6 shows that the dialog has
opened after referencing the Inventory.xslx file. This Inventory file could
not be found, and Excel is prompting to find another workbook to use in its
place. Note in the formula bar the cell is referencing the Inventory workbook,
yet allows another to be used instead. Well, how about that! Excel is quite