Knowing where formulas are is the first step in understanding how a spreadsheet works.
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 could quickly and easily switch back and forth.
The next way you might 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 status bar, we can see the number of cells selected.
With all formulas selected, you’ve got the option to 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 useConditional Formatting.
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.
The second parameter is theINDIRECT function.
Now we can grab the range we want to work with and create a new Conditional Formatting rule.
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.
Three ways to find and highlight formulas:
1.
Go To Special > Formulas3.
Conditional formatting with GET.CELL as named formula
