When working with data in Google Sheets, you often need to deal with the top values. You may want to highlight the top 10 or top 5 values in your data. In this example, we are going to highlight the top 3 values, but it is the same steps to have top 10 values.
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 =RANK(A1,A$1:A$10)<=3 in the next box;
– Please change the data range in your own data. You can also change 3 to any other number to have the top values. For example, change 3 to 10 to have the top 10 values or change 3 to 5 to have the top 5 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 3 values will be in green;
Step 7: To have the bottom 3 values in the column, you can change the formula in step 4 to =RANK(A1,A$1:A$10,1)<=3 , where 1 in the Rank function is to sort the data in the ascending order.
If you want to list the top or bottom 10 values, please change the value 3 to 10 to have the list.