How to Highlight Top 10 Values in Google Sheets

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.