conditional formatting creating

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

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

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

# Conditional formatting - Creating a formula | Excel forum

Resolved · 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

Training courses

Training information:

 Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

### 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: