conditional formatting creating

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Conditional formatting - Creating a formula | Excel forum

Conditional formatting - Creating a formula | Excel forum

resolvedResolved · High Priority · Version 365

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


 

Excel tip:

DATEDIF function

The DATEDIF function is a worksheet function that computes the difference between two dates. This function is documented only in the help files for Excel 2000. It isn't documented in Excel 2002.
the formula function is:
=DATEDIF(Your age,Today(),"Y")
Y stands for year

View all Excel hints and tips


Server loaded in 0.07 secs.

✓ w3speedster