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