Alex has attended:
Excel Intermediate course
Conditional Formatting based on a % of another cell
Hi
I am trying to use conditional formatting on a cell.
I would like it to be conditioned on a % of another cell.
For example:
Column A are my Total sales and column B/C/D are sales from my registers.
If A1 is £1000
B1 is £300
C1 is £500
D1 is £200
I want to make it so that if B1 equals or is greater than 25% of the total sales it is green,
10%-15% orange
9% and bellow red
Please help
RE: Conditional Formatting based on a % of another cell
Hello Alex,
Hope you enjoyed your Microsoft Excel Intermediate course with Best STL.
Thank you for your question regarding using conditional formatting with percentages.
To do this follow these steps:
1) Select the 3 cells containing the register values
2) Click the conditional formatting button on the Home ribbon
3) Click 'New Rule'
4) Select the option to 'Use a formula to determine which cells to format
5) Enter this formula - =IF(B2>$A2*25/100,TRUE)
6) Format with a green fill colour. Click OK
7) Go to Manage Rules
8) Add new rule following same procedure as above
9) Formula is =IF(B2>=$A2*10/100,TRUE)
10) Format with a yellow fill colour
11) Add another new rule
12) Formula is =IF(B2<$A2*10/100,TRUE)
13) Format with a red fill colour
The conditions must be arranged properly with Green then Yellow then Red in the Manage Rules area.
I have attached a sample file with additional data for you to examine. I have added conditional formatting to the data.
Have fun with it.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
Conditional Formats.xlsx
