Tom’s Tutorials For Excel: Formatting Cells Containing Formulas, Constants, or Nothing

Tom’s Tutorials For Excel: Formatting Cells Containing Formulas, Constants, or Nothing.

With Conditional Formatting, you can color-code cells in real time that contain formulas, or constants, or nothing at all.



Step 1
From your keyboard press Ctrl+F3 to add a new name to your workbook.

Step 2
As shown in the pictures, enter Formulas as a new name.
In the Refers to field, enter the formula =GET.CELL(48,INDIRECT("rc",0)).

The Define Name dialog box in version 2003.


The Name Manager dialog box in version 2010.

Step 3
Select the range of cells on your worksheet to be conditionally formatted for formulas. In this example, the selection started in cell A1.

Step 4
From your keyboard, press Alt+O+D to show the Conditional Formatting dialog.

Step 5
The formula rule for cells containing formulas is =Formulas.
Click the Format button for your desired color for cells with formulas.

The formula rule for cells containing constants is =AND(LEN(A1)>0,ISTEXT("rc")).
Click the Format button for your desired color for cells with constants.

Conditional Formatting dialog in version 2003


Conditional Formatting dialog in version 2010

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,
6 comments on “Tom’s Tutorials For Excel: Formatting Cells Containing Formulas, Constants, or Nothing
  1. Ben says:

    Hi, i have a sheet that contains dates which are all paste linked cells populated by another worksheet.

    Is there any way to conditional format the formula driven cells to highlight dates that are 30+days old?

    thanks
    Ben

    • Tom Urtis says:

      Hi Ben – –

      Thank you for contacting me. For cells with formulas (or not) that contain dates, and you want to conditionally format those cells whose dates are 30 days old or more, use this formula rule in your Conditional Formatting dialog box:

      =AND(LEN(A2) > 0,A2 < TODAY()-30)

  2. Shevy says:

    Hi i am trying RAG (red amber green) a cell that has a formula in it.

    So if the value of the cell with formula in it is 1 or 2 I need it to turn green value 3 amber and value 4 red.

    Can this be done?

    Thanks in advance

    Shevy

    • Tom Urtis says:

      It seems that Conditional Formatting shoudl do what you want. Suppose your cell of interest is B2. Select that cell and from your keyboard press Alt+OD. Click the New Rule button, then click “Use a formula to determine which cells to format”. Enter this rule: =OR(B2=1,B2=2) then click the Foermat button, click onto the Fill tab, select your green color from the palette, click OK, abnd OK again. Click New Rule again and enter your second rule, =B2=3 and choose yoru amber color. Repeat those same steps for the third rule, =B2=4 and choose red. OK your way through the dialog boxes and your cell will display those colors based on that cell’s value being a 1 or 2; or 3; or 4.

  3. KP-Bmore says:

    This tip works in MS Excel 2000, Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

*