MrExcel.com - Your One Stop for Excel Tips & Solutions

Identify Cells with Formulas

December 18, 2001 - by

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

=HasForm

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!!