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.
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.
Using the Special option works, but it’s temporary – it’s a quick way to get a quick look. If you want to identify formula cells in a permanent way, you’ll need to do so manually, or you can use VBA. For example, add the following function to a module and run it as a macro to apply a yellow highlight to formula cells:
'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
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:
Function IdentifyFormulaCellsUsingCF(rng As Range) As Boolean
'Called from conditional formatting to determine
'if cell contains a formula.
IdentifyFormulaCellsUsingCF = rng.HasFormulaEnd Function
Then, show the user how to call the function from conditional formatting as follows (in Excel 2007 and 2010):
- 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.
When showing users how to apply this conditional format, make sure that you emphasize that they must enter the first cell in the selected range, as in =IdentifyFormulaCellsUsingCF(B3). If they enter the wrong cell reference, Excel won’t highlight the right cells.
Users will have no trouble applying formats in a familiar way using this method. (They probably won’t have the VBA expertise to update the original macro code.) The rule applies yellow highlighting just to formula cells within the selected range. This is another advantage for this method – you can control the range in which you identify formula cells. In addition, this method is dynamic. If you add a formula cell to the selected range, the conditional format will automatically kick in and highlight the cell. The original macro can’t do that for you–you’ll have to execute it each time you want to identify new formula cells.
Subscribe to the Developer Insider Newsletter
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays