Conditional Format - Format cells based on a % deviation

[jessiegeorgia]

Hi All,

I've got tthree rows of data - forecast, actual and deviation (actual-forecast)
12 columns (Jan - Dec)
I'd like conditional formatting to highlight the deviation if it is more than 10%.

I'm sure conditional formatting is meant to be easier in 2007 version but I can't figure this out at all,

Thanks in advance,
Jess

 

[StuartWhi]

Hi Jess,

Complete the following and you should be covered.

- Select the 12 cells showing the % deviation.
- Select Home > Conditional Formatting > Highlight Cell Rules > Greater than...
- Enter 10%
I like to also adjust the formatting as well - Pull down > custom formatting then select Fill and red.

Hope it works for you.

Stuart.

 

[jessiegeorgia]

Hi Stuart,

Thanks for your reply, unfortunately I don't have a percentage deviation in the third row - it's the difference in number (i.e 620 rather than 11% and sometimes negatives) which our client wants highlighting if it is more than 10% of the forecast. I don't know if conditional formatting can do a calculation???

Thanks
Jess

 

[StuartWhi]

Hi Jess,

Not an issue slight change then,

- Select the first cells showing the deviation.
- Select Home > Conditional Formatting > Highlight Cell Rules > More Rules
- Select "Use a Formula...."
- Input into the "Format values where.... is true:" =D3/B3>0.1
- Column D being the result, B the actual (i.e. working out %)
- If you like you can replace the "0.1" with a cell reference allowing a simply way to change the %.
- Change the "Format" i.e. red background (Fill) like my first post.

Lastly you need to implement this conditional format across all cells.
- Select Home > Conditional Formatting > Manage Rules..
- Adjust "Applies to" the range of deviation cells.

You should be set.

Regards,

Stuart.

 

[jessiegeorgia]

Thanks Stuart! Work perfectly

 

[StuartWhi]

Hi Jess,

Glad to hear.

Catch you later,

Stuart.