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.
Conditional formatting based on percentile range
Conditional formatting based on percentile range
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:
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
