Our Excel Formula Audit tool is a part of free DataXL automation add-in.
The Excel default formula audit tool caters only to the basic functions. If we work on large tables and many formulas then we have to find a much more effective solution.
This is why we created the Excel formula audit add-in. We endeavored to keep the user experience in mind.
The goal of the Excel productivity tool is to portray in visual format the connection between data. Please read through today’s lesson because you will become richer with useful information!
Let’s talk a little bit about the process of Excel formula audit. Soon we’ll analyze the content of a given cell and also the connection of the formulas therein.
If we only use a few formulas then the Excel built-in tool is just suitable.
But during a day’s work there can be over ten even 100 formulas be present in a given model. In this case we need a much more effective tool. And we have such tool!
Cell Audit – How to Trace Precedents?
We’ll examine two kinds of relations regarding the Excel formula audit. Let’s highlight a cell containing a formula!
What may be the relation between a given cell and other formulas?
The question is this: what formulas resulted the value of the highlighted cell? (That is actually an end result). Exactly what cells affected the emergence of the final result?
We’ll show you the relations with the help of a very simple example. The reason for this is that this way you will understand the operation of the functions very quickly.
On the picture in the I8 cell we have written the following formula: 100/200*10, namely G6/G7*G10. The end result is 5.
Let’s highlight the I8 cell then from the ribbon start the Excel formula audit tool.
After this, click on the “Precedents” radio button! We can find much useful information on the appearing UserForm. You can see four lines here. Every single line tallies the value of one-one cell.
Later the appearance of the “Selected Value” note warns us that originally which cell content we are analyzing. We can find the end result in cell I8, and the appearance of the detailed list is done by the Excel add-in.
Don’t forget what we have mentioned at the beginning of the article: the Excel formula audit tool can be applied not only 4 but even in the case of 100+ cells! The formulas can be on several worksheets and even in several workbooks. We can connect them easily.
Cell Audit – How to Trace Dependents?
Ok, the precious example proved to be useful! Let’s see now the opposite case. We are curious what kind of relations does the G7 cell (its actual value is 200) has with the other values?
Highlight the G7 cell and again start the Excel formula audit tool from the ribbon.
Now click on the “Dependents” radio button! Now we are analyzing that the highlighted cell (which doesn’t contain the end result, but has a sub-result) what kind of role it played in the emergence of the end result?
The UserForm again shows us the detailed information. According to this the value in the G7 cell is a part of the formula in the cell I8.
Advanced Excel Formula Audit
Let’s see now a more complex example where we have to gather formulas from several worksheets and audit them.
In the cell L8 of the “Main” sheet we find a calculated value. We can’t really determine quickly at first glance where this value is originated from.
Look at the detailed list! We marked it with a red square for better understandability. The source of the data is the “data_2016” sheet. Of this we are also informed visually.
One of the most useful characteristics of the Excel formula audit tool is the visual formula trace.
The operation of the visual formula trace is as follows. If we click from the currently active row to the next one then the source table will immediately appear.
If the table even more complex than the one introduced here, than by clicking from row to row the program choses in real time between the sheets containing the data.
You can see this on the next picture.
Formula Audit Color Tool
The color tool is just a little supplement in order to be able to see better the examined cells.
On the UserForm click on the “Color” menu. Then a new panel will appear.
In the list highlight the cell that we want to apply with color code. With the help of the “New Color” choose the desired color.
And this is all! Of course we can apply color code to several cells.
We work on DataXL add-in!
The DataXL free Excel productivity add-in is becoming “smarter” week by week; we’re installing new functions continually.
Let the next function be a secret for now. We’re making great development progress. Our final goal for the future is the realization of more effective Excel data processing.
For now we wait for your questions in the subject and we try to answer them as quickly as possible. Thank you for choosing the Excel formula audit tool!
Update: we’ve improved our productivity excel add-in, check the new version!