How to conditional formatting data based on percentile rank in Excel?
In Excel, to calculate the percentile may be easy for most of users, but have you ever tried to apply conditional formatting in the set of numbers based on percentile rank? In this tutorial, I will tell you the method on quickly conditional formatting data based on percentile rank in Excel.
Recommended Excel Productivity Tools
1. Select the data you want to apply the conditional formatting, and click Home > Conditional Formatting > New Rule. See screenshot:
2. Then in the New Formatting Rule dialog, click Use a formula to determine which cells to format, and enter this formula =F2<=PERCENTILE($F$2:$F$18,0.25) into the Format values where this formula is true textbox. See screenshot:
In the formula, F2 is the first value in the list you want to use, F2:F18 is the list including the numbers you want to highlight, 0.25 indicate 25th percentile.
3. Click Format, and in the Format Cells dialog, you can change the formatting as you need to format the cells. See screenshot:
4. Click OK > OK. Now if the values are lower than the 25 percentile of the data set, they will be highlighted automatically. See screenshot: