MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Identify Cells with Formulas

December 18, 2001 - by Bill Jelen

I am going to repeat a cool tip from the Message Board for today's tip. Pat asked:

Is there a quick and easy way to format cells to indicate which ones have formulas and which have values? I know you can display all formulas, but I want to look at the spreadsheet and easily tell which are calculated values and which are entered values.

Juan Pablo, the Excel whiz behind our Spanish-language MrExcel Consultoria Division, came up with this cool trick that relies on XLM

One *quick* method to do this is as follows.

  • Select A1.
  • Go to Insert, Names, Define.
  • Put a name in it, like HasForm
  • in the refers to put: =GET.CELL(48,A1)

Now, select the range you want to "check" for formulas. Select Conditional Formatting, change Value is to Formula is, and put this formula


Click on the pattern button and select something that makes it easy for you to see it has a formula and you're ready to go!!