Formula Auditing

Excel functions are pretty much the backbone of using excel…

through functions, you can accomplish what excel is made for - manipulating your data.

But what happens when your formulas cause you trouble?

Thankfully…there’s a whole section of the formula ribbon devoted to that.

The Formula Auditing Section

The formula auditing section of the Formulas ribbon is what it sounds like…

it’s where you can “audit” your formulas anytime that you have an error, or need to do a deep dive on a cell.

Trace Precedents, Trace Dependents, and Remove Arrows

The first few buttons of the Formula Auditing section allow you to evaluate which cells affect, or depend, on a specific cell or range

Trace Precedents

Here, you’ll get insights into how exactly a cells value was derived

this is similar to when you double click into a cell, or hit F2…

but you generally get more details in this view, and can understand what cells in other tabs / workbooks are affecting this cell.

Trace Dependents

Trace dependents is similar to Trace Precedents, only it’s the opposite.

Here, you can understand what cells depend on this cell

This is especially useful whenever you are considering deleting a cell, and want to make sure that other formulas won’t get tripped up

Remove Arrows

This one’s simple…

Once you’re done analyzing precedents / dependents, you can simply hit the remove arrows button to get back to a clean view

Show Formulas

Show formulas is a neat tool for getting a quick view into all of the formulas in your spreadsheet.

This is helpful if you inherit a file from someone else, and you want to avoid clicking into each cell one by one

A popular shortcut here is to press CTRL + `

Error Checking & Circular references

The Error Checking button will open up a dialogue box, providing more context on an error in your spreadsheet

You can also trace an error to see which cells are contributing to the error.

Sometimes…you may run into this notorious thing called a Circular Reference 😱

This happens whenever you have a cell that refers to itself.

When that happens…generally…excel will kind of act drunk 🥴.

You may see some weird results from your calculations until the circular reference is fixed.

At the same time…there may be some instances where you actually want a circular reference.

You can allow for that by navigating to File > Options > Formulas > Enable iterative Calculations

Here, Excel will iterate through your formula after 100 iterations, or after all values in the circular reference changes by less than .001 between iterations, whichever comes first

Evaluate formula

I love using this one…especially once I have a really long formula

Here, you can step into a formula, and see the results as excel goes through the process one by one

This makes it easier to see where exactly you ran into an issue with your formula

As we learned from Spiderman…with great power, comes great responsibility.

Your formulas can be your best friend…or your worst nightmare 😨

With the right error handling & auditing procedures in place, you can sleep more safely at night knowing that your spreadsheet is in good hands