Today, I have an exciting trick to share with you that will take your Google Sheets skills to the next level. We’re going to learn how to highlight the largest three values in each row using the powerful LARGE function. Are you ready? Let’s dive in!
Custom Formula to Highlight Max | Largest 3 Values in Each Row – Google Sheets
Before we get started, take a look at the image below. It showcases how the highlighting works on each row, making it easier for you to understand the concept.
In order to highlight the three largest values in each row, we can use a simple custom formula in Google Sheets. Here’s how you do it:
- Select the range you want to apply the conditional formatting to. For example,
B2:K. - Go to Format > Conditional format.
- In the custom formula field, enter the following formula:
=B2:B>=LARGE($B2:$K2,3). - Apply the formatting.
And just like that, the largest three values in each row will be highlighted, making it easier for you to spot them at a glance.
Dealing with Common Issues
While using this conditional formatting technique, you may come across a few common issues. Let’s address them one by one and find solutions.
Issue #1: No Highlighting Happens When There Are Less Than Three Max Values
Sometimes, you might notice that the last row is unaffected by the highlighting. This happens when there are only two values in that row. The LARGE function fails to return the third largest value, resulting in no highlighting. To fix this issue, follow these steps:
- Fill all the blank cells in the range, such as
B2:K5, with zeros (0). - This way, the formula
=LARGE(B5:K5,3)will return 0 instead of an error. - Voila! The conditional formatting will now work properly, even if there are less than three max values.
Issue #2: Large Function NUM! Error
Filling the blank cells with zeros might seem like a good solution, but it’s not practical in a growing range. Your sheet will end up looking cluttered with unnecessary zeros. To overcome this issue, you can modify the formula as follows:
=B2:B>=LARGE(ARRAYFORMULA(N($B2:$K2)),3)
By wrapping the LARGE function with the N function and using ARRAYFORMULA, we can handle the NUM! error. This modification allows the conditional formatting to work smoothly without cluttering your sheet with zeros.
Issue #3: Highlighting Largest 3 Values in Each Row Affects Blank Rows Too
When you solve one conditional format issue, another one might pop up. If all the values in a row are zeros, the entire row gets highlighted. This happens because the N function makes the blank cells appear as zeros. So, how do we solve this highlighting puzzle? Simple, by using the AND logical operator.
Here’s the final formula to conditionally format the largest three values in each row:
=AND(LEN($B2:$K2), B2:B>=LARGE(ARRAYFORMULA(N($B2:$K2)),3))
By combining the length of the range with the conditional formatting formula, we can ensure that only the rows with values are highlighted. No more highlighting blank rows!
And there you have it! You now know how to highlight the largest three values in each row using the LARGE function in Google Sheets. Feel free to explore the resources below to expand your knowledge and make the most out of conditional formatting in Google Sheets.
Resources:
- Conditional Format Max Two Values Based on Criterion in Google Sheets
- AND, OR, or NOT in Conditional Formatting in Google Sheets
- Highlight an Entire Row in Conditional Formatting in Google Sheets
- Role of Indirect Function in Conditional Formatting in Google Sheets
- How to Highlight Every Nth Row or Column in Google Sheets
- Highlight Matches or Differences in Two Lists in Google Sheets
- Highlight Intersecting Value in Google Sheets in a Two-Way Lookup
- Google Sheets – Highlight the Max Value in Each Group
- Highlight Min Excluding Zero and Blank Cell in Google Sheets
Now go ahead and impress your colleagues with your newfound knowledge. Happy highlighting!
