Exceljet

Quick, clean, and to the point

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, 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 several faster ways to find all formulas at once.

First, you can toggle visibility of formulas on or off using the keyboard shortcut Control + grave accent. This 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 from 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 Click OK. Excel will select all cells that contain formulas.

In the status bar, we can see there are xxx cells selected.

With all formulas selected, you can easily formatting. For example,  let's add a light yellow fill. Now all cells that contain formulas 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 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 if 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 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're using use R1C1 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 is simply our named formula, CellHasFormula

Once we set the format, we'll see it applied to all 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 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 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

Course 
Core Formula

Related shortcuts

Author 
Dave Bruns