Excel Random Row Selector

The Latest AI News:

1. OpenAI released o3 and o4-mini models

2. Anthropic outlines a framework for mitigating AI-related harms, balancing risks and responsible development.

3. Google’s giving college students free storage and cutting-edge AI access.

SPONSOR

Convert PDFs to Excel fast. Automate repetitive tasks without leaving the comfort of your spreadsheet. Try Lido now.

MAIN ARTICLE

Excel Wheel of Fortune

Let’s do something fun in Excel today. (yes, it’ll work fine in Google Sheets too).

I recently had the need to pick a random entry from an Excel table. In due spreadsheet fashion, I wanted the convenience of a formula plus the satisfaction of a checkbox being ticked.

We can do this by nesting several functions in one cell that simply reference a checkbox (now available natively in Excel).

Video Walkthrough

Randomize by ROWS

We’ll use a table with 200 rows for this example, so the first thing I want to do is simply pick a number between 1 and 200 (inclusive) to represent which row will ultimately be picked.

The RANDBETWEEN() function does this nicely when paired with the ROWS() function.

I’ve named our table of data, Customers. So, by writing =RANDBETWEEN(1,ROWS(Customers)) we will return a random number between 1 and 200.

ROW vs ROWS

A bit of definition here before we write the full formula.

• ROWS gives us the number of rows in a table so our random number picker works.

• ROW returns the actual row number.

We’re going to use these both in a clever way in the next step…

INDEX

Index returns a value or reference to a value from a table or range. We’ll need this as well when we write the full FILTER function.

By writing =INDEX(Customers[email],RANDBETWEEN(1,ROWS(Customers))) we can grab a random email address out of the email column.

Then by wrapping it in ROW, we can turn that into simply the row number that the email address occurs on.

FILTER the Table for the Result

In order to only return the one row that we randomize, we’ll want to use the FILTER function. Check out below:

=FILTER(
Customers,
ROW(Customers[email])=ROW(
INDEX(
Customers[email],RANDBETWEEN(
1,ROWS(Customers)))))

We want to filter out every row except for one in our Customers table. So, the first argument in our FILTER function is the table itself followed by some confusing logic:

1. ROW(Customers[email] simply gets the row number of each email entry (I used the email column arbitrarily).

2. Then we check if that number is equal to the row number of INDEX(Customers[email], RANDBETWEEN(1,ROWS(Customers)))

LET it be simpler

We can make it a hair more readable by using LET if we want to flex our Excel muscles. It doesn’t make it any shorter to write, but it allows us to take that RANDBETWEEN part and pull it out to the start.

Let me show you:

=LET(
  randRow, RANDBETWEEN(1, ROWS(Customers)),
  FILTER(Customers, ROW(Customers[email]) = ROW(INDEX(Customers[email], randRow)))
)

The first line in the LET function allows us to declare a variable, randRow (although we can name it anything we want) and then define that variable: RANDBETWEEN(1, ROWS(Customers))

So, we are saying that

• there’s a variable called randRow

• it’s equal to RANDBETWEEN(1, ROWS(Customers))

This is very powerful especially in longer, more complex formulas. Instead of writing the whole RANDBETWEEN part, all we have to do is write randRow.

This is exactly like referencing a table’s name instead of the full A1 notation cell range.

The Free Sheet

Here’s a link to the Google Sheet project.

NEXT STEPS

Whenever you’re ready, here’s how I can help:

1. Business tech stack (FREE)
   My recommendations for software and tools I use to create content and run my online business.

2. Sponsor Got Sheet
   Got Sheet teaches business operators, teachers and entrepreneurs how to get good at spreadsheets. Interested in sponsoring an issue or a series of Got Sheet newsletters? Reach out to get more information.

3. Personal budget tool
   As a Got Sheet subscriber, I want you to have a 50% discount on the personal finance system I update every year.

4. YouTube

   If you aren’t subscribed yet, come on over to my YouTube channel where I make all my spreadsheet, coding and productivity tutorials

HAPPY SPREADSHEETING!

Enjoyed this issue?
Subscribe below or Leave a testimonial

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.