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

conditional formatting

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Conditional Formatting

Conditional Formatting

ResolvedVersion 365

Charlotte has attended:
Excel Intermediate course

Conditional Formatting

What is the best formula for output to: highlight a series of cells with various values if they fall 10% below fixed value in other cell, all other cells that are 15% above highlight in another colour, leave all others as they are?

RE: Conditional Formatting

Hi Charlotte,

Thank you for the forum question.

Best practice is to type the fixed value, 10%, and 15% in 3 different cells on the worksheet.

In my example I have the fixed value typed in F1. 10% in D1, and 15% in E1. My values I would like to highlight are in the range A1:A20


You need two rules.

Select all the values you would like to be highlighted.

Click Conditional Formatting and click New Rule.

Select the last option on the top "Use a formula to determine which cells to format".

In the "Format values where this formula is true" type:


=A2<$F$1*(1-$D$1) (this one highlights under 10%)

Click Format and choose a colour.

Do the same again but this time type the formula:

=A2>$F$1*(1+$E$1) (this one highlights over 15%)

I hope this makes sense.

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

Thu 5 Aug 2021: Automatically marked as resolved.

Excel tip:

Creating custom lists

In Excel if you type in January in a cell, you can then copy this cell to replicate Febraury, MArch, April etc.

This list has come from Tools- options and Custom lists.

Therefore to save time and create your own list you can click on New (in Tools and custom list tab) and type out the lsit that you want copied quickly.

All you have to do is then type in the 1st word and you will be able to copy the rest of the list quickly.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.