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.

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

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