Identify cells containing formulas using conditional formatting

**requiem** · 06-07-2011, 01:06 PM

I need to identify cells in a column (i.e. W2:W4857) that either do contain a formula or don't contain a formula. I tried using this formula under the conditional formatting: =isformula($W$2:$W$4857), but it didn't work as isformula isn't available in excel 2007.

This column has vertical (subtotals) and horizontal (U*V) formulae. Originally every cell in this column did contain a formula (there are no blank cells in this column before row 4857). My boss "overwrote" some of the cells by typing in text. (AGHHH!!!) Now I have find these cells to put the formulae back in so that the worksheet calculates properly. It wouldn't be a problem to copy the original formula (u*v) all the way down, but after the insertion of the subtotals, they are now in the way.

Does anyone have a solution that doesn't use a macro??

**ChemistB** · 06-07-2011, 01:31 PM

One quick way is to select the range, hit F5 (GoTo) then Special, then formulas.
That will select all your cells with formulas in them. Then change the background color on those cells. Then, in Excel 2007, you can filter by cell color (or "no fill"). Then when you're done, change things back. Does that work for you?

**requiem** · 06-08-2011, 01:32 PM

Hi - thanks.That would work... but I guess I was looking for a quick way that wouldn't force me to remove then reinsert the subtotals....

**martindwilson** · 06-08-2011, 02:06 PM

you could try more functions add in
http://xcell05.free.fr/morefunc/english/
if thats not up try
http://download.cnet.com/Morefunc/30...-10423159.html
then select cell a1
define a name of say " isformula" and make it
=XLM.GET.CELL(48,Sheet1!A1)
now apply conditional format to your range of use a formula option
=isformula
it works in 2007 i just tried it unfortunately i cant embed morefunc in excel 2007! to upload a sample

**ChemistB** · 06-08-2011, 03:56 PM

Actually, Get.Cell(48,A1) is an old Macro 4 command that still works in Defined names. Remove the xlm part and it should work as Martin described without any add ins