About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store


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:

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

By Bill Jelen on 18-Dec-2001 Consulting can be hired to implement this concept, or many other cool applications, with your data. provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.


Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.