Formula auditing techniques!

 

To Err is to Human and to correct that err is to audit your formulas 😆 Jokes apart, today lets talk about how can we effectively audit our formulas in different ways. I have 5 smart tricks which you can use for various needs! Let’s begin

Auditing with the F2 Key

This is the most basic type of auditing but works a treat. All you need to do is to press the F2 key to see which cells are linked to your formulas (linking is shown by color coding). The formula bar although shows your formulas but one can’t really make a head and tail out of it (since it shows no linking)

 

Auditing with the F5 Key

Now the next level comes when you have the formula linked to a cell on a different sheet, the linked cells don’t show up when you press the F2 Key. Here the F5 key comes to rescue. Here is how it works

1. Let’s say we have total 3 numbers
2. Two of the three numbers are kept on Sheet 1 and the 3rd number is kept on Sheet 2
3. We can select the 3rd number in the formula and press the F5 key
4. It will take you to the Goto dialogue box. Press Ok and it leads to the referenced cell in the formula

The benefit of this is that you can edit the references in the formula easily

 

Auditing with the F9 Key

What if you have the need to calculate a part of the formula? Here the F9 (calculation) key comes really handy. Here we have to calculate tax @ 10% on the sum of 3 products

1. Select the part of the formula you want to calculate
2. Press F9
3. Caution: If you press enter after you have calculated the formula, the values are no more referred to the cells they become absolute numbers. You must press ESC to retain the cell references

 

Auditing Step by Step with Formula Evaluator

Often you have large formula and you need to crack it down step by step. You can do a formula evaluation in that case. Just for our understanding of how it works, let’s try it over a smaller formula. The Formula Evaluator is placed in the Formulas Tab

1. Select the cell where you want to run the formula evaluator
2. Click on Evaluate formula in the Formulas Tab
3. It will open up the formula evaluation tab
4. Click on Evaluate button to see how the calculation works

 

Tracing Precedents and Dependents

One of the other needs is to see which all cells are being linked to a cell (called as tracing dependents) or checking the formula is made up of which cells (called trace precedents)

A quick case : Here we want to check which cells are linked to the tax of 10% (Trace Dependents)

1. Click on Trace Dependents in the Formula Tab, to show which cells are dependent on the cell selected
2. It will place arrows on the cells which are linked
3. You can clear the arrows by Remove Arrows button
4. In the similar way you can also Trace Precedents

 

How do you audit your formulas?

Please share using comments the best ways to audit your formulas