Ben Collins Profile picture
May 6, 2022 4 tweets 2 min read Read on X
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.
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 Image
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.
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! Image

• • •

Missing some Tweet in this thread? You can try to force a refresh
 

Keep Current with Ben Collins

Ben Collins Profile picture

Stay in touch and get notified when new unrolls are available from this author!

Read all threads

This Thread may be Removed Anytime!

PDF

Twitter may remove this content at anytime! Save it as PDF for later use!

Try unrolling a thread yourself!

how to unroll video
  1. Follow @ThreadReaderApp to mention us!

  2. From a Twitter thread mention us with a keyword "unroll"
@threadreaderapp unroll

Practice here first or read more on our help page!

More from @benlcollins

Feb 9
Thanks to reader Guy L. who asked this question.

How can we fill in blank rows of data in a column, based on the rows with data?

It's a classic question that I've seen countless times in my career.

For example, consider this dataset of appointment times. Image
Each blank row needs to have the time filled in to match the non-empty row above. I.e. rows 3 and 4 are at 9:15 am.

Of course, you could copy and paste the times, but that is not efficient beyond a handful of rows of data.
With the new LAMBDA functions, this can be solved with a single formula:

=SCAN(A2, A2:A10, LAMBDA(t, v, IF( ISBLANK(v), t, v )))

So, how does this work?
Read 8 tweets
Nov 7, 2022
A little step-by-step tutorial to start the week. 👇

Let's create a mini RSS reader in #GoogleSheets to keep us up-to-date with the latest Google Workspace product releases.

We'll be using the IMPORTFEED function to import the Google Workspace Product Updates RSS feed.
First, in cell A1, use the IMPORTFEED function to import the raw RSS feed:

=IMPORTFEED("feeds.feedburner.com/GoogleAppsUpda… ")
Then wrap this with the QUERY function to extract the columns we want:

=QUERY(IMPORTFEED("feeds.feedburner.com/GoogleAppsUpda… "),"select Col4, Col1,Col3")
Read 5 tweets
Aug 24, 2022
🚨🚨 BREAKING: 🚨🚨

If you're a Google Sheets user, stop what you're doing and check this out...

...they just dropped 10 new functions on us!!! 🔥🔥🔥

Christmas came early for Sheets nerds 🎅

Let's check them out 👇
1/ NAMED Functions

Named Functions in Google Sheets let you save and name your own custom formulas and then re-use them in other Google Sheet files.

This reusability is a HUGE development. I'm SUPER pumped on this one.

E.g. here's a function called STARCHART that I created:
2/ LAMBDA Function

It's here!!

The LAMBDA function in Google Sheets creates a custom function with placeholder inputs, that can be reused.

Used with other lambda functions (see below).
Read 14 tweets
Aug 23, 2022
🔥 AMAZING SPREADSHEETS 🔥

To some, Google Sheets is just a humble piece of work software...

...but to others, it's a blank canvas to build incredible things with.

10 creations you won't believe are built with #GoogleSheets:
1) Wordle in Google Sheets, by @aTylerRobertson

Tyler builds amazing games in Google Sheets, using only the built-in formulas.

His wordle game is just one of many:

👉 zapier.com/blog/wordle-in… Image
2) Minesweeper in Google Sheets, by @KieransSheets

This is a beautifully constructed game that uses Apps Script (Google Sheets code) to recreate the classic minesweeper actions.

Kieran has documented the build in amazing detail too!

👉 kierandixon.com/google-sheets-… Image
Read 12 tweets
Aug 8, 2022
The SEQUENCE function is an amazingly useful function in #GoogleSheets. It lets you create sequences of numbers from a single formula. The obvious use case is to create a count up 1, 2, 3, 4, 5 etc.

But did you know you can easily create a count down in your Sheets too? 👇
This single, simple formula:

=SEQUENCE(100,1,100,-1)

counts down from 100 e.g. 100, 99, 98, 97, 96...1

To count down from some other number, for example 50, change it as follows:

=SEQUENCE(50,1,50,-1)

Easy-peasy!
The four numbers you enter as arguments to the SEQUENCE function represent:

✅ Number of rows (e.g. 100)
✅ Number of columns (e.g. 1)
✅ Start number (e.g. 100)
✅ Step to increase/decrease by (e.g. -1)

Arguments 2, 3 and 4 are also optional.
Read 4 tweets
Aug 1, 2022
If you create #GoogleSheets that multiple people use, one super helpful thing you can do is add a sidebar to your Sheets, containing key info or instructions.

Today I'm going to show you how to create sidebars using the group columns feature...
STEP 1️⃣

Highlight some columns, right click, and choose Group columns.

This adds a button above the columns, which you can toggle to show/hide these grouped columns.
STEP 2️⃣

Right-click on the +/- button and select left or right to set the +/- toggle button to be on the left or right of the group.
Read 5 tweets

Did Thread Reader help you today?

Support us! We are indie developers!


This site is made by just two indie developers on a laptop doing marketing, support and development! Read more about the story.

Become a Premium Member ($3/month or $30/year) and get exclusive features!

Become Premium

Don't want to be a Premium member but still want to support us?

Make a small donation by buying us coffee ($5) or help with server cost ($10)

Donate via Paypal

Or Donate anonymously using crypto!

Ethereum

0xfe58350B80634f60Fa6Dc149a72b4DFbc17D341E copy

Bitcoin

3ATGMxNzCUFzxpMCHL5sWSt4DVtS8UqXpi copy

Thank you for your support!

Follow Us!

:(