Emma has attended:
Excel Intermediate course
Conditional formatting - Creating a formula
I have a spreadsheet of data that currently has a conditional formatting rule in place which highlights a cell green or red if the value in that cell is greater or less then the cell that it's being compared against.
I now need to edit this existing rule to include a rule if possible that will highlight a cell amber if that value is less / greater than 1% of the cell it's being compared against.
Is this possible to add by editing the existing rule, by using a rule type of a formula which I need some advice on how to build!
RE: Conditional formatting - Creating a formula
Hi Emma,
Thank you for the forum question.
Can I please ask you to copy the rule you have and show me the rule here.
Thanks
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Conditional formatting - Creating a formula
Hi Jens,
As an example, if cell C2 is greater than C4 - it should be green.
Edit formatting rule\ format only cells that contain\ edit the rule description - format only cells with:
Cell value\ Greater than\ =$C$4
Highlights green
if cell C2 is less than C4 - it should be red.
Edit formatting rule\ format only cells that contain\ edit the rule description - format only cells with:
Cell value\ less than\ =$C$4
Highlights red
But would it be possible to add an additional rule, where a formula could be added so it would also highlight a cell amber if the value in cell C2 is greater or less than 1% of the value in cell C4
RE: Conditional formatting - Creating a formula
. Here are the steps:
1. Greater Than (Green):
o Select the cell range where you want to apply the formatting (e.g., D5:D10).
o Go to the Home tab and click on Conditional Formatting.
o Choose Greater Than and set the comparison cell to $C$4 (or any other cell you want to compare with).
o Pick a format (e.g., light green fill) and click OK.
2. Less Than (Red):
o Follow the same steps as above but choose Less Than instead.
o Set the comparison cell to $C$4.
o Pick a format (e.g., light red fill) and click OK.
3. 1% Less or Greater (Amber):
o Again, follow the same steps, but this time choose Use a formula to determine which cells to format.
o Enter the formula: =AND(D5>=0.99*$C$4, D5<=1.01*$C$4).
o Select your desired format (e.g., amber fill) and click OK2
This will highlight cells based on your specified conditions. Feel free to adjust the formatting and cell references as needed!
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
