Visually tracing all precedents in Excel without add-ins

There are numerous tools and add-ins in the market constructed to enhance the ability to critically review formulas in Excel. Many of these tools build on the native functionality of Excel – trace dependents and trace precedents.

Mastering the native Excel tracking functions can make a big difference if you are not privileged to purchase one of the many tools (which I would recommend you to though if you are in the professional financial modelling space).

‘Standard’ trace precedents functionality

Most people know about the Formula Auditing toolbar and how to use it. It can be found in View -> Toolbars

trace-1

The Trace Precedents functionality highlights with blue lines which cells have been directly used to calculate the active cell.

trace-2

If you press the Trace Dependents multiple time then you get to see the precious level of precedents.

trace-3

This can of course be quite useful but it can sometimes be very slow as a large financial model for a complex project may have 10, 20 or even 100 levels of precedents for a high level output such as NPV or IRR.

Find anomalies (spreadsheet errors) with Trace ALL Precedents

In a small calculation module like the one above it is pretty easy to find errors, but when looking at larger spreadsheets we need to make this process more efficient. In a formal financial model audit the independent review process includes a cell-by-cell review of every unique cell in the financial model but when working internally there may not be enough time for this process to be completed.

The original example could be expanded to an example with five orders and a calculated total as per below:

trace-4

If this had been coded correctly then every order section should have the exact same structure as Order 1 in the original example.

One way of quickly identifying structural problems in an Excel section (the method doesn’t work on off-sheet references) is to us the Trace All Precedents functionality.

Trace All Precedents – CTRL + SHIFT + [

The Trace All Precedents functionality can be used via the Go To -> Special -> Precedents -> All Levels, but it is far too slow to be useful. The only way to use this efficiently is to use the Excel shortcut CTRL + SHIFT + [

trace-5

In order for this to work you need to select a high level summary cell that is based on as much of the section you are reviewing as possible. In our example this cell is the ‘Total Order Value’ – the yellow cell with the value 1,950.

Select the yellow cell and press CTRL + SHIFT + [ and you get the following results:

trace-6

By visual inspection we can now identify that the calculation patterns or Orders 3 and 4 are different to the others. Order 4 appears to be excluded altogether and all the assumptions of Order 3 are not used.

Based upon the visual inspection we can progress with a more detailed spreadsheet review, but focus on the identified areas alone. Tracing Precedents (the traditional way) yields the following result:

trace-7

The Excel calculation errors can be identifies as

  1. The Total Order Value sum does not include all Order Values
  2. The Order Value 3 (b) is using the Volume of Order Value 3 (a)

Limitations of the Trace All Precedents functionality in Excel

This spreadsheet review functionality is very powerful if used in the right context but it is also very important to know the limitations.

  1. It doesn’t follow links to other sheets or workbooks
  2. It can only be used to detect structural problems and to specific calculation errors. A hardcoded number within a formula would remain undetected until additional methods are used
  3. It works best when used in areas where there are section summaries available (which is the case in most well-built financial models) as the reviewed are may otherwise be too large to inspect by the visual method.

Excel trace functionality – further techniques

If you have found this useful then you should definitely review the Trace All Dependents functionality and the Row Differences and Column Differences tricks (They are all available from the Go To menu but it is by using the Excel shortcuts you can make this really efficient.

The Keyboard Shortcuts are available in this Excel Keyboard Shortcuts summary from Navigator Project Finance.

Recent posts by Rickard Wärnelid

Tags: ,

Comments for “Visually tracing all precedents in Excel without add-ins”

  1. Great tip!

    I personally use a tool called ‘Formula Navigator’ by Spreadsheet Guys for tracing formula and find it to be very good.

    It can be downloaded here:
    http://www.spreadsheetguys.com/downloads/software/formulanavigator

    Regards,

    Blake McNaughton

  2. Hi Blake,

    I have not tested this one before – I suppose it is similar to BPM’s bpmTraverse?

    http://bpmglobal.com/bpmtravcamp/bpmtraverse.html

    BPM’s tool is free and does a good job for this. What are the benefits of the ‘Formula Navigator’ over bpmTraverse? The $9.95 isn’t a staggering amount but would be good to get your views on this.

  3. Blake McNaughton says:

    Hi Richard,

    Yes I have used BPM’s free traverse tool before and Formula Navigator is quite similar, though far better in my opinion and worth the small price tag.

    Formula Navigator has some great additional features:
    – Sheet navigation buttons which allow you to select a range or multiple ranges and jump to the exact same range/s in adjacent sheets (great for de-bugging or auditing asset portfolio models where there is a silo or template sheet structure)
    – A history window to keep track of what cells you’ve been looking at in the spreadsheet.
    – A ‘Study Range’ feature which enhances the in-built trace functionality of Excel by allowing you to trace on a range rather than just a single cell (very powerful)

    I also find it’s general useability to be better suited to my needs and I like the Re-sizeable floating window!

    Blake.

  4. Rickard Wärnelid says:

    Hi Blake,

    What is your relation to the SpreadsheetGuys? In the interest of getting unbiased comments it would be good for people to know your background as a bit of research on google suggests that you may not be as independent as your previous comments suggest.

    It may still be worth looking into the ‘Formula Navigator’ to see how it stacks up against bpmTraverse. Having the best available spreadsheet review tools is critical and we are constantly looking out for better solutions.

    Rickard

  5. Hi Rickard,

    I designed ‘Formula Navigator’ with a colleague after I left the financial modelling team at one of the Big 4 accounting firms.

    One could call me bias but I still think ‘Formula Navigator’ is a very useful add-in for model builders and auditors needing to trace precedents and dependents in Excel.

    There is a free 7 day trial and I’d be interest to get your feedback.

    Blake.

  6. Chris McNeill says:

    Tools are an absolute must for efficient and risk-reduced model review. I developed quite a suite of labour saving devices to trawl through repetitive tasks and report on exception.

    A decent navigation tool is a must, may I respectfully point out a shortcoming that the above tools appear to have – they don’t handle indirects, and only BPM sort of handles offsets. What I mean by this is that they don’t locate the “true” reference. They seem to rely on the precedents and dependents. I have successfully developed one that isolates any indirects or offsets to their “true” reference because my review team needed this.

  7. Hi Chris, having seen you tool demonstrated I must say that it is pretty powerful. A practical consideration for most people though is that they neither have the skills, time or ambitions to build their own tools. Maybe you could sell you product as an ‘add-on’ to any of the other model review tools?

Comment on this Article