random assignment google sheets

How To Randomize a List in Google Sheets (3 Easy Ways)

• Last updated January 25, 2024

To randomize a list in Google Sheets, use the randomize range feature. My guide shows how to use this, filter and sort, and other methods to randomize a list.

Table of Contents

Introduction to Randomization in Google Sheets

A random list can be really helpful when you are trying to make sure there’s no bias in the sequencing or selection of the items from the list.

There are many reasons to randomize or shuffle a list of items . For example, you might want to randomly select participants for an event, or you might want to randomly sort people or items together.

Google Sheets has recently introduced the new ‘Randomize range’ feature specifically to cater to creating a Google Sheets randomize list. You simply have to right-click and select Randomize range to use it. In this tutorial, we will show you how to randomize a list in Google Sheets in three ways:

• The new way using the Randomize range Feature
• The traditional way using a Filter and Sort
• Using a Google Sheets randomize list Formula

To demonstrate all three methods, we are going to use the following sample list:

How to Randomize a List in Google Sheets

Here’s how to randomize in Google Sheets in two steps:

• Select the list of items
• Right-click and select Randomise range.

This method is the simplest and uses the inbuilt randomizer in Google Sheets.

How to Randomize a List in Google Sheets Using the Randomize Range Feature

The first and quickest way to randomize a list of items is by using the new ‘ Randomize range’  feature. This method is simple, involving just a few clicks.

To randomize or shuffle the above list, all you need to do is follow the steps below:

Step 1: Select the list of items (cell range A2:A10 in our example).

Step 2: Right-click on your data range and select ‘Randomize range ’ from the context menu that appears. Alternatively, you can select the Randomize range  option from the Data menu.

Your list should now be randomly sorted.

How to Randomize a List in Google Sheets using the Filter and Sort Feature

In Google Sheets, the traditional method to randomize Google Sheets lists has been to use the RAND function to generate a list of random numbers  between 0 and 1 and then use Filter and Sort function  to sort the list along with the random numbers.

Unlike the first method, this method is not limited to Google Sheets and can also be applied to other spreadsheet software like Microsoft Excel. This method lets you create a Google Sheets random order by using a helper column.

The sort function is used to alphabetize  a list, but in this case, it will be used to arrange a list of numbers in an ascending or descending order.

Here are the steps for following this method to sort the items in the sample list shown above:

Step 1: In a blank column next to your list, type the formula: =RAND() and press the return key.

Step 2: Drag down or double click the fill handle of this cell to copy the formula to the rest of the cells in the column. Keep in mind the random numbers generated will be different from the original data.

Step 3: You should now see a list of random numbers between 0 and 1. Select any one of the cells in this column.

Step 4: From the Data menu, select the option ‘ Sort Sheet by Column B A→Z ’.

Step 5: Select the column containing the random numbers (column B in our example) and delete it.

Your list of items should now get sorted in random order.

Note:  You might find the column header of your list sorted along with the rest of the items in the list (as shown in the above image). This is because this method of Google Sheets random sorts the entire sheet, without any option for selecting the items that you want to sort. If this happens, simply insert a new row before the first row, and then cut and paste the header back to the top.

Note:  Since the RAND function is a volatile function, you will find your list resorted every time there is a change in the sheet. So, to avoid this, make sure you first delete the list of random numbers before making further changes.

The random sort Google Sheets method is quick and easy but it has the disadvantage that it sorts the entire worksheet. So if you have other rows in the worksheet that you don’t want to randomize, it could create problems.

So, here’s another way to use this method while making sure that only your selected range of items gets sorted:

Step 2: Drag down or double click the fill handle of this cell to copy the formula to the rest of the cells in the column.

Step 3: Select the range of items in your list, along with their corresponding cells of random numbers, as shown below:

Step 4: Right-click on your selection and click on ‘ Sort Range’.

Step 5: This will open the ‘ Sort range’  dialog box. From the dropdown menu next to ‘ Sort by ’ select ‘ Column B’ .

Step 6: Click the Sort  button.

Step 7: Select the column containing the random numbers (column B in our example) and delete it.

Now only your selected cells will be randomly sorted.

How to Randomize a List in Google Sheets Using a Formula

If you prefer using a formula, rather than clicking menus, here’s another way. In this method, we basically do the same  randomize and sort  process as we showed in the previous section, but with a single formula.

The formula we use is:

=SORT(A2:A10,ArrayFormula(RANDBETWEEN(SIGN(ROW(A2:10)),1000000)),True)

Simply copy this formula into a blank cell in a new column, replacing the ranges A2:A10 with references to the range of cells in your list.

When you press the return key, you will get a new list of the same items, arranged in random order.

This method is great if you don’t want to disturb the original list of items, since it lets you randomize the list in a separate column.

Note:  Make sure the column containing your formula is a blank one. If any of the cells in the column have data in them, the formula will return a #REF! error.

If you are interested to know the difference between the RAND and RANDBETWEEN functions, you can check out our article on generating random numbers .

Google Sheets Random from List No Repeats

The problem with the above methods for Google Sheets to randomize a list is that there will likely be duplicates of the generated number. It’s much easier to randomize a list without repeats in Excel than it is in Google Sheets.

However, it is still possible to do a Google Sheet random from a list with no repeats, but you will need a helper column.

Let’s look at our example above. We might want to assign each person in the list a partner randomly. If you want to avoid repeats, here’s how to randomize in Google Sheets:

Step 1: Copy the column with data using the keyboard shortcut CTRL + C and paste it in the adjacent cell using CTRL + V.

Step 2: You can now use the method to randomize column in Google Sheets. Go to Data > Randomize range.

Step 3: Do the same for the remaining two columns. Once you’re done you will have you’re randomized list, but you’ll notice that some names are repeated, like Ted in the first row.

Step 4 : Using the formula =COUNTUNIQUE(A2:D2), we can find out which rows have repeating names. Since there are 4 columns there should be 4 unique values therefore any row that has less than 4 has repeats.

Step 5 : If there are too many repeats, you can try randomizing the column again. Then you can manually find and remove the repeats in each row.

In this tutorial, we covered how to randomize a list in Google Sheets. Besides the traditional methods, which involve using the RAND or RANDBETWEEN functions, we also discussed the new ‘Randomize range ’ feature of Google Sheets. This is something that comes in handy for all sorts of situations, including building TTRPG spreadsheets or assigning work shifts.

If you have any questions, please let me know in the comments!

• How to Round Numbers in Google Sheets
• How to Divide in Google Sheets (Numbers, Cells, or Columns)
• How to Number Rows in Google Sheets (Add Serial Numbers)
• ISNUMBER Function in Google Sheets (Check if Cell Contains Number)
• Google Sheets Football Squares Template
• How to make a Random Number Generator in Google Sheets
• How to Make a Google Sheets Drop Down List

Most Popular Posts

How To Highlight Duplicates in Google Sheets

How to Make Multiple Selection in Drop-down Lists in Google Sheets

Google Sheets Currency Conversion: The Easy Method

A 2024 guide to google sheets date picker, related posts.

How to Zoom Out in Google Sheets and Zoom Back In [Easy]

• Sumit Bansal
• July 3, 2024
• May 16, 2024
• May 2, 2024

How to Insert a Google Sheets Hyperlink in 5 Seconds

• Chris Daniel
• April 15, 2024

Thanks for visiting! We’re happy to answer your spreadsheet questions. We specialize in formulas for Google Sheets, our own spreadsheet templates, and time-saving Excel tips.

Note that we’re supported by our audience. When you purchase through links on our site, we may earn commission at no extra cost to you.

Like what we do? Share this article!

• Google Sheets
• Sheets Vs. Excel

How to Generate Random Groups in Google Sheets

How to Create a Running Sum with Negative Value Carryover

ISEMAIL Function: Verify Email Addresses in Google Sheets

How to Add a Total Row to a Google Sheets Data…

Visually Track Where Your Data Falls Within Limits (Google Sheets)

Automatically Pre-fill Google Forms from Google Sheets: A Step-by-Step Guide

How to Set Up Google Docs Forms: A Comprehensive Guide

How to Insert Drop-downs in Google Docs Documents

How to Create a Table and Pin and Unpin Header Rows…

How to Use Section Break in Google Docs

How to Split a Table in Google Docs

How to Create First Line Indent and Hanging Indent in Google…

Excel LAMBDA: Creating Named and Unnamed Custom Functions

Dynamic Moving Average in Excel

Excel: Calculating Cumulative Sum by Group

Excel: XLOOKUP for First and Last Non-Blank Value in Row

Highlight Groups with Alternating Colors in Excel

Display Month Names Only at Month Start (Excel & Google Sheets)

SUMIFS with XLOOKUP in Excel and Google Sheets

Counting XLOOKUP Results with COUNTIFS in Excel and Google Sheets

SORT and SORTBY – Excel Vs Google Sheets

SUMPRODUCT Differences: Excel vs. Google Sheets

Interactive random task assigner in google sheets.

Published on

You have multiple tasks and multiple people. Here’s a fun way to randomly assign a task to a random person in Google Sheets.

The fun element lies in the automation of the random task assignment. Here’s how it works:

The top row contains tasks, while the first column contains names (students, employees, or your friends’ names), with the grid between them filled with checkboxes.

The free template initially assigns a random task to a random person. It does this by highlighting an unchecked tick box randomly.

Once you tick that box, it randomly highlights another tick box, and this process continues until you tick all the checkboxes.

You can use this interactive Random Task Assigner template straight out of the box. However, I strongly suggest following the step-by-step instructions below to help you adjust the template to meet your specific requirements.

Free Random Task Assignment Template for Google Sheets and How to Use It

You can preview and obtain a copy of this free Random Task Assigner template by clicking the button below.

The template is configured for 20 tasks and 20 people, but feel free to adjust the number of columns (tasks) and rows (names) as needed.

To remove a column, simply right-click on the column letter at the top and select “Delete column” from the context menu.

To delete a row, right-click on the row number on the left and choose “Delete row” from the context menu.

If you have additional tasks or names, insert rows or columns within the grid, avoiding the beginning or end. You can access these options by right-clicking the row number or column letter and selecting the relevant options from the context menu.

Replace ‘Task 1’, ‘Task 2’, etc. with your specific tasks and ‘Name 1’, ‘Name 2’, etc. with actual names.

Once you’ve made your adjustments, click the drop-down menu (currently located in cell V2) and select “Yes” to initiate the highlighting process. Do not select “No” afterward.

Click on the checkbox in the highlighted cell (representing the randomly assigned task in the grid). This will prompt the highlighting of another task. Continue this process until you have checked all cells.

What happens if I tick a checkbox that isn’t highlighted?

You can tick one or more checkboxes to prevent the template from highlighting them. For instance, if you wish to exclude Task 10 from the assignment, select cells K2:K21 and press the spacebar. This action ticks all the checkboxes in that range, indicating that these tasks are not to be assigned to anyone.

If you encounter any issues with the Random Task Assigner template not responding to user interaction, you can resolve it by following these steps:

• Copy the formula located in cell W2.
• Delete the formula in cell W2.
• Paste the copied formula back into cell W2.

This should ensure that the template functions properly.

The Key Formula Used in the Random Task Assigner and Its Role

The template incorporates a formula within a cell and another formula within conditional formatting.

The pivotal formula, found in cell W2, is as follows:

What role does this formula play in assigning a random task to a random person?

This formula returns a random cell address from within the grid while ensuring that it excludes cells that have already been ticked.

Here’s a breakdown of the formula in its simplest form:

LET assigns the name ‘grid’ to the range B2:U21, which contains the tick boxes.

Generate the addresses of unticked cells within the ‘grid’ using the formula:

The result is assigned to the variable ‘test’.

Sort the cell addresses returned in the previous step in ascending order based on randomly generated numbers by the formula:

This formula returns one random cell address, as the SORTN function returns sorted ‘n’ values, where ‘n’ in this case is 1.”

Highlighting a Cell in the Grid Matching the Cell Address

We have a random cell address representing the random task to assign. We’ll employ conditional formatting to highlight the corresponding cell within the grid.

I’ve applied the following rule, which tests whether each cell address in the grid is equal to the cell address returned by the key formula in cell W2:

If a match occurs, the cell highlights.

Here are some related topics in Google Sheets:

• How to Pick a Random Name in Google Sheets (Does Not Refresh)
• Google Sheets: Macro-Based Random Name Picker
• How to Randomly Select N Numbers from a Column in Google Sheets
• How to Randomly Extract a Certain Percentage of the Rows in Google Sheets
• How to Generate Odd or Even Random Numbers in Google Sheets
• Pick Random Values Based on Conditions in Google Sheets

• Conditional Formatting
• Spreadsheet

More like this

Leave a reply cancel reply.

Save my name, email, and website in this browser for the next time I comment.

This site uses Akismet to reduce spam. Learn how your comment data is processed .

Return to Excel Formulas List

How to Make Random Groups in Excel & Google Sheets

Editorial Team

Reviewed by

Steve Rynearson

Download the example workbook

This tutorial will demonstrate how to make random groups in Excel and Google Sheets.

Random Groups

To randomly assign items (data, people, etc.) to groups we can use the RANDBETWEEN and CHOOSE Functions:

RANDBETWEEN Function

The RANDBETWEEN Function generates random numbers between two integers. In this example it will generate numbers 1, 2, 3, or 4.

CHOOSE Function

The CHOOSE Function selects an item from a list by it’s position.

By combining the two functions we can randomly “choose” an item from a list, thus assigning people to groups as in the first example.

Random Groups – Same Size

The example above will assign people to completely random groups. The resulting groups may not be of the same size.

To assign people to same sized groups, we can use the ROUND, ROUNDUP, and RANK Functions:

This calculation is a bit more complicated, so let’s walk you through it.

RAND Function

First, we use the RAND Function to generate random decimal values between 0 and 1 for each person:

Now that we have random numbers for each person we have a way to split them into groups.

RANK Function

Next we will use the RANK Function to identify where each random value “ranks” in the list of random values.

The RANK Function will return a number between 1 and the total number of people in the group. The largest random number will receive 1, second largest will receive 2, etc.

Next we will divide the result of the RANK Function by the desired group size. (ex. 4 for a group size of 4 people per group).

Notice that this returns decimal values between 0 and 4.

ROUNDUP Function

Last, we will apply the ROUNDUP Function to the result to round the decimal values up to integer values 1,2,3, and 4.

This gives us four groups of equal size.

Note: Group 4 only has 1 person assigned, because there are not enough names shown to fully populate group 4.

Note 2: By setting the num_digits input to 0, we tell the ROUNDUP Function to round up to the nearest integer.

Random Groups in Google Sheets

All of the above examples work exactly the same in Google Sheets as in Excel.

AI Formula Generator

Try for Free

• Excel Help Bot
• Formula Generator
• Formula Explainer
• VBA Code Generator
• VBA Code Commenter
• Excel Template Generator

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

• Automatically Graded Exercises
• Learn Excel, Inside Excel!

Free Download

How to Randomize a List in Google Sheets (3 Quickest Methods)

Posted on Last updated: November 3, 2023

Google Sheets’s newly launched “Randomize Range” feature lets users randomize a list. You need to go to the main menu and click on “ Data ” followed by “ Randomize range ” to access this feature.

The Sort and Filter tools in Google Sheets are most commonly used to organize datasets in particular order.

However, there are a few scenarios where you may wish to sort the dataset in a completely random order. For example,

• Sorting student names in random order for group studies or games
• Picking up participants randomly for an event
• Selecting a winner for a giveaway

If you are searching for the quickest method to randomize a list in Google Sheets, then you have come to the right place.

In this article, we will discuss several methods to randomize a list in Google Sheets. It includes using built-in features and functions that may help you now as well as in the future, so make sure to read the article till the end.

Here are the three methods to randomize a list in Google Sheets. You can click on the links to jump to that particular section of the article.

• Using the Randomize range feature – (Quickest Method)
• Using the RAND function and Sort tool
• Using the ARRAYFORMULA function

Copy the Example Google Sheet

Now, before we dive deep into each method of randomizing a list in Google Sheets, consider getting the following Google Sheets data and following me as I take you through this article.

Click Here to!

The example Google Sheet contains datasets used to demonstrate various features and functions to randomize a list.

If you have your own Google Sheet ready to practice the things we are about to discuss, then skip downloading the above file.

Let’s get started now without any further ado!

How to Randomize a List in Google Sheets

As mentioned above, you can choose from various methods to randomize a list.

We will start with the quickest method to randomize a list, then dive deep into using the built-in functions to get almost similar results.

The good thing is you don’t need to be an expert to use any of these methods.

Here is the list of the Giveaway participants,

Task: Our job is to randomize the above list and pick up three winners.

Now, let’s get started.

METHOD #1 – Using the Randomize range feature in Google Sheets to randomize a list

Google Sheets has recently announced this feature to let users quickly randomize datasets.

It is pretty straightforward. Before this announcement, users had to go through a comparatively lengthy workaround to randomize datasets.

Here are the steps,

• Open the Google Sheet
• Select the range you wish to randomize (Note that you need to avoid including the column header while selecting the range. In case you don’t have a header, then simply select the entire column and follow the steps below)
• Right-click to see more options
• Next, choose the “ View more cell actions ” option from the popup
• Click on “ Randomize range “

You will see that all the cells are shuffled randomly, as shown in the above GIF.

If you are not happy with the results, then make sure to use the “Randomize range” feature several times.

Q. What is another way to access the Randomize range feature in Google Sheets?

You have two options to apply the Randomize range to a list.

The first one is to right-click and select the “View more cell actions” option from the popup, as discussed above.

For the second one, here are the steps:

• Go to the main menu
• Click on the “ Data ” tab

Both of them require you to do a few clicks. So, you can choose it as per your preference.

METHOD #2 – Using the RAND function and Sort tool in Google Sheets to randomize a list

This was the most popular method to randomize a list in the past. Before Google Sheets introduced the Randomize range feature, it was my go-to method.

In this method, you need to create a helper column and use the RAND function in Google Sheets to generate random numbers.

Those random numbers are then sorted in ascending or descending order using the Sort tool.

Before we dive deep into this method to randomize a list, let us have a quick look at the RAND function.

Explained: The RAND Function in Google Sheets

The RAND function generates a random number between 0 and 1 without any argument.

Here’s the general syntax for the RAND function,

This function automatically generates a new number each time you open the Google Sheet or perform any calculations within the spreadsheet. In other words, the calculations are real-time and vary depending upon the changes made to the Google Sheet.

Avoid inserting any values or providing the cell references for this formula, as it doesn’t accept them and returns an error.

That’s all about the RAND function. 

Now, let us use it to randomize a list in Google Sheets.

Step 1: Prepare a helper column using the RAND function

We will start by adding a column next to the main list, as shown below.

You can name the column as per your preference. For the sake of simplicity, we have named it Helper.

Now, we will generate random numbers using the RAND function. Here are the steps:

• Click on the cell “ B2 “
• Type “ =rand ”
• Select the first option from the popup or press the “Tab” key
• Close the bracket using “ ) “
• Press “ Enter “

• You will see a “ + “ icon
• Click on it and drag that “+” icon to the end of the table, as shown in the following GIF

Here’s how our helper column looks like,

Note that all the random numbers generated above would change each time you perform a certain calculation on them or even add new entries to the spreadsheet.

For example, I have added random input “ asd ” into the cell “ D1 “. As soon as I do it, the numbers are generated again. Refer to the following GIF.

Step 2: Randomize the dataset using the Sort tool

In this step, we will sort the entire dataset by the numbers generated using the RAND function.

Let’s begin,

• Select the entire table

• Click on the Filter icon

• Go to the cell “ B1 ” and click on the filter icon
• Select the first option, “ Sort A to Z “

Google Sheets will instantly sort the table based on current numbers generated using the RAND function. Also, at the same time, new numbers are generated, which can be used to randomize the list further.

The RAND is a volatile function that allows you to repeat the above process an infinite number of times to randomize a list.

METHOD #3 – Using the ARRAYFORMULA function in Google Sheets to randomize a list

In this method, along with the ARRAYFORMULA, we need to use various built-in functions in Google Sheets.

We will combine all those functions to build a formula as follows,

It is a quite lengthy formula that randomizes a list every time you open the Google Sheets or perform any calculation within the spreadsheet.

Before we dive deep into the actual steps, let us quickly discuss each function used in the above formula so that you will understand how it is built.

Explained: The ARRAYFORMULA Function in Google Sheets

As from the name itself, it is an array formula that has the following general syntax.

The “ arrray_formula ” argument accepts cell range or an expression including the cell range as an input.

So, based on that particular cell range or array size, the function performs calculations. If you don’t put any expression (that includes the cell range), the function will simply display the entire cell range as is.

Explained: The SORT Function in Google Sheets

The SORT is a powerful function in Google Sheets . It allows users to sort datasets by single or multiple columns in ascending or descending order.

The general syntax for the SORT function is as follows,

Here’s how to deal with each argument of the SORT function:

• “ range ” – It needs to be replaced with the cell or table range, which needs to be sorted.
• “ sort_column ” – Here, particularly in the case of the table, you need to define the column which you wish to sort.
• “ is_ascending ” – This argument lets you decide if the sorting should be applied in ascending or descending order. It is an optional argument that accepts “TRUE” or “FALSE” as input.

Note that with the SORT function, you can sort the table using multiple columns.

Explained: The RANDBETWEEN Function in Google Sheets

This function is similar to the RAND function discussed in the second method of randomizing a list in this article.

The only difference is that the RANDBETWEEN function lets you generate random integer numbers within a specified range.

The general syntax for the RANDBETWEEN function is as follows,

The arguments are self-explanatory from the name itself.

• “ low ” – Here, you need to define the lower bound of the range
• “ high ” – It is the upper bound of the range

The function is pretty straightforward. Note that it is a volatile function that updates the random number each time you open the Google Sheet or perform any calculation within the spreadsheet.

Explained: The SIGN Function in Google Sheets

The SIGN function in Google Sheets is used to return the sign of a given number.

It evaluates the sign and returns “1” if it is positive or “-1” if it is negative. Whereas, for the zero as an input, the function returns “0”.

Here is the general syntax for the SIGN function in Google Sheets,

Where the “ value ” argument needs to be replaced with the integer number whose sign needs to be evaluated. You can either manually enter the number or provide the cell reference holding the number.

It is combined with the other functions in Google Sheets to create powerful formulas.

Explained: The ROW Function in Google Sheets

The ROW is a simple lookup function in Google Sheets. It returns the location of a given cell or range of cells.

Simply put, the function returns the row number within a specific cell range.

The general syntax for the ROW function is as follows,

Here, the “ cell_reference ” argument is optional. If you don’t specify any value, then the function will return the row number where you have entered the formula.

You can provide a cell reference or range to this function.

Now that we have learned all the functions let’s combine them to create a formula capable of randomizing a list in Google Sheets.

• Type “ =sort ”
• Select the first option from the popup or press “Tab” key
• Provide the cell reference “ A2:A21 ” for the “range” argument of the SORT function
• Press “ , ” to move to the next argument

• Select the first option from the popup
• Now, for the “array_formula” argument of the ARRAYFORMULA function, we will type “ randbetween “ (Here, we are creating an expression using the RANDBETWEEN function. It will generate a random number for the cell range defined in the ARRAYFORMULA function)

• Type “ sign ”

• Type “ row ”

• Provide the cell reference as “ A2:A21 ” for the “cell_reference” argument of the ROW function
• Close the bracket for the ROW function using “ ) “
• Close the bracket for the SIGN function using “ ) “
• Now, you will be asked to provide the information for the second argument (“high”) of the RANDBETWEEN function. Let’s type “1000” (Make sure to choose the number that will be greater than the size of the column range)
• Close the bracket for the RANDBETWEEN function using “ ) “
• Close the bracket for the ARRAYFORMULA function using “ ) “
• Next, you will be asked to replace the “is_ascending” argument with the proper information. Let us type “ TRUE ” and sort the cell range in ascending order
• Close the bracket for the SORT function using “ ) “
• Press the “ Enter ” key

Here’s how our formula looks after following the above steps,

As you can see from the above GIF, after typing the formula, Google Sheets will instantly sort the names of the participants in a random order.

Note that this is a volatile formula due to the use of the RANDBETWEEN function. It means that if you perform any calculation, add entries, or perform any operation, the sorting will be automatically adjusted.

For example, let us add “ asd ” in the “ D1 “.

As seen in the above GIF, the sorting is automatically updated.

The above methods use built-in functions and features by Google Sheets to randomize a list. Additionally, there are a few third-party add-ons that get the job done for you.

However, with the newly launched Randomize range feature, you don’t need anything else to randomize a list in Google Sheets.

I hope this article was helpful to you.

Feel free to comment below if you are still having any issues or are stuck somewhere while randomizing a list using one of the methods discussed in this article.

Looking forward to answering all your questions.

How To Randomize A List In Google Sheets

Last Updated on January 2, 2024 by Jake Sheridan

Generally when people think about the order of data in a spreadsheet, they want to sort that data in a specific way.

But you may also run into situations where you need to randomize a list in.

For example, if you want to create random break out or study groups for a meeting or class.

Instead of trying to randomize a list manually or relying on complex formulas to randomly select each entry from the sorted list, you can automatically randomize a range in Google Sheets quickly in just a few clicks.

Step 1) Access the randomization feature

Step 2) confirm the randomization, step 3) repeat if necessary.

Here’s how shuffle rows in Google Sheets:

Select the range you want to randomize, excluding headers.

Randomization is done based on rows, so you must select at least two rows, however you can select any number of columns

Open the Data menu and click on the Randomize Range option

The rows in the selected range will be sorted into a random order. Note that any blank rows in the range will be moved to the end of the list

You can repeat the process as needed to get a different random order of the rows in the selection

Example Spreadsheet: Make a copy of the example spreadsheet

In this tutorial, I covered how to randomize a list in Google Sheets. Want more? Check out all the Google Sheets Tutorials .

More ways to sort in Google Sheets:

• How To Sort
• Format Dates
• Stop Google Sheets From Changing Dates
• Sort Alphabetically
• Arrange Numbers In Ascending Order
• Sort By Color
• Sort By Date
• Change Date Format
• Sort By Number
• Sort By Last Name
• Sort And Filter
• Sort Unique Values
• Sort In Google Sheets And Keep Rows Together
• SORTN Function

You might also like:

• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand
• OverflowAI GenAI features for Teams
• OverflowAPI Train & fine-tune LLMs
• Labs The future of collective knowledge sharing
• About the company Visit the blog

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

Simple way to choose random cell from a list in Google Sheets

Do you have a list of names in one column and want to draw between them?

Here is the simplest way to do this.

• google-sheets
• google-sheets-formula

2 Answers 2

one character shorter solution would be:

• 1 =offset(A1,randbetween(0,9),0) or even offset(A1,rand()*10,0) I guess but not worth a separate answer –  Tom Sharpe Commented Dec 7, 2021 at 12:31

For this we will use two functions: INDEX and RANDBETWEEN

If you want to randomly choose one cell between A1 and A10 the formula would look as follows:

And if you decide to make more than one draw, you can type CONTROL+R , automatically the formula is updated within the cell, delivering a new result.

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Sign up or log in

Post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy .

Not the answer you're looking for? Browse other questions tagged arrays google-sheets random indexing google-sheets-formula or ask your own question .

• The Overflow Blog
• Unpacking the 2024 Developer Survey results
• Featured on Meta
• We've made changes to our Terms of Service & Privacy Policy - July 2024
• Introducing an accessibility dashboard and some upcoming changes to display...
• Tag hover experiment wrap-up and next steps

Hot Network Questions

• What happens if your child sells your car?
• Who‘s to say that beliefs held because of rational reasons are indeed more justified than beliefs held because of emotional ones
• Implication in one-to-one function definition
• Mobility of salts on unbonded bare silica HILIC column
• What are these red pointy flower buds with heart-shaped leaves?
• Does wisdom come with age?
• How does a plane literally "fall from the sky"?
• If it's true that a reservation fee for a cruise ship is non-refundable, then what happens if somebody cancels?
• Combining Regex and Non-Regex in the same function
• Will a spaceship that never stops between earth and mars save fuel?
• How does "regina" derive from "rex"?
• What is “were’t”?
• Mathematical Theory of Monotone Transforms
• Netgate 6100 Will Not Acknowledge Lastest Versions of pfSense (When Checking For Updates)
• Why, fundamentally, does adding sin graphs together always produce another sin graph?
• Refereeing papers by people you are very close to
• Positive Freedom v. Negative Freedom: a binary or a spectruum?
• Search values by priority in stream
• English equivalent to the famous Hindi proverb "the marriage sweetmeat: those who eat it regret, and those who don't eat it also regret"?
• In Europe, are you allowed to enter an intersection on red light in order to allow emergency vehicles to pass?
• Solving the "reverse" Assignment Problem?
• Statistically, which is more of a severe penalty on a d20 roll, Disadvantage or a -10 penalty?
• How to write this X=1 to L with curvy brackets in LaTeX? (image included)
• Is Marisa Tomei in the film the Toxic Avenger?

Randomly Assign Teams in Google Sheets

• May 4, 2023
• Google Sheets Tutorials
• Ryan Morton

In this article, you will learn how to set up Google Sheets to randomly assign a group of participants to any number of teams.

Once you set up the spreadsheet, you only have to click twice to assign everyone to a team. And if you don’t like the results, you can re-randomize the assignments again!

Before getting into any functions or formulas, you need to set up your spreadsheet. Start by listing the team names across columns in row 1.

Next, list the participants in a single column to the right of your team names.

With your spreadsheet setup like this, you are ready to compose the formula .

The Formula

Add the following formula to cell A2.

This formula uses the WRAPROWS function to take the participants and split them up across all of the teams.

The first argument of the WRAPROWS functions references the range of cells containing all of the participants. In this case, the participants are listed in the range of E2:E10.

The second argument of the WRAPROWS function is the number of teams you have. In this example, there are three teams.

With your formula set up, all that’s left is to randomize the assignments.

Randomize the Range

To randomize the team assignments, you must randomize the original participant list.

To do this, select the range of cells containing the participants. With this range selected,  right-click  and select  View more cell actions , and then  Randomize range .

This will shuffle the list of participants, thereby resulting in randomized team assignments.

And that is how you can randomly assign teams in Google Sheets.

See It In Action

Check out the video below to see this method demonstrated in Google Sheets.

This Post Has 0 Comments

Leave a reply cancel reply.

Your email address will not be published. Required fields are marked *

Save my name, email, and website in this browser for the next time I comment.

Notify me of new posts by email.

This site uses Akismet to reduce spam. Learn how your comment data is processed .

Related Posts

How Location Affects Formula Syntax in Google Sheets

Lookup the Smallest or Largest Value

How to List Missing Values in Google Sheets

• previous post: Build a Heat Map in Google Sheets
• next post: Filter by Color in Google Sheets

Privacy Overview

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 Insert ➜ Checkbox ) 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):

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:

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):

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 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 :

You might like:

TRIM Google Sheets Function [With Quiz]

The TRIM function in Google Sheets cleans text for you! Leading, trailing, and unnecessary spaces are removed so you get only validated text.

Formula Parse Errors In Google Sheets & How To Fix Them

Experiencing formula parse errors in Google Sheets? Find out how to fix every type of error (and learn about error handling) with this guide.

How To Generate Random Numbers In Google Sheets

Google Sheets generates random numbers. Learn all about random numbers as decimals, integers, between two numbers, and buttons to make them!

Want Better-Looking Google Sheets?

Get my 12-tip cheat sheet that will make your spreadsheets more user-friendly.

Tips, tutorials, and add-ons

Taking a Random Sample in Google Sheets: Two Methods

Selecting random data points from your spreadsheet is crucial for statistical analysis. We will review two methods to accomplish this. The first uses traditional spreadsheet functions, and the second uses the Random Sampler add-on for Google Sheets.

• 1 Define the Starting Population
• 2 Method 1: Sampling with the “Randomize Range” Option
• 3 Method 2: Random Sampler Add-on
• 4 Choosing the Right Method
• 5 Video Tutorial

Define the Starting Population

We will use a list of grocery store transactions as our starting population. In this context, the word population refers to the data you want to sample. The table contains four columns and 101 rows. Row 1 contains the headers and rows 2 through 101 contain the data.

Get a copy of the Google Sheet with this sample data to save as your own spreadsheet in Google Drive.

Method 1: Sampling with the “Randomize Range” Option

This method utilizes the built-in power of Google Sheets. There are just a few steps, so let’s jump in. Here’s how it works:

Identify Your Population: Decide what data you want to sample. In this case, it will be the one hundred rows of grocery store transactions. Highlight this range except for the header row. The range is A2:D101 in the example sheet.

Choose Randomize Range: Click on Data , then choose Randomize range .

Google Sheets randomly rearranges the rows in the range.

Use the Top x Rows: Now select as many rows as you want from the top of the range.

Copy and paste these rows to use them as your random sample.

This technique is simple and should work on any data set. However, it has its limitations. It leaves the data in a different order and only offers one type of sampling. Let’s look at an add-on next that will leave your data intact and offer other sampling methods.

Method 2: Random Sampler Add-on

For a quicker approach, consider using the Random Sampler add-on. Start Google Sheets then follow these steps:

Install the Add-on: Go to Extensions > Add-ons > Get Add-ons . Search for “Random Sampler” and install it.

Launch the Add-on: Click on the Random Sampler icon in the side panel. You may need to expand your Side panel if you don’t see it.

Define Parameters: A sidebar box will appear. Choose the data range (A1:A101 in our example), check the Has Header box, and select Simple for a statistically random sample. Click Next .

Customize Output: specify the desired sample size (five). You can display the samples on a New worksheet , Add a column with an indicator , or Highlight them in place .

Generate Sample: Click Sample . The add-on will populate a new sheet with your random sample data.

Choosing the Right Method

• Formulas offer more control over the sampling process.
• The add-on provides a user-friendly, one-click approach for basic random sampling.

No matter which method you choose, Google Sheets empowers you to create a random sample for an unbiased look at your data.

Video Tutorial

Watch the video tutorial below about taking a simple random sample.