PerfectXL
Highlighter
Walkthrough

Familiar territory

Get started in familiar territoryl; the PerfectXL Highlighter can be accessed straight from Excel. Install the PerfectXL Add-in and click Open Highlighter.

In the Highlighter pane that appears, you can choose between any number of highlight modes to temporarily color cells based on various aspects.

Formulas & data

One of the most straightforward highlight modes differentiates between formula cells and data cells. Check which of these types you wish to highlight and click Highlight.

Colors

Click any of the color squares to choose a different color that better suits the existing formatting of your model. Wish to temporarily disable existing cell formats? Simply check Hide existing formatting and you’re all done.

Consistent formulas

In the Consistent Formulas mode you can quickly get an overview of the calculation model. Give every formula its own colour to do a step-by-step audit of formulas.

Wish to focus on a particular formula? Just click on it in the sidebar and it will highlight just the cells that contain the chosen formula. Click it again to bring back all formula colours.

References

In the References-mode cells are highlighted based on the cell references inside formulas. For example, the formula “=RAND()” contains no references, whereas the formula “=SUM(F5:M5)” contains a reference to within the same sheet (F5:M5). This mode helps you understand the dependency of a model without having to click through every cell. Take the example below: the pink cells contain a reference to another Excel file. The absence of this external dependency likely resulted in the #REF! errors.

Input / output

The mode Input Output is somewhat like the opposite of the References mode. Instead of considering formula references inside this worksheet, it looks at formulas everywhere to see if they reference the current worksheet. Input is defined as any cell referenced by a formula somewhere, output as the end of a formula chain. Formulas that calculate intermediate results are both in- and output. If you are only curious about the input/output within this worksheet (and not in relation to the rest of the workbook), select the option Only look in this worksheet.

Cell values

Use Cell Values to verify data types. For example, you can make sure that after a CSV import no code labels were accidentally parsed as dates. Can you trust all text cells with number-like values? Were any error cells missed?

List of highlighted cells

When not all cells are visible at once, there’s a number of things you can do: zoom out or scroll around a bit for instance. But there’s a faster way. In any highlight mode, click Show list of highlighted cells to reveal a list of all cells that have been given a colour based on the selected criteria of the mode.

Number formats

Excel’s number formats are commonly used to present data in a more readable format. Since this can include stuff like rounding numbers, it’s important to make sure such formats are applied logically and consistently. In this example the original formatting is hidden. So curiously, the Number formats mode shows us that all of column A, E and I up to row 25 are formatted as percentage, even though they are mostly empty.

Protection

When you protect a worksheet in Excel (Review > Protect Sheet), cells in this worksheet can no longer be manipulated unless they have been unlocked (right-click cell(s) > Format Cells… > Protection > uncheck Locked). This functionality is a bit hidden away in Excel, so using the Protection mode in PerfectXL Highlighter you can quickly colour unlocked cells to verify all formulas are locked and all input cells unlocked.

Special cells

The Special Cells mode is a useful spot check, a way to see “if there’s anything else you should look at”. Legacy array formulas, hidden formulas, that sort of stuff. This mode can also be useful to find particular cells: cells that have been merged together, cells whose text colour is the same as the fill colour (Invisible values), or cells that are subject to some data validation rule.

Custom highlights

The PerfectXL Highlighter can see a lot, but some models require business-specific inspection. Need to find all formulas with a SUM() function? Mark all cells that are zero? Of course you can find such things using Excel’s “Find & Select” window, or by writing meticulous conditional format rules. Yet often you just want to quickly see it at a glance. Use the Custom Search mode to accomplish this. Colour based on the presence of up to 7 different search terms and control whether they should Search in values and/or Search in formulas.