Software

Identify which cells in Excel are formula cells

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:

  1. Press [F5].
  2. Click Special.
  3. 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.
  4. Click OK and Excel selects cells that contain formulas.

Using VBA 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:
Sub IdentifyFormulaCells()

'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 rng

End 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.HasFormula

End Function
Then, show the user how to call the function from conditional formatting as follows (in Excel 2007 and 2010):

  1. 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.
  2. Click the Home tab.
  3. Click the Conditional Formatting dropdown in the Styles group.
  4. Choose New Rule.
  5. In the resulting dialog box, select the Use A Formula To Determine Which Cells To Format option.
  6. In the Format Values Where The Formula Is True control, enter the following expression: =IdentifyFormulaCellsUsingCF(B3).
  7. Click Format.
  8. On the Fill tab, click Yellow.
  9. Click OK twice.

In Excel 2003, do the following:

  1. Select cells B3:E11.
  2. Choose Conditional Formatting from the Format toolbar.
  3. Choose Formula Is from the Condition 1 dropdown.
  4. Enter the expression =IdentifyFormulaCellsUsingCF(B3).
  5. Click Format.
  6. 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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

5 comments
JanTijssen
JanTijssen

Found this solution. Great. Should work, but can't get is working somehow. Macro is nog problem. Works! It is about the function..... I consider myself as an "almost expert on Excel" (VBA level is average...) and can't find the problem.

ppg
ppg

Once you have used F5 to select the cells with formulae you can just use format to highlight or otherwise mark the cells. In most cases that will be faster than setting up a macro (and this from someone who lves writing macros)

jbenton
jbenton

I define the name "CellHasFormula" as "=GET.CELL(48,INDIRECT("rc",FALSE))" then I can use conditional formatting to dynamically highlight any cells for which CellHasFormula is true (works in 2003 at least)

jbenton
jbenton

...that's not dynamic

d_benfield
d_benfield

Also works in Excel 2010. I did as described in a couple of the posts and I've included the details below in case someone needs it... - highlight the area to identify cells containing formulas - Formulas tab - define name - New name dialog - Name : CellHasFormula - Scope : Workbook - Refers to : =GET.CELL(48,INDIRECT("rc",FALSE)) - close the dialog - Make sure the area is still selected then Home tab, Conditional Formatting, New Rule - New Formatting Rule dialog - Select "Use a formula to determine which cells to format" option - "Format values where this formula is true" : "=CellHasFormula" - Set the desired format - I just used a (boring) grey fill And that's it. Hope that helps.

Editor's Picks