In today's #GoogleSheets tip, I'm going to show you how to highlight the top 5 values in your data.
There are loads of ways to do this, like adding a filter and sorting the values highest to lowest, but today we're going to be using formulas and conditional formatting.
There are loads of ways to do this, like adding a filter and sorting the values highest to lowest, but today we're going to be using formulas and conditional formatting.
Here's the dataset we'll be using. Let's dive in!
1️⃣ Highlight your data, but exclude the header row, i.e. A2:C21 in this example
2️⃣ Open the menu: Format > Conditional formatting
3️⃣ Under Format rules, select Custom formula is
1️⃣ Highlight your data, but exclude the header row, i.e. A2:C21 in this example
2️⃣ Open the menu: Format > Conditional formatting
3️⃣ Under Format rules, select Custom formula is
4️⃣ Add one of the following formulas:
=$C2>=LARGE($C$2:$C$21,5)
or
=RANK($C2,$C$2:$C$21)<=5
The functions LARGE and RANK can all identify the position of a value relative to others around it.
5️⃣ Choose your format options, e.g. highlight the rows yellow.
=$C2>=LARGE($C$2:$C$21,5)
or
=RANK($C2,$C$2:$C$21)<=5
The functions LARGE and RANK can all identify the position of a value relative to others around it.
5️⃣ Choose your format options, e.g. highlight the rows yellow.
Here's what the end result looks like.
For more examples, the template, and a walkthrough of the formulas above, have a read of How To Highlight The Top 5 Values In Google Sheets ⬇️
benlcollins.com/spreadsheets/g…
That's a wrap!
For more examples, the template, and a walkthrough of the formulas above, have a read of How To Highlight The Top 5 Values In Google Sheets ⬇️
benlcollins.com/spreadsheets/g…
That's a wrap!
• • •
Missing some Tweet in this thread? You can try to
force a refresh
