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.
Hello,
I’m trying to build a schedule in Excel (yes MS project would be better, but it mixes in a lot of data too). I’ve used formulas to predict the dates. Is there a way to conditionally format the cells containing formulas to highlight if they are late?
Example:
A2 contains project start date,
A3 contains milestone 1 as a formula “=WORKDAY(A2,5,Holidays!A2:A13)”,
A4 contains milestone 2 as a formula “=WORKDAY(A3,5,Holidays!A2:A13)”,
A5 contains target completion date
You would update the value in A3 to the actual completion date, which would trigger an update in A4.
This is the cell I’d like to highlight if there’s less than 5 days between the newly calculated date in A4 and the value in A5.
Hello Holly – –
Just reading your question a few times, a conditional formatting formula rule applied to cell A5 could simply be
=(A5-A4)<5Hi Tom,
I want to conditional format a cell in an excel spreadsheet I am making up.
It is for maintenance planning with cell E3 being the date maintenance last completed and cell F3 being when maintenance next due. I want cell F3 to fill orange when the date in it is less than 14 days away. ie. if the date in cell F3 is 15th May 2020, it would fill orange on the 1st of May 2020
This conditional formatting rule would do what you want, applied to cell F3 and you select the orange color in the conditional formatting dialog box.
=TODAY()-F3<14Hello Tom, Great work here. Thanks for helping so many people!
I’m trying to use conditional formatting in a cell that has a formula ( =C3&” “&D3 ) to merge text and numerical data from other cells. There will be different results and I want to color the cell according to the end result of the formula. I’ve tried using “Format only cells that contain” rule and “Use a formula to determine which cells to format” rule but nothing works.
Any ideas?