Our Excel Formula Audit tool is a part of the 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, 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 a visual format the connection between data. Therefore, 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 a given cell’s content and the connections 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, over ten or even 100 formulas can be present in a given model. So, in this case, we need a much more effective tool. And we have such a tool!
Cell Audit – How to Trace Precedents?
We’ll examine two kinds of relations regarding the Excel formula audit. First, 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 from the value of the highlighted cell? (That is a result). Exactly what cells affected the emergence of the final result?
We’ll show you the relations with the help of a straightforward 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 result is 5.
Let’s highlight the I8 cell and start the Excel formula audit tool from the ribbon.
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 a one-one cell.
Later the appearance of the “Selected Value” note warns us that originally which cell content we are analyzing. Then finally, we can find the result in cell I8, and the Excel add-in shows the detailed list’s appearance.
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! So let’s see how 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 start the Excel formula audit tool from the ribbon again.
Now click on the “Dependents” radio button! Now we are analyzing the highlighted cell (which doesn’t contain the result but has a sub-result); what role does it play in the emergence of the result?
The UserForm again shows us detailed information. According to this, the value in the G7 cell is a part of the formula in cell I8.
Advanced Excel Formula Audit
Let’s see how a more complex example where we have to gather formulas from several worksheets and audit them.
We find a calculated value in cell L8 of the “Main” sheet. But unfortunately, we can’t determine quickly, at first glance, where this value 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, the source table will immediately appear.
If the table is even more complex than the one introduced here, the program chooses between the sheets containing the data in real-time by clicking from row to row.
You can see this in the following picture.
Formula Audit Color Tool
The color tool is a little supplement to see the examined cells better.
On the UserForm, click on the “Color” menu. Then a new panel will appear.
In the list, highlight the cell we want to apply with the color code. Then, 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 are working on the DataXL add-in!
The DataXL free Excel productivity add-in is becoming “smarter” week by week; we’re installing new functions continually.
Let the following function be a secret for now. We’re making great development progress. Our final goal for the future is to realize more effective Excel data processing.
We are waiting for your questions on the subject, and we will 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!
Additional resources: