There are a number of in-built Excel features for auditing your formulas. You can find them in the Formula Auditing section within the Formulas menu. They can be particularly useful if you’re trying to understand a spreadsheet you’ve inherited from someone else, or you’re returning to something you worked on a while ago.

 

Here is a brief explanation of what these features do.

 

Trace Precedents

Shows which other cells are used to form the calculation in the selected cell, so you can quickly visualise where its coming from.

 

Click Trace Precedents once to get the first set of precedents, and again if there is a second level.

 

 

 

Trace Dependents

This works the other way around, using arrows to indicate any cells which depend on the selected cell in their formulas.

 

Remove Arrows

This button removes any arrows from your previous Trace Precedents or Trace Dependents selections.

 

Evaluate Formula

Handy for longer formulas like nested IF statements, you can step through each expression within the formula one-by-one.

 

  

 

Hit Evaluate and the currently underlined expression will switch to show its result. In this case stepping in shows that H2 contains the value “Blue”. Click Step In on an expression to open a new Evaluate window for a precedent cell.

 

Watch Window

If you’re familiar with using Watches when debugging Visual Basic this works in much the same way.

 

You can select a cell to show the value of, then keep the Watch Window open and visible while continuing to work on the sheet. When the formula in a watched cell updates its value, that will be reflected in the Watch Window.