How to find and highlight formulas

In this video, we're going to look at three ways to find formulas in a worksheet. Knowing where formulas are is the first step in understanding how a spreadsheet works.

When you first open a worksheet you didn't create yourself, it may not be clear exactly where the formulas are.

Of course, you can just start selecting cells, while watching the formula bar, but there are several faster ways to find all formulas at once.

First, you can toggle the visibility of formulas on or off using the keyboard shortcut Control + Grave Accent. This key is just below the Escape key on US keyboards. This shortcut will cause Excel to display the formulas themselves instead of their results. Using this shortcut, you can quickly and easily switch back and forth.

The next way you can find all formulas is to use Go To Special. Go To Special is based on the Go To Dialog. The fastest way to open this dialog box is to use the keyboard shortcut Control-G. This works on both Windows and Mac platforms.

In the Go To dialog box, click Special, select Formulas, and then click OK. Excel will select all cells that contain formulas.

In the status bar, we can see the number of cells selected.

With all formulas selected, you can easily apply formatting. For example, let's add a light yellow fill. Now all cells that contain formulas are marked for easy reference.

To clear this formatting. We can just reverse the process.

[go to special, clear formatting]

A third way to visually highlight formulas is to use Conditional Formatting. Excel 2013 includes a new formula called ISFORMULA(), which makes for a very simple Conditional Formatting rule, but that won't work in older versions of Excel.

Instead, we'll use a function called GET.CELL(), which is part of the XLM Macro language that preceded VBA. Unfortunately, GET.CELL can't be used directly in a worksheet. However, by using a named formula, we can work around this problem.

First, we create a new name called CellHasFormula. For the reference, we use the GET.CELL function like so:

=GET.CELL(48,INDIRECT("rc",FALSE))

The first argument—48—tells GET.CELL to return TRUE if a cell contains a formula. The second parameter is the INDIRECT function. In this case, "rc" means current row and column, and FALSE tells INDIRECT that we are using R1C1 style references instead of A1 style references.

Now we can select the range we want to work with and create a new Conditional Formatting rule. We want to use a formula to control formatting, and the formula we use is simply our named formula "CellHasFormula."

Once we set the format, we'll see it applied to the cells that contain formulas. Because the highlighting is applied with Conditional Formatting, it's fully dynamic. If we add a new formula, we'll see it highlighted too.

One advantage to using Conditional Formatting is that the actual formatting of the cells is not affected.  To stop highlighting formulas, simply delete the rule.

The next time you inherit a new workbook, try one of these 3 methods to quickly and easily find all formulas.

Three ways to find and highlight formulas:

1. Toggle Formulas with Control + `
2. Go To Special > Formulas
3. Conditional formatting with GET.CELL as named formula