To make your Google Sheets stand out, you can highlight the top 10 ranks using either the RANK or LARGE functions. These formulas are powerful tools that allow you to highlight up to rank 10 (n) in a single column or in multiple columns separately.
RANK Formula to Highlight Top 10 Ranks in Columns
Single Column
Let’s start with a single column. Suppose you have values in the range B2:B that you want to highlight. The RANK formula below can help you achieve this:
=RANK(B2,B$2:B)<=10
This formula determines the rank of the value in cell B2 in the range B$2:B. The cell reference B2 is relative, meaning that the conditional format will automatically adjust the rank for each cell in the range. The range B$2:B is absolute, which ensures that it remains fixed as you apply the formula to different cells.
To use this formula as a custom format rule, follow these steps:
- Select the range B2:B.
- Click on “Format” and then “Conditional formatting” to open the sidebar.
- Make sure that “Apply to range” is set to B2:B (or B2:B1000 if there are 1000 rows).
- Select “Custom formula is” under “Format rules”.
- Enter the RANK formula into the custom formula field.
- Click “Done” to apply the formatting.
If you’ve successfully implemented these settings, your format rule should look similar to the screenshot below.
Each Column
To highlight the top 10 values in each column, you can use the same RANK formula. The absolute/relative cell references in the formula make it adaptable to multiple columns. For example, if you want to highlight the columns B2:B, C2:C, and D2:D, simply select the range B2:D before applying the highlighting rule or change the “Apply to range” in the sidebar panel.
LARGE Formula to Highlight Top 10 Ranks in Columns
In addition to the RANK formula, you can also use the LARGE formula to achieve the same result in Google Sheets. The formula is as follows:
=B2>=LARGE(B$2:B,10)
Just like the RANK formula, the LARGE formula works for both single and multiple columns. You can apply the same steps mentioned earlier to implement this formula as a custom format rule.
Highlight Top 10 Values in Ascending Order
By default, the earlier conditional format rules assign rank #1 to the maximum value in a column. If you want to assign rank #1 to the smallest value in a column, you can make a small change to the formulas.
For the RANK formula, change it as follows:
=RANK(B2,B$2:B,1)<=10
For the LARGE formula, replace the function “LARGE” with “SMALL”:
=B2<=SMALL(B$2:B,10)
This way, you can highlight the top 10 ranks in either ascending or descending order in Google Sheets.
These methods will help you make your data more visually appealing and easier to analyze. So go ahead and start highlighting those top ranks in your Google Sheets!
Find more useful Google Sheets tips and tricks on Crawlan.com
