Learn how to identify formula cells the easy way using VBA, and then combine VBA and conditional formatting for more permanent identification.
If you practice good spreadsheet design, formulas shouldn't be hard to find. That's a nice thought, but it's not terribly practical. You might be working in a legacy workbook that you didn't design or perhaps the sheet contains a lot of data. Fortunately, there are two quick ways to find formula cells: [F5] and VBA.Using [F5] Pressing [F5] is the easiest way to identify formula cells. Doing so actually selects the cells, so it's temporary. To use this method, do the following:
- Press [F5].
- Click Special.
- Select Formulas. By default, this option selects all formula cells in the current sheet, but you can be more selective by excluding specific suboptions: Number, Text, Logical, and Errors.
- Click OK and Excel selects cells that contain formulas.
Sub IdentifyFormulaCells()You can call a similar function from a conditional format. This method is probably more appropriate if you want to offer users a method they can apply themselves. In this case, add the following function to a module:
'Apply yellow highlight to all formula cells.
Dim ws As Worksheet
Dim rng As Range
Set ws = ActiveSheet
For Each rng In ws.Cells.SpecialCells(xlCellTypeFormulas)
rng.Interior.ColorIndex = 36
Next rngEnd Sub
Function IdentifyFormulaCellsUsingCF(rng As Range) As BooleanThen, show the user how to call the function from conditional formatting as follows (in Excel 2007 and 2010):
'Called from conditional formatting to determine
'if cell contains a formula.
IdentifyFormulaCellsUsingCF = rng.HasFormulaEnd Function
- Select the range where you want to identify formula cells. This can be the entire sheet or a simple data range. In the case of the example sheet, you might select cells B3:E11.
- Click the Home tab.
- Click the Conditional Formatting dropdown in the Styles group.
- Choose New Rule.
- In the resulting dialog box, select the Use A Formula To Determine Which Cells To Format option.
- In the Format Values Where The Formula Is True control, enter the following expression: =IdentifyFormulaCellsUsingCF(B3).
- Click Format.
- On the Fill tab, click Yellow.
- Click OK twice.
In Excel 2003, do the following:
- Select cells B3:E11.
- Choose Conditional Formatting from the Format toolbar.
- Choose Formula Is from the Condition 1 dropdown.
- Enter the expression =IdentifyFormulaCellsUsingCF(B3).
- Click Format.
- From the Patterns tab, select yellow and then click OK twice.