When working with data in Google Sheets, you often need to deal with the top values. If you want to highlight the top 10 values, please refer to the post here. However, you may want to highlight the values by percentage, e.g.,the top 10% values in your data. In this example, we are going to highlight the top 20% values, but it is the same steps to have top values by other percentage.
Step 1: Select the data column or the data range that you want to highlight;
Step 2: Click the "Format" tab from the ribbon;
Step 3: Click the "Conditional formatting" from the drop-down list;
Step 4: On the right of your spreadsheet, you will see the "Conditional format rule" panel. In the "Format rules" box, select "Custom formula is" from the bottom and copy the formula =PERCENTRANK($A$1:$A$10,A1)>=80% in the next box;
– Please change the data range in your own data. You can also change 80% to any other percentage to have the top values for other percentage. For example, change 80% to 90% to have the top 10% values.
Step 5: If you want a different color from the default color, you can change the color in the "Formatting style" section, e.g., "green";
Step 6: Click the "Done" button, the cells with top 20% values (2 values) will be in green;
Step 7: To have the bottom 20% values in the data column, change the formula in step 4 to =PERCENTRANK($A$1:$A$10,A1)<=20% .
