have no idea why this took me so long to figure out, but it was driving me crazy.

If you want to have Excel conditionally format only those cells that have formulas, the easiest way to accomplish this is:

1)  Select Insert > Name > Define.

2) In the Define Name dialog box, enter the following in the ‘Names in workbook’ box:  CellHasFormula

3) Then enter the following formula in the “Refers to” box =GET.CELL(48,INDIRECT(“rc”,FALSE))

4) Click Add, and then OK.

5) Select all the cells to which you want to apply the conditional formatting.

6) Select Format > Conditional Formatting

7) In the Conditional Formatting dialog box, select ‘Formula Is’ from the drop-down list, and then enter this formula in the adjacent box:

=CellHasFormula

8) Click the Format button and select the type of formatting you want for the cells that contain a formula.

9) Click OK.

HOWEVER, assume you have a spreadsheet with nothing BUT  bunch of formulas and you want to find those cells that have been “hard coded” (i.e., those cells that have been overwritten with “values” or “constants”).

This was not as easy, but I finally figured it out.

So . . . . suppose we have a spreadsheet in which Column ‘A’ has some formulas but also some cells that contain hard coded values (see Image 1) and you want to highlight those cells AND ONLY THOSE cells that have a hard coded value in them (in the below example, it would highlight cells “A5” and “A9”:

 

Excel sheet with formulas and hard coded values in same column
Image 1 – Excel sheet with formulas and hard coded values in same column

Similar to the steps above:

1)  Select Insert > Name > Define.

2) In the Define Name dialog box, enter the following in the ‘Names in workbook’ box:  CellHasValue

3) Then enter the following formula in the “Refers to” box =NOT(GET.CELL(48,INDIRECT(“rc”,FALSE))) – see Image 1

 

Define Name for a Cell that does NOT have a formula

Image 2 – Define Name for a Cell that does NOT have a formula

4) Click Add, and then OK.

5) Select all the cells to which you want to apply the conditional formatting.

6) Select Format > Conditional Formatting

7) In the Conditional Formatting dialog box, select ‘Formula Is’ from the drop-down list, and then enter this formula in the adjacent box (Image 3):

=CellHasValue

8) Click the Format button and select the type of formatting you want for the cells that contain a formula.

9) Click OK.

 

Conditionally Format only those cells that meet the criteria for NOT containing a formula.

Image 3 – Conditionally Format only those cells that meet the criteria for NOT containing a formula

But if you stop there, you will only be conditionally formatting those cells that not only do NOT contain a formula, but it will also highlight those cells that don’t contain ANYTHING (in this example, cells “A1”, “A6”, “A7” and “A10 . . . . “ on down.  So we need to take a few more steps by conditionally formatting blanks so that the format of those blank cells, isn’t really a format at all.

10) Select all the cells to which you want to apply the conditional formatting (the same range as above).

11) Select Format > Conditional Formatting

12) In the Conditional Formatting dialog box, select ‘Format only cells that contain’ from the drop-down list, and then select “Blanks” (Image 4):

13) Apply a “Custom Format” to those cells that contain neither a special font color, shading and/or border to those blank cells.

14) Click OK.

 

Apply a blank conditional format which formats (without formatting) any blank cells.
Image 4 – Apply a “blank” conditional format which formats (without formatting) any blank cells

15) Finally, under Conditional Formatting, select “Manage Rules” and make sure your “Blank” format appears first and check “Stop if true” (Image 5).

 

Make sure your “blank” cells are treated as blanks and ONLY as blanks.

Image 5 – Make sure your “Blank” cells are treated as blanks and ONLY as blanks

Now, the end result should be a spreadsheet in which cells that have hardcoded values are highlighted (Image 6).

 

Only those cells that are hard coded values or hard coded constants are highlighted.

Image 6 – Only those cells that are hard coded values or constants are highlighted

If you have a more straightforward solution, please let me know!