Secret: Formula Auditing as a Means of Navigating a Model

When people watch me work, they are sometimes surprised that I tend not to use the mouse.  I find that clicking on things takes time.  It slows me down.  And I find switching back and forth from keyboarding to mousing is disruptive.  So, long ago, I chose to invest the extra time in learning the keystrokes shortcuts in Excel, so that I would save time later.  They also notice that I navigate models in a funny way, with blue arrows constantly popping up and then disappearing.  These are auditing arrows.

In reviewing models, it is critical to be able to follow the flow of the calculations, both forwards and backwards.  By forwards, I mean from Assumptions to Calculations to Results.  And by backwards, I mean from Results to Calculations to Assumptions.   Excel provides a few helpful tools here.  The button for Formulas/Trace Precedents highlights which cells are used in a formula.  The Trace Dependents button shows you where any given cell is used.  Unused cells should probably be removed.  And before removing anything, you should probably check if it is being used.

Here’s an example of what clicking Trace Precedents does, when the cursor is first placed in the Interest Payment amount.

auditing

Once you’ve pushed the Trace Precedents, clicking the arrows that Excel draws will move you to the precedent cell.  I like to use this little feature to navigate around the whole spreadsheet.  I recorded the below functions then mapped them to keystroke combinations.

 

Sub TracePrecedents()
'====================================================================
' Draws a tracer arrow to the cells which are precedent to the active
' cell, then goes to the precedent. Works best if there is a single precedent.
' (mapped to Ctrl+j)
'====================================================================
 Selection.ShowPrecedents
 ActiveCell.NavigateArrow TowardPrecedent:=True, ArrowNumber:=1, _
 LinkNumber:=1
End Sub
Sub TraceDependents()
'====================================================================
' Draws a tracer arrow to the cells which are depedent to the active
' cell, then goes to the depedent. Works best if there is a single dependent.
'====================================================================
 Selection.ShowDependents
 ActiveCell.NavigateArrow TowardPrecedent:=False, ArrowNumber:=1, _
 LinkNumber:=1
End Sub
Sub RemoveArrows()
'====================================================================
' Removes all tracer arrows
'====================================================================
 ActiveSheet.ClearArrows
End Sub




Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: