How to Find the Top 10% of Values in Google Sheets

To find the top 10% of values in Google Sheets, you'll need to use a combination of functions like PERCENTILE, RANK, and COUNT. Follow these steps to find the top 10% of values in your dataset:

1. Start by arranging your data in a single column. Let's assume your data is in column A, starting from cell A1 and going down to cell A20.
2. In an empty cell, enter the formula to calculate the 90th percentile of your data. The 90th percentile represents the value below which 90% of the data falls, so the top 10% of values are above this number. Use the formula =PERCENTILE(A1:A20, 0.9) and press Enter. Let's assume you entered this formula in cell B1.
3. In another empty column, say column C, use the RANK function to rank the values in column A. In cell C1, enter the formula =RANK(A1, $A$1:$A$20). Drag this formula down to fill the entire range of cells corresponding to your data in column A (in this example, cells C1 to C20).
4. Now, you need to count the total number of values in your dataset. In an empty cell, enter the formula =COUNT(A1:A20). Let's assume you entered this formula in cell B2.
5. Calculate the number of top 10% values by multiplying the total count by 0.1. In cell B3, enter the formula =ROUND(B2*0.1, 0).
6. Finally, use a filter or conditional formatting to highlight or display the top 10% of values, based on their rank.

Example

Assuming your data is in column A (A1:A20):

1. In cell B1, enter the formula =PERCENTILE(A1:A20, 0.9)
2. In cell C1, enter the formula =RANK(A1, $A$1:$A$20) and drag it down to fill cells C1 to C20.
3. In cell B2, enter the formula =COUNT(A1:A20)
4. In cell B3, enter the formula =ROUND(B2*0.1, 0)
5. Select cells C1 to C20, and click on "Format" > "Conditional formatting" from the menu.
6. In the "Format cells if" dropdown, select "Less than or equal to."
7. In the value field, enter $B$3 and choose a formatting style to highlight the top 10% of values.
8. Click "Done" to apply the conditional formatting.

Your top 10% of values will now be highlighted in the selected formatting style.