Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
Sheets Tip 276: Find the top values 🥇🥈🥉
Hi Reader,
Welcome to the Google Sheets Tips newsletter #276, your Monday morning espresso, in spreadsheet form!
In case you missed my email earlier today, I'm delighted to announce that you can now enroll in my newest Google Sheets course.
It's called The AI Playbook for Google Sheets and it's designed to teach you how to use these new AI tools effectively with Google Sheets, so you can work more efficiently.
Check it out here and enjoy a 50% launch discount this week only:
​The AI Playbook for Google Sheets​
_______
➜ Google Sheets Tip #276: Find the top values 🥇🥈🥉
In this tip, we look at how to find the top values in a dataset.
There are several different ways to do this but I won't cover them all.
For example, you could add a filter to your dataset and sort the values from highest to lowest.
Today I want to show you how to use the RANK formula (and conditional formatting) to highlight the top 3.
We'll use this dataset:
RANK Function
In column E, add this formula and copy it down the whole column:
=RANK(D2,$D$2:$D$17)It adds a ranking to each row based on the value in column D and assuming that the highest value is ranked 1:
You can now sort the dataset by this rank column to get the highest values at the top.
Nice!
Conditional Formatting
Alternatively, we can use conditional formatting to highlight the top 3.
1) Highlight your data, but exclude the header row, i.e. A2:D17 in this example
2) Go to the menu: Format > Conditional formatting
3) Under Format rules, select: Custom formula is
4) Add the following formula:
=RANK($D2,$D$2:$D$17)<=35) Choose your format options, e.g. add a yellow background.
Boom!
_______
If you enjoyed this newsletter, please forward it to a friend who might enjoy it.
Have a great week!
Cheers,
Ben
P.S. If you find conditional formatting tricky, learn how to use AI tools to help you figure the correct formula out. See how in my new course:
​The AI Playbook for Google Sheets​
Enjoy 50% off during this launch week!
Google Sheets Tips
Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.
Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #377, your Monday morning espresso, in spreadsheet form! Coming to your inbox on a Tuesday this week, because it was a public holiday here in the US yesterday. Today I'm pondering whether it makes sense to write your own formulas anymore. AI tools are getting so good at writing formula syntax (and Apps Script code) that it doesn't make sense (economically at least) to spend hours crafting complex formulas yourself, if...
Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #376, your Monday morning espresso, in spreadsheet form! I've been using Gemini and Gemini Canvas a lot recently for various projects. It's mind-blowing how quick it is at generating formulas and scripts that work. We're definitely at the threshold (or maybe already past it 🤔) where it doesn't make sense to write your own complex formulas or code from scratch (for specific problems with limited scope). Having said...
Brought to you by: Hi Reader, Welcome to the Google Sheets Tips newsletter #375, your Monday morning espresso, in spreadsheet form! We're still in an ice box on the east coast of the US with the snow set to hang around for a while yet. It's beautiful but I am looking forward to the ice melting so it's easier to get around. The Potomac river is covered with ice Today's newsletter is not directly related to Google Sheets. Instead, I'm sharing the sources I use to stay informed of AI...