15. Formula Auditing with Colorize
Colorizing plug-ins such as XLtest can help you audit your formulas much more efficiently than by solely using traces. In this lesson, we'll colorize our model to find and fix some formula errors.
Formula auditing with colorize
- Using a colorizing plug-in such as XLtest can improve your formula auditing process
- Link to XLtest - www.sysmod.com/xltest
- Make sure to run you colorizing plug-in on a copy of your spreadsheet
- The formatting from a colorizing plug-in can't be undone
ALT + T , U , T: Trace precedents
ALT + T , U , A: Remove all traces
CTRL + →: Move to the last cell in the data region
When you finish building your model in Excel it's important to check your spreadsheet for formula errors.
In Excel foundations, we use some formula auditing tools such as Traces to check our formulas.
When building large models, however, cell by cell auditing with traces can take a lot of time especially when we have inputs on a separate sheet to the model itself.
A better way of conducting formula audits is through a colorized plugin such as XLTest.
If cells share the same formula, then XLTest simply formats these cells with the same background color.
This makes formula errors much easier to identify.
There are many plugins out there that perform a similar function, but I like XLTest because their free product is easy to use and you don't need to install the plugin to use it.
So let's go to the XLTest website and download the free version.
When the file has downloaded, I'll open up my downloads folder and double-clik on the zipped folder Within the folder, I have two Excel add-in files.
I'll select the larger file which is .xlam which is the right version.
When I double-click, it asks me to enable macros, which I will and then it asks me to install the add-in.
If I click No, the add-in will only be available for this current session and that's what I'll select.
When I click No, I can now go to the Review tab and click Colorize formulas.
When I do this, it asks me for permission to change the formatting on my spreadsheet.
I'll press OK, and it colorizes all of our formulas.
When cells are formatted like this, it's pretty easy to spot a discrepancy.
In the top row, we can see that the 2014 value is a different color to every other cell in the row.
And the difference is that the 2014 value is hard coded and the remaining rows are simply a formula that add one to the previous year.
We may want to change the formatting of the 2014 cell so that it's seen as a hardcoded number.
If we now go to the end of the dataset, we can see another discrepancy where the production cost formula does not extend out to the last 2026 column.
This is a common modeling error but thankfully, XLTest makes it very easy to spot.
To fix this error, I'll just copy the formula from the adjacent cell and Alt + E, S, F to paste formulas.
If I now press Colorize Formulas again, you can see that the error has been rectified.
One drawback with Colorize is that the formatting can't be undone.
If you have a spreadsheet with a lot of formatting, you may want to perform the audit on a copy of this spreadsheet and then replicate the changes in the master file afterward.
For more complex models, colorizing formulas almost becomes essential, because it makes identifying errors much easier than cell by cell tracing.