A percentile rank indicates the standing of a particular data value relative to other data values in a sample. Percentiles are most notably used in determining performance on standardized tests. If a child scores in the 90th percentile on a standardized test, this means that his or her score is higher than 90 percent of the other children taking the test.
Another way to look at it is to say that the child’s score is in the top 10 percent of all the children taking the test.
Percentiles are often used in data analysis as a method of measuring a subject’s performance in relation to the group as a whole — for instance, determining the percentile ranking for each employee based on an annual revenue.
In Excel, you can easily get key percentile ranks using the PERCENTILE function. This function requires two arguments: a range of data and the percentile score you want to see.
In the example shown, the value in cell D7 is a result of the following formula, which pulls the 75th percentile based on the data in range B3:B20:
=PERCENTILE($B$3:$B$20,0.75)
This formula tells you that any employee with revenue over $52,651 is in the top 75 percent of performers.
The value in cell D16 is a result of the following formula, which pulls the 25th percentile based on the data in range B3:B20:
=PERCENTILE($B$3:$B$20,0.25)
This formula tells you that any employee with revenue below $24,656 is in the bottom 25 percent of performers.
Using these percentile markers, this example applies conditional formatting so that any value in the 75th percentile will be colored green and any value in the 25th percentile will be colored red.
To build this basic formatting rule, follow these steps:
Select the data cells in your target range (cells B3:B20 in this example), click the Home tab of the Excel Ribbon, and then select Conditional Formatting→New Rule.
This opens the New Formatting Rule dialog box.
In the list box at the top of the dialog box, click the Use a Formula to Determine which Cells to Format option.
This selection evaluates values based on a formula you specify. If a particular value evaluates to TRUE, the conditional formatting is applied to that cell.
In the formula input box, enter the formula shown with this step.
In this formula, you evaluate whether the data in the target cell (B3) is within the 25th percentile. If so, the conditional formatting will be applied.
=B3<=PERCENTILE($B$3:$B$20,0.25)
Note that in the formula, you exclude the absolute reference dollar symbols ($) for the target cell (B3). If you click cell B3 instead of typing the cell reference, Excel automatically makes your cell reference absolute. It’s important that you don’t include the absolute reference dollar symbols in your target cell because you need Excel to apply this formatting rule based on each cell’s own value.
Click the Format button.
This opens the Format Cells dialog box, where you have a full set of options for formatting the font, border, and fill for your target cell. After you have completed choosing your formatting options, click the OK button to confirm your changes and return to the New Formatting Rule dialog box.
Back in the New Formatting Rule dialog box, click the OK button to confirm your formatting rule.
At this point, you should be in the Conditional Formatting Rules Manager dialog box.
Click the New Rule button.
This opens the New Formatting Rule dialog box.
In the list box at the top of the dialog box, click the Use a Formula to Determine which Cells to Format option. This selection evaluates values based on a formula you specify. If a particular value evaluates to TRUE, then the conditional formatting is applied to that cell.
In the formula input box, enter the formula shown here.
In this formula, you’re evaluating if the data in the target cell (B3) within the 75th percentile. If so, the conditional formatting will be applied.
=B3>=PERCENTILE($B$3:$B$20,0.75)
Click the Format button.
This opens the Format Cells dialog box, where you have a full set of options for formatting the font, border, and fill for your target cell. After you have completed choosing your formatting options, click the OK button to confirm your changes and return to the New Formatting Rule dialog box.
Back on the New Formatting Rule dialog box, click the OK button to confirm your formatting rule.
If you need to edit your conditional formatting rule, simply place your cursor in any of the data cells within your formatted range and then go to the Home tab and select Conditional Formatting→Manage Rules. This opens the Conditional Formatting Rules Manager dialog box. Click the rule you want to edit and then click the Edit Rule button.