Julia has attended:
Excel Advanced course
PowerPoint Intermediate Advanced course
Conditional formatting by a formula
Hello Best STL,
I am trying to format cells using Conditional Formatting. I would like to colour red cells that contain a value lower than the value I have in cell E65. So I highlight the data in the column I want this to apply to. I go to 'Conditional Formatting', 'New Rule', 'Use a formula to determine etc.'.
In the formula box I enter <=E65, and I choose the fill to be red. It accepts the formula but nothing happens (even though there are several cells that are lower than the value in E65).
Is it an issue that the value in E65 is itself run off a formula? (It's the 0.33 percentile of the data above it).
Many thanks for your help... I need to get this formatted asap this morning (as in Tues 30th) so I really hope you can help...
Julia
RE: conditional formatting by a formula
Hello Julia,
Hope you enjoyed your Microsoft Excel Advanced course with Best STL.
Thank you for your question regarding using conditional formatting with a formula.
Your formula should begin with an = sign followed by the first cell reference of the data array which will be formatted. The reference to cell E65 should be as follows:
>=E$65
The formula will then look something like this assuming that the first cell of the data array to be formatted is say F2:
=F2>=E$65
Make sure that you select the entire data array before you start.
I have attached a sample file which should help you with this.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
Attached files...
Conditional Formatting.xlsxRE: conditional formatting by a formula
Hello Rodney,
Many thanks for your quick reply. This has sorted the problem!
However, how do I get it not to colour in the "na" cells green?
Can I send you a screen shot - do you have an email address (I'd rather not send it on the forum)?
Regards,
Julia
RE: conditional formatting by a formula
Hello Julia,
I'm happy that my solution worked for you.
You may send me a sample file or screenshot to my email address:
leechenterprises04@yahoo.co.uk
Look forward to helping you further
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
RE: conditional formatting by a formula
Hello Julia,
Thanks for your screenshot.
For this type of conditional format don't use a formula type rather, use the cell value option.
This will appear as Cell Value = "na"
Don't fill in the double quotes, let Excel do it for you and make sure that this option is at the top.
This worked for me so give it a try!
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
