Excel – Using the Formula Auditing Tools
Once you’ve created a spreadsheet or you get one from someone else, one of the more painful things you end up doing at some point is trying to work out which cells feed into any of the formulas throughout the workbook or alternatively, which cells are dependent on any given cell(s).
Finding which cells feed into a single formula is easy, just by clicking on the formula cell which then highlights all the cells that feed into it.
However, this is very limited as you can only check out one formula at a time and there is no way of knowing if any of the cells that feed into your formula are in turn dependent on other cells for their values short of checking each and every cell which of course no sane person would do.
So how can you check this without spending hours looking at all the cells in your worksheet/workbook?
This is where the FORMULA AUDITING tools come in. On your FORMULAS tab you will find the FORMULA AUDITING group.
In this blog I am going to concentrate on TRACE PRECEDENTS and TRACE DEPENDENTS, ERROR CHECKING and will write separately about EVALUATE FORMULA.
I will briefly mention SHOW FORMULAS before starting on the main topics of this blog. As the name suggests, this will convert all cells with formulas on the current worksheet from displaying their values to displaying the formulas contained within them.
You can achieve the same effect using CTRL + ` (on an English – UK QWERTY keyboard). It’s a quick way of viewing all formulas on a worksheet without the need to view each cell separately. Can’t say any more than that!
So how do you find out which cells feed from or into another cell?
Let’s start by finding out which cells supply a formula cell with its inputs. Click on any cell containing a formula and then click on TRACE PRECEDENTS.
Any cell(s) that provide data to your formula should now be highlighted. But do any cells feed those before they feed into the formula cell? Simply click the TRACE PRECEDENTS button again.
Keep on clicking until it can go no further i.e. you have reached the original source to all the data feeding into your formula.
Now that you’ve found out all the cells that feed into your formula, is the result of your formula used anywhere else. In other words, are cells DEPENDENT on this one?
Staying on the same formula cell, now click on TRACE DEPENDENTS.
As with TRACE PRECEDENTS if you continue to click the TRACE DEPENDENTS button you will eventually get to the final cell in the chain that is influenced, however remotely, by the currently selected cell.
There will be times when you check for PRECEDENTS or DEPENDENTS and you will see this;
This tells you that data from an external source is feeding into the cell. This can mean from another worksheet in the same workbook, or from another workbook altogether. Double click the dashed line and it will open the GO TO dialog box.
This will show you the source of the data and if you click on the reference and click on OK, it will take you the feeder cell if it’s in the same workbook, or if the other workbook is open at the time.
There comes a point where there are too many lines/arrows to follow. To remove any lines/arrows click on REMOVE ARROWS to remove all visible lines/arrows, or select REMOVE PRECEDENT ARROWS or REMOVE DEPENDENT ARROWS depending on what you want clear off screen.
Alternatively, you can trace precedent and dependent cells by using CTRL + [and CTRL + ] respectively which simply highlights any cell(s) that either precede of follow the currently highlighted cell.
If your formulas produce error messages such as #DIV/0! And you have eliminated syntax errors, it could be that one of the feeder cells somewhere in the chain of cells has an error. Use ERROR CHECKING to find and correct those errors.
Click on TRACE ERROR to show all cells feeding into the formula producing the error. This works just like TRACE PRECEDENTS showing any external links.
For more information select ERROR CHECKING. This will show you where the error is and what type of error it is. You then have access to a number of options such as EDIT IN FORMULA BAR or IGNORE ERROR in case the problem is simply due to incomplete or missing data which will correct itself once it is updated.
So next time you are not sure where a formula gets its inputs from, or you need to trace an error you can now use your AUDITING TOOLS to help you out a bit.
Posted on March 31, 2015, in Auditing, Functions & Formulas and tagged error checking, formula auditing, msexcel, show formulas, trace dependents, trace precedents. Bookmark the permalink. Leave a comment.