Conditional Format Help. Number +/- 10%

New Contributor



I'm a little stuck creating a conditional formatting rule. I need to highlight cells that are <> 10% of a value in another cell.


Thank you in advanced

4 Replies


Let's say the cells you want to highlight are in D2:D100, and the cell to compare to is the cell in the same row in E2:E100.

Select D2:D100. D2 should be the active cell in the selection.

On the Home tab of the ribbon, select Conditional Formatting >New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula




Click Format...

Specify the desired formatting.

OK your way out.


If you want to compare to E2 for all cells in D2:D100, change $E2 to $E$2 in the formula.

And if you wanted the opposite, change < to >.

@Hans Vogelaar Many thanks for taking the time to reply.  I understand your response and have adapted for my scenario.  


In the table below I am now trying to compare the cells in each columns with that columns Median. Highlighting any cell that is above/below the median figure by 10%. Conditional formatting keeps reverting back to absolute values and therefore I am unable to drag formula. 


Median 40.641.44244.743.837.138.6



best response confirmed by dmoggie (New Contributor)


See the attached sample workbook.

Thank you so much I just could not figure it out.