Software

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.

7 comments
danny
danny

Hi Mary Ann - Thank you for this post and for the VBA Code! When I teach an Excel Class, I always demonstrate several ways to Show / Highlight the cells that contain formulas. I use the Ctrl+ ~ Keyboard Shortcut so that you can actually see each formula in the worksheet. This is a "Toggle" so it is easy to restore the normal view of the worksheet. When I want to demonstrate how easy it is to have "hard-coded values" in a cell that should contain a Formula, I use Edit - Go To (or Ctrl+G or F5) and then select Special - Formulas (or Constants) Highlighting the Formulas in a worksheet is a great way to have them selected and then apply either formatting of a Cell Style. If you want to watch a short video on this topic, here is the link to the one that I created: http://www.thecompanyrocks.com/excels/2008/07/how-to-reveal-the-formulas-in-all-cells/ Thanks again! Danny Rocks www.thecompanyrocks.com

andrewandwendy
andrewandwendy

There is an alternate method built into Excel to achieve the same thing. Edit menu, go to, special then select formulas radio button. All formulas in the worksheet are selected and you can apply a colour from the formating toolbar. This is a lot more steps though.

Marshwiggle
Marshwiggle

Don't we first have to select some cells before adding the conditional formatting? Like all the cells, or all the used cells, on the sheet?

pgurney
pgurney

If your only purpose is to see which cells contain formulas, Excel 2007 has an easier way than color-coding the cells. On the Formula Tab, in the Formula Auditing group, there is a command for Show Formulas. It's a toggle button - click once it will show you the formulas in the cells, click again and it will show the cells values instead of the formulas.

artiff49
artiff49

andrewandwendy, Had the only alternate method for locating formulas in excel 2003! Thanks andrewandwendy.

Marshwiggle
Marshwiggle

... you could simply use Ctrl+~ on the keyboard (at least up up to v. 2003); but I think the advantage of Mary Ann's way is that you can see which cells have formulas w/o having to display the formulas ... and it's definitely prettier.

Editor's Picks