Have you ever had to reopen an old workbook – or worse, a workbook that was set up by someone else – and figure out what was going on in it, update it without being sure that you weren’t going to break some of the calculations? Of course, you may already know about the show/hide formulas in Excel tip but Excel has more useful tools.
Download the Sample Excel File
Broken Formula or Broken Workbook?
Good modelling techniques are really important when you develop spreadsheets, whether using Excel or other programmes. Training courses often focus on features rather than best practices unfortunately, propagating some really bad stuff. Some users are self-trained too, fair play, but too often that means even looser interpretations of spreadsheet basic security and integrity.
By the time I am called in to help prepare a workbook for reporting, it can be hard to figure what formulas are supposed to do in the first place and whether they do that at all. For that reason, this week we are looking at our first set of auditing tools: Trace Dependents and Trace Dependents.
Locating Formula Auditing Tools in Microsoft Excel
Excel 2003 or earlier
- Click Tools, select Formula Auditing and choose the option to display the toolbar.
It looks like this:
Newer versions of Excel (2007, 2010 & 2013)
- Click the Formulas tab and look right for the Formula Auditing group. Here they are:
Specifically today, we are focussing on these commands:
which in older Excel look like this 
So far so good, yep? Before we continue, why don’t you download the sample file?
Download the Sample Excel File
Trace Dependents
In the example below, before changing the rate (23%) in J5, you might want to figure out which calculations will be impacted. Hopefully you can guess by looking at this one, it’s rather simple but let’s see how Trace Dependents can helps us audit the workbook.
To find out which cells depend on the rate in J5
- Select cell J5 which contains the rate.
- Click Trace Dependents. Blue arrows appear which indicate formulas which use this reference.
- Click the same Trace Dependents again and again to reveal the sequence of formulas using this rate. I thought an animation might help… You’re welcome.
That was alright, wasn’t it? There’s a link to the sample file at the end of the article so you can try that yourself.
Right now, continue to the next page to see the other commands in that group and how you can make this work across multiple workbooks… Thrilling? Yep, I think so too.
Trace Precedents
Trace Precedents does the same job of highlighting cells which are linked through formulas but, as you may have guessed by now, it starts from a formula and shows cells which ‘feed into’ the formula. Ìn this example, let’s start by selecting the formula in L14 and click Trace Precedents twice to go back two levels. This is what we get:
Remove Arrows
Click Remove Arrows to, well, remove arrows all at once or look in the command dropdown to specify if you want to remove precedent or dependent arrows only.
Note that arrows disappear as soon as you save the workbook anyway or anytime you close the workbook. They are meant to help you as you troubleshoot formulas but not meant as ways of documenting your spreadsheet.
OK, that was easy, wasn’t it? Next we’ll explore what happens when formulas link to external sheets or workbooks.
Tracing References to External Sheets and External Workbooks
How would that work if formulas used references across multiple sheets or external workbooks? I’m glad you asked. Kinda the same but different.Notice the dashed arrow line which is displayed after you use the Trace option:
- Double-click the dashed arrow line.
- The Go To dialog appears which lists all linked references. Highlight one of the references (there is only one here) and click OK.
- Now Excel has’taken you’ to that reference.
Try it yourself on one of your spreadsheets or download today’s sample file. Next time, we will explore other ways you can troubleshoot your Excel workbooks. Comment, Share, Like or Tweet – I bet you know someone else who uses Excel.
Download the sample file:
Download the Sample Excel File
