Generate A Random Item From A List In Google Sheets
These formulas will work when you need to get:
A single random item from a list in a column:
5 random items from a list in a column:
5 random, unique items from a list that contains duplicates (in a column):
In all three, A2:A references the list from which you want an item or items.
Other formulas including getting random items from rows and tables instead of columns AND explanations about how to create these formulas are below.
Google Sheets can randomly select an item from a list with a formula.
Question is, what do you need to do?
How To Randomly Select From A List In Google Sheets
To generate a random item from a list you'll need a formula that combines the INDEX, RANDBETWEEN, and COUNTA functions.
The INDEX function takes in data and outputs a row, column, or single data point from a row and/or column:
- reference = range from which to extract data (row/s and/or column/s)
- [row] = [optional] which row to extract data from (1 = top-most row)
- [column] = [optional] which column to extract data from (1 = left-most column)
To select an item randomly from a list, the reference in your INDEX function will be the list itself.
The [row] and [column] numbers will depend on if you're taking from a column, row, or table:
- List in column = you only need [row] (as there's only one column in reference)
- List in row = you only need [column] (as there's only one row in reference)
- List in table = you need both [row] and [column]
Let's assume you have your list in a column.
The [row] number will need to be a random number between 1 and the number of items in your list.
There's a function for that:
- low = lowest bound of random range (inclusive)
- high = highest bound of random range (inclusive)
Now you're getting somewhere:
The RANDBETWEEN function is what's known as a volatile function.
This means it recalculates every time the sheet changes or recalculates regardless of whether the change or recalculation is associated with the formula that contains the volatile function.
This makes it easy to create a button to generate a random item from your list.
Simply insert a checkbox in a nearby cell (go to
➜ ) and everytime you click the checkbox a new item is chosen at random.If you want to stop this from happening, copy the cell with the formula (Ctrl+C or ⌘+C) and paste it as a value (Ctrl+Shift+V or ⌘+Shift+V).
This removes the formula and leaves you with your random item that won't change.
As mentioned above, to generate a random item from a list your RANDBETWEEN low is always going to be 1 so that it can potentially pick the first item in your list.
That means all that's left to calculate is the high.
You don't want to extract a [row] from your reference list that doesn't exist. That means the high bound should not be higher than the number of items in your list.
There's a formula for that too:
The COUNTA function returns the number of values present, whether they're numbers, text, or boolean.
If you add the INDEX reference as an argument of the COUNTA function, it will return how many items are in the list, which is the number you need for the RANDBETWEEN high.
Here's the final formula:
And an example that generates a random name from a list (click the checkbox to refresh the volatile RANDBETWEEN function):
A | B | C | |
1 | List | Formula | Output |
2 | Michael Scott | =INDEX(A2:A,RANDBETWEEN(1,COUNTA(A2:A))) | Oscar Martinez |
3 | Dwight Schrute | ||
4 | Jim Halpert | ||
5 | Pam Beesly | ||
6 | Andy Bernard | ||
7 | Stanley Hudson | ||
8 | Phyllis Vance | ||
9 | Angela Martin | ||
10 | Oscar Martinez |
There you have it, a working randomizer in Google Sheets!
This is the perfect setup to pick the winner of a contest at random when you have all of the contestants in a Google Sheet.
There is an assumption being made that your list does not contain blanks.
If it does contain blanks the items at the end of your list will not be able to be randomly selected as blanks aren't counted by COUNTA.
As a result, the RANDBETWEEN high won't be enough to pull from the end of the reference list and your random result will sometimes be one of the blank cells.
To avoid this, you can filter out the blanks from the reference list using the FILTER function:
In the example above this formula would be:
If your list is in a row instead of a column, use this formula:
And if you have blanks in the row:
Here you're omitting the [row] from the INDEX function and including only the [column].
If your list is in a table of data (in both rows and columns), use this formula:
It includes both a [row] and [column] in the INDEX function and takes advantage of the ROWS and COLUMNS functions to return how many rows and columns are in the selected range.
FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:
You'll get updates from me with an easy-to-find "unsubscribe" link.
Randomly Select Multiple Items From A List In Google Sheets
To get a random sample from a list in Google Sheets, things get a little more complicated than if you were to just get a single item.
The INDEX function used to solve the single item problem won't work efficiently to solve this problem.
INDEX is designed to return a single value or an entire array of values (whole column or whole row).
What you want is some of the values, not one or all.
So which formula can get you a subset?
ARRAY_CONSTRAIN is a good option.
- input_range = the array of values to get a sample from
- num_rows = how many rows to sample
- num_cols = how many columns to sample
This is perfect! Now all you need to do is randomly sort your list of items and ARRAY_CONSTRAIN will return a sample of however many you need.
And as usual Google Sheets has your back with functions to allow you to do just that:
- range = the array of values to be sorted
- sort_column = which column in the range OR an external column to sort by
- is_ascending = TRUE or FALSE option for ascending or descending sort
- [sort_column2, is_ascending2, ...] = [optional] additional sort columns
For your needs the range would be the list of items to pull from (with a FILTER function attached to remove blanks).
The sort_column would be an array of random numbers with the same number of rows and columns as the filtered range.
is_ascending can be TRUE or FALSE, it doesn't matter as the range will be sorted randomly.
This is where you're up to:
To continue you need an array of random numbers for your sort_column.
Once again, Google sheets delivers:
- rows = the number of rows of random numbers to generate
- columns = the number of columns of random numbers to generate
To get the number of rows and/or columns as the filtered range. You can use the COUNTA function to return the number of non-blank values in the range.
Let's put it all together:
That's it!
Here's a working formula to get 5 items from a list in a column:
Here's an example (click the checkbox to refresh the volatile RANDARRAY function):
A | B | C | |
1 | List | Formula | Output |
2 | Michael Scott | =ARRAY_CONSTRAIN( SORT( FILTER(A2:A, A2:A<>""), RANDARRAY( COUNTA(A2:A), 1), TRUE), 5, 1) | Pam Beesly |
3 | Dwight Schrute | Dwight Schrute | |
4 | Jim Halpert | Stanley Hudson | |
5 | Pam Beesly | Phyllis Vance | |
6 | Andy Bernard | Michael Scott | |
7 | Stanley Hudson | ||
8 | Phyllis Vance | ||
9 | Angela Martin | ||
10 | Oscar Martinez |
Here's a working formula to get 5 items from a list in a row:
Notice that before the SORT can take place the data must be TRANSPOSED into a column and then TRANSPOSED back into a row at the end.
And lastly, here's a working formula to get 5 items from a list in a table:
Here the data is FLATTENED into a single column before being FILTERED, SORTED, and CONSTRAINED.
Randomly Select From A List With No Repeats In Google Sheets
You have a list with some items repeated.
From this list you want to get multiple items without doubling up.
To get multiple random items from this list without repeats you're going to need a slight tweak on the last formula for randomly selecting multiple items from a list.
The tweak is the inclusion of the UNIQUE function:
This function takes in a range (or an array) and returns only the unique rows from that range.
If we take the formula from above (for a list in column A2:A):
You'll need to add UNIQUE at the second last step, after the data is SORTED but before it is CONSTRAINED.
Here's a working formula to get 5 unique items from a list in a column:
Here's an example (click the checkbox to refresh the volatile RANDARRAY function):
A | B | C | |
1 | List | Formula | Output |
2 | Michael Scott | =ARRAY_CONSTRAIN( UNIQUE( SORT( FILTER(A2:A, A2:A<>""), RANDARRAY( COUNTA(A2:A), 1), TRUE)), 5, 1) | Pam Beesly |
3 | Michael Scott | Oscar Martinez | |
4 | Dwight Schrute | Dwight Schrute | |
5 | Dwight Schrute | Michael Scott | |
6 | Jim Halpert | Andy Bernard | |
7 | Jim Halpert | ||
8 | Pam Beesly | ||
9 | Andy Bernard | ||
10 | Oscar Martinez |
Here's a working formula to get 5 unique items from a list in a row:
And lastly, here's a working formula to get 5 unique items from a list in a table:
FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:
You'll get updates from me with an easy-to-find "unsubscribe" link.