PerfectXL Highlighter

Walkthrough

On this page you will find a step-by-step walkthrough of the PerfectXL Highlighter. Here we show you what each mode does and how you can use the highlighter to build better spreadsheets.

Getting started

The modes

To get started just select the PerfectXL Highlighter icon from the Home tab in Excel. Here you will go straight into the general mode and you’ll see what is going to be highlighted and which colors your selected cells will get. You can adjust the selection here with the checkboxes and change the colors. A Clear-button appears whenever there is highlighter formatting present.

Note: The first time you open the PerfectXL Highlighter it will show a welcome-overlay. Just click “Get started” and you’ll be on your way!

When you click the green title with the arrow under “PerfectXL Highlighter” you open the mode selection pane. From here you can easily choose a different mode, which allows you to highlight different cell aspects on the sheet. Continue reading to find out what different modes have to offer.

General

The General-mode opens automatically when you first activate the PerfectXL Highlighter. This mode is free to use, for the other modes you will need to buy and activate a license. In the General-mode you can highlight formulas, single references (e.g. ‘=A1’), and data cells. This lets you easily spot hard-coded values in a row of formulas, and also gives a lot of information about the structure of a worksheet.

Consistent Formulas

In “Consistent formulas” mode you can instantly see the formula layout of the sheet: where formula series are broken up, reused, and whether they show suspicious patterns. You can also select one individual formula to visually isolate it in the sheet. This mode is also perfect for validations in which you have to validate each unique formula.

References

The References-mode shows the user the scope of each cell. Cells are colored based on whether their formulas contain cell references within the worksheet or workbook, or even whether they refer other workbooks. Use this mode to see where information processed on the sheet is coming from, and quickly spot any external connections that should also be properly validated. Also good to note: a formula without references (shown in orange above) generally contains hardcoded values, so make sure to double check those.

Input & Output

As a follow-up on the References-mode, the “Input Output”-mode shows you whether or not the cells you see are used somewhere else in the workbook. Input cells are cells whose values are referenced by formulas; output cells are not referenced again in the model. An additional highlight option is available here: “Only look in this worksheet”. If you select this, formulas outside the current worksheet will not be taken into account. It’s a little less informative, but performs a lot quicker.

Cell Values

The “Cell Values”-mode gives cells a color based on the type of the cell value, regardless of formatting. For example, even if a text is formatted as something different, it will be highlighted as “Text cells”. This gives you an idea of what sort of data is on the worksheet. Easily find those pesky Excel errors and verify that when a column looks like it contains numbers, it actually contains numbers. Tip: for big worksheets, use the “Show list of highlighted cells” option to spot error cells that might not be in view.

Number Format

The “Number Format”-mode highlights cells not based on the type of the value, but on the way it is formatted using Excel’s number format feature. So even empty cells get colored if they are formatted. This is a quick way to take a critical look at your formats, especially for a dashboard/reporting sheet. Make sure your currencies are really currencies and be careful with percentages!

Protection

The Protection-mode highlights two kinds of cells: unlocked cells with and without a formula. It looks simple, but don’t underestimate its value. Unlocked cells can still be edited even on a protected sheet. Quickly spot unlocked cells and make sure they should be open-access to all users of the spreadsheet.

Special Cells

The “Special cells”-mode highlights cells with special characteristics, such as when they are merged together with other cells, when they contains legacy array formulas, or when they are subject to Excel’s data validations. It’s not to say that any of these cells are good or bad, but simply letting you know they are there can help you make mindful decisions about them. The one exception here is merged cells, don’t use them! Use this mode to spot and un-merge any merged cells you find.

Custom Search

With the mode “Custom Search”, you can highlight cells based on custom search terms. This is essentially the upgrade to Ctrl+F. It can be really helpful in finding certain information on your sheet. This mode can be used to modernize your sheets. For example, you could search for VLOOKUP and replace results with XLOOKUP functions instead, read more about XLOOKUP here.

How can PerfectXL help you?

Share your questions with us, we are more than happy to help you. We will get back to you within 48 hours.