📞 01223 214177 ✉️ karen@roem.co.uk
📞 01223 214177 ✉️ karen@roem.co.uk
If you work with somebody else's workbooks or you haven't opened one of your own worksheets for a while it can be difficult to know what's going on.
Many many moons ago I told you how you can view (and print) all formulas (see tip_238.php). But you can even take it a step further and see at a glance where the data is coming from and going to, which can help you to find errors that may occur in your worksheets due to incorrect formulas.
Here's how: (in Excel 2003)
Here's how: (in Excel 2007)
When you use any of the above commands or buttons, a tracing arrow is displayed, connecting the selected cell to each of its precedents or dependents, assuming, of course, that the cell has a precedent or dependent. When a precedent or dependent of a particular cell appears in another worksheet or in another workbook, a black dotted line with an arrow pointing from or to a worksheet icon will appear. To open the worksheet or workbook it refers to double-click the dashed arrow to display the Edit > GoTo dialog box. From there, simply select the particular reference and press OK and the corresponding workbook/worksheet will open.
To remove tracing arrows from a worksheet choose the Tools, Formula Auditing, Remove All Arrows command, or click the Remove Precedent Arrows, Remove Dependent Arrows or Remove All Arrows button on the Formula Auditing toolbar (in Excel 2003) or click on the Remove Arrows button in the Formula Auditing group on the Formulas tab, or click on the arrow next to the Remove Arrows button and click Remove Precedent Arrows or Remove Dependent Arrows.
By the way if you often inherit somebody else's workbooks and would like to better understand what's going on, why not sign up for our half day Excel Further Use course; see msexcfu.html for details.
.
I’m committed to sharing the best of what I know with others so please don’t keep me a secret. If you enjoyed today’s tip, please forward it to anyone you feel may benefit. Alternatively, feel free to reprint it (with full copyright and subscription information) in your newsletters and message boards.
