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

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

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)

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

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.

This tip works in MS Excel 2000, Thanks!

Good to know – – thanks!

Excellent

Thank you!

i am doing this but failed, can you tell me where i am missing? i am applying all your direction to new sheet but did not get two color by conditional formatting.

Thanks.

I cannot day what you are missing because I cannot see your workbook. But I know that the steps work that I showed in the pictures, so there must be something different you are doing or not doing.