How to Select Random Rows from a List in Google Sheets

Adam Steinfurth

This tutorial shows you how to select random rows from a list in Google Sheets. We will review two methods: one that uses built-in functions and another that utilizes the Random Sampler add-on. The starting population from which we will select our sample is a fictional list of employees.

Employee Listing
Population for Sampling

Our example list consists of 14 rows and 3 columns, starting in cell A5 and ending in cell C18.

Method 1: Using Built-In Functions

Let’s first select a random sample of 4 items from this Google Sheets list using a single formula. There are several ways to write the formula, but it quickly becomes hard to understand.

⚠️ We will use the formula below, but note that it may produce duplicate results.

This example is available in this Google Sheet, in case you’d like to make a copy to follow along.

Assuming your list is in A5:C18 (14 rows), use this formula in a cell:

=CHOOSEROWS(A5:C18,RANDBETWEEN(1,14),RANDBETWEEN(1,14),RANDBETWEEN(1,14),RANDBETWEEN(1,14))

How it works:

  • CHOOSEROWS returns chosen rows from a range.
  • RANDBETWEEN generates a random number between two values, in this case, 1 and 14.
A formula creating a random sample of four rows from a list
Four Random Rows

This formula outputs four random items from A5:C18 as an array with a single formula. Keep in mind there may be duplicates.

Notes:
  • The output is dynamic and updates with each sheet recalculation. To “freeze” the sample, copy and paste as values.
  • No manual sorting is needed, making this efficient for quick random sampling.

If you frequently sample in your spreadsheet, you likely need a faster and more flexible solution. Let’s take a look at a random sampling add-on next.

Method 2: Using The Random Sampler Add-On

For a simpler approach with no formulas, use the Random Sampler add-on.

Steps to install the Random Sampler add-on
Steps to Install the Add-On

Install Random Sampler from the Google Workspace Marketplace (Extensions > Add-ons > Get add-ons).

Once installed, you can start the add-on, then highlight your list (e.g., A2:A101).

Selecting the sample with Random Sampler
Selecting the Sampler

The add-on outputs the selected rows in a new sheet, requiring no formulas.

Conclusion

Both methods are effective, but the add-on simplifies the process for frequent sampling, while formulas offer more control and transparency.

Similar Articles