How to Highlight 10 Largest Numbers in Column
Learn how to highlight the 10 largest numbers in a column using conditional formatting and the LARGE formula in Google Sheets.
We've highlighted 10 largest numbers in the column. I'm going to show you how to do this and why it works this way. So first off, we have a column and we're going to select it. Going to go to format , conditional formatting , and there you're going to add another rule. The rule we're going to add is a custom formula equals a one is greater than or equal to large. A1 to A10. So how does this work and why is the large formula used here? I'm going to show you. Equals large, let's look at that first,. This is giving us the nth largest number in a data set. So we have A, and let's look at the third largest number. We can put any number here. We can see the first largest number, the highest number, without using the max formula. Third, fourth, fifth, whatever number we put in here. So if we put in 10, we have here from this column, the 10th largest number.
And we just have to do a little bit of mental gymnastics here to say, okay, everything higher than or equal to the 10th largest number is going to be the 10 largest numbers in this column. So let's delete this and start over again. We're going to add a rule, we're going to change our format rules over on the right to custom formula , and our custom formula is going to be equals A1, it's going to be greater than or equal to large. range we're looking at is A2 colon A. We're looking for the tenth largest number . What's going to happen is that this A2 and A is going to move and change along with the conditional formatting as it changes. So we need to actually lock this in to get the actual whole column . So that's why we put a dollar sign in front of the A, if we want to not
highlight this A one, what we can do is just change the range to a two. That should now shift everything, but we do need to change this A one to a two so that it aligns with the top one. And what it's gonna do is in a two, it's gonna say apply this formula . In a three, it's going to apply this formula except changes a two to a three. It's just gonna be adding a row as it goes down the rows. And this is how conditional formatting works and why we need to put this dollar sign here to lock in this a go down to done. We have all of our top ten numbers highlighted here.