Create an Excel function to highlight formula cells in a worksheet

With the help of a simple function, Excel's Conditional Formatting feature can save you time and effort. Here's an easy way to flag formulas in your worksheets.

Do you often need to check the formulas in a worksheet? Rather than scan each cell individually, create a function that will mark the cells you need to check. Follow these steps:

  1. Open the worksheet containing those formulas and press Alt + F11
  2. Go to Insert | Module.
  3. Enter the following function (Figure A):

Function HF(range) As Boolean

HF = range.HasFormula

End Function

Figure A

  1. Press Alt + Q.
  2. Go to Format | Conditional Formatting. In Excel 2007, click Conditional Formatting on the Home tab.
  3. Choose Formula Is from the Condition 1 drop-down list. In Excel 2007, click New Rule and then click Use A Formula To Determine Which Cells To Format.
  4. Enter the following formula:=HF(A1).
  5. Click the Format button.
  6. Click on the Patterns tab, select blue, and click OK. In Excel 2007, go to the Fill tab, choose blue under Background color (Figure B), and click OK.
  7. Click OK.

Figure B

Now all cells containing formulas are highlighted in blue (Figure C).

Figure C

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks