Formula Auditing – woes and arrows

I’ve been playing around with the Formula Auditing tools a fair bit recently. These things:
 
Formula Auditing Tools

 
In the course of this, I noticed a few things I haven’t before. Firstly, here’s my setup:
 
Formula Auditing Example_No Arrows
 
 
When I have cell D6 selected and click Trace Precedents, Excel is kind enough to draw just one arrow from the precedent range, while putting a box around the entire Precedent range so I can see where it is:
 
Formula Auditing Example_TracePrecedents
 
 
If I were to click on Trace Dependents for that cell, I’d like to see pretty much the same thing:
 
Formula Auditing Example_DesiredTraceDependents
 
 
…but here’s what I actually see:
 
Formula Auditing Example_TraceDependents
 
 
…which looks like something that Hippies were hanging on the walls in the late sixties:
 
String Picture
 
 
…when they weren’t out protesting, that is:
 
String Protest
 
 
Doing a Trace Precedents when there’s a much longer array of dependent cells involved looks even worse:
 
Formula Auditing Example_TraceDependents_Many
 
 
…and Excel becomes very sluggish as you scroll around, so presumably Excel is constantly redrawing these. Scrolling down doesn’t tell you much…that’s for sure:
 
Formula Auditing Example_TraceDependents_Many2
 
 
Let’s take a look at another setup, to better illustrate a couple of things I didn’t know until now:
 
Formula Auditing Example2_blank
 
 
Here’s what Trace Dependents has to say about cell B2:
 
Formula Auditing Example2_TraceDependents_Level1
 
 
One thing I didn’t realise until today, is that if you keep clicking that Trace Dependent button, Excel keeps drawing in additional levels of downstream dependents:
 
Formula Auditing Example2_TraceDependents_Level2
 
 
Formula Auditing Example2_TraceDependents_Level3
Formula Auditing Example2_TraceDependents_OffSheet
 
 
In case you didn’t know, you can double-click on any of the blue arrows, and you’ll be taken to the Precedent/Dependent cell concerned…particularly handy if it points somewhere off-screen. And you can double-click the arrow once you’re there to be magically transported back again. The dotted arrow pointing to a little sheet icon in the above screenshot tells you that there’s an off-sheet dependent that points at cell C19, which you can jump to if you double click on that dotted arrow and then select the reference from the Go To box:
 
Formula Auditing Example2_Goto
 
…although as you see above, the native dialog box is so narrow that you’re unable to actually see the cell addresses, and can’t be resized. In that case, you might want to download Jan Karel’s excellent RefTreeAnalyser, that fixes this and does a good deal more besides:
Formula Auditing Example_JK_UserForm
 
It also has a much better way of displaying precedents, by overlaying in the current window some little pictures of any precendents that happen to be out of view or on another sheet. (Would be really handy to have the same functionality for dependents too.):
 
Formula Auditing Example_JK_Visualise
 
 
Colin Legg has some great code that will also help you to determine all on-sheet and off-sheet precedent cells, using the .NavigateArrow method to actually travel up those blue arrows and thus find any precedents on other sheets. I imagine Jan Karel uses pretty much the same approach. [Edit: No, he doesn’t ]. You’ve got to use the .NavigateArrow method, because the Range.Precedents property doesn’t return precedents on other sheets or other workbooks.

Now here’s something nasty I’ve just noticed about the native Formula Auditing tool: It doesn’t pick up on off-sheet references that involve any kind of Table Reference, although on-sheet references work just fine:
 
Formula Auditing Example_Tables offsheet
 
 
So it is well broken, in my opinion, because I often refer to Tables on other sheets. And both Colin’s code and Jan Karel’s addin won’t help you here, I’m afraid. [Edit: Jan Karel’s code still catches these.] Seems to me the only way to get around this would be to search the worksheet for instances of a Table’s name occurring within formulas. That’s assuming there’s no way to actually read Excel’s dependency tree from wherever Excel maintains it. I seem to recall seeing a post a few years back about how you can extract information from the tree by extracting XML from the workbook file, but that might just be a flight of fancy. Anyone know whether it ispossible to interrogate the dependency tree directly somehow?

How ’bout colours instead of Arrows?

Given all those arrows can get pretty confusing, I thought I’d have a crack at coding up something that lets you use Conditional Formatting instead and/or arrows to highlight Dependents (Green) and Precedents (Blue). Here’s my starter for ten, with both Dependents and Precedents highlighted. Direct Dependents/Precedents get a darker colour and white bolded font so you can easily tell them from indirect:
 
Formula Auditing Example_Jeff1
 
…and you can restrict it to just showing direct:
Formula Auditing Example_Jeff2
 
…and overlay arrows if you want:
 
Formula Auditing Example_Jeff3
 
 
It automatically updates if you change your selection, too:
 
Formula Auditing Example_Jeff4

It doesn’t solve the Table issue mentioned above, but I’ve been finding it quite handy to get a quick feel for what’s going on in those crappy spreadsheets I inherited…err…designed recently.

Here’s a sample file:
FormulaAuditing_20141112

12 thoughts on “Formula Auditing – woes and arrows

  1. Thanks Jeff, another insightful post. I think that colour highlighting is a great idea.

    Keep up the good work.

  2. Hi Jan Karel. Thanks for the correction…have amended. So do you manage to extract the precedents directly from the dependency tree somehow? What you’re doing seems like dark magic to me, with an emphasis on magic :-).

  3. Ah.I guess you could leverage off the Range.DirectDependents and Range.DirectPrecedents properties, but of course you’d still have to check for objects (charts, tables, PivotTables etc) that reference (or are referenced by) the cell concerned. But that would certainly let you do Dependents, and I imagine is pretty fast.

  4. I meant to say: It does not detect dependents if they are tables referenced from other sheets because I use the NavigateArrow method.
    My tool does “do” objects, so you can see all dependencies in your workbook with regards to objects. Unfortunately, this can be very slow, because Excel has no way of telling you those relationships and you have to loop through all objects to find out which point to a particular range. Imagine having thousands of conditional formatting rules, which is something that often happens…

  5. I never made any progress with the ‘Table’ object: it appears to have some properties of a named range, but I see no enumeration of ‘Tables’, and they are not a clearly-identifiable property of a sheet or workbook.

    …So how do you parse their names out of formulae?

  6. These hotkeys are also worth mentioning in this discussion:

    Ctrl+[ (opening square bracket) Select all cells directly referenced by formulas in the selection.
    Ctrl+] (closing square bracket) Select cells that contain formulas that directly reference the active cell.
    Ctrl+Shift+{ (opening brace) Select all cells directly or indirectly referenced by formulas in the selection.
    Ctrl+Shift+} (closing brace) Select cells that contain formulas that directly or indirectly reference the active cell.

    They aren’t always helpful when a formula has lots of references, but are great from jumping around a workbook for simple references.

Leave a Reply

Your email address will not be published. Required fields are marked *