Improving the Trace Precedents Experience

Hi all,

I’ve been working on my RefTreeAnalyser in the past weeks. One thing I’ve been working on is improving the not-so-intuitive way Excel displays Precedents using arrows, especially if a cell has mutliple off-sheet precedents:

Excel's way to show precedents

Notice that silly goto dialog (which you get when you double-click the off-sheet arrow with the tiny “table” icon next to it)?
Prize question: which worksheets and ranges are the entries in the Goto listbox pointing to?

I redesigned that “experience” to this (I manually added the red call-outs to this screenshot to explain what is what):

My way to show Precedents

Note how off-sheet precedents are represented by a picture of (part of) the range in question. A nice way to enable you to see what the precedent values are.

The boxes contain a hyperlink to that range so a simple click takes you there for further inspection. On hover with your mouse you’ll get the precedent’s source address in a tooltip.

If you like this idea, why not head over to my site and download the demo version so you can try it and shoot some comments at me?

There is one snag: the new feature only works for Excel 2007 and up.


Jan Karel Pieterse

15 thoughts on “Improving the Trace Precedents Experience

  1. That is just awesome. Why just give a picture for off-sheet precedents? Why not on-sheet ones to? Often they are out of the current window.

  2. I would settle for being able to resize the original window so I can see all of the text. This is awesome.

  3. Great idea.

    I get a “Compile error in hidden module: modVisualizeTree” however. It might have to do with some international settings. I run my Excel in English, but there might be some french here and there.


  4. What else might be useful is if you superimpose the text of the referring formula in that picture. Sometimes a formula is going to say more than a picture of a block of text. Sometimes not.

  5. Great! I’ve been dreaming of this.
    Very interesting to have the surrounding range visible, but the “refersto” cell/range also need to be highlighted within that range.
    Ps:I have the demo version but I am not able to see the new “experience”…

  6. @DavidC: I expect you’re using Excel 2003? I’ve just updated the tool today to make that functionality available to 2003 as well.
    The picture the tool shows *is* the referred to range itself, so I guess it is already highlighted.

  7. @Jeff: I thought about showing formulas, but if a formula is long, it sort of becomes rather useless.
    Or perhaps you just mean the reference address itself? that is shown by a tooltip already.

  8. @jkpieterse : I’m running Excel 2007 SP2. Clicking “visualize” with no effect (apart from hiding the arrows if they have been created by Excel – ie not the add-in)… Am I doing something wrong? Thanks!

  9. @jkpieterse : or I get a :runtime error ’13’ type mismatch. And the first two rectangles are drawn (the pruple + the first green but no arrow).

  10. Hi Jan,

    I am using the demo. I use the shortcut Ctrl+? to visualize, and an error pops saying “No cells were found”.

    When I use the Visualize button on the same cell, it provides the arrow. Are shortcuts active in the demo?



Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.