15. Formula Auditing with Colorize

Subtitles Enabled


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.

Lesson Notes

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

Keyboard shortcuts

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've finished 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 plug-in, 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 plug-ins 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 plug-in 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 click 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 Okay 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 hardcoded 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 seems 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've a spreadsheet with a lot of formatting, you may want to perform the audit on a copy of the 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.