Search
Close this search box.

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

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.

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

How to Randomize a List in Google Sheets

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

  1. Select the list of items
  2. 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.

random sort google sheets

Your list should now be randomly sorted.

google sheets random sort

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.

google sheets random sort

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.

randomize google sheets

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’.

Sort Sheet By

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

Sorting with the title

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.

Sorting without the header

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

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

How to Randomize a List in Google Sheets (Shuffle Data)

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

Sort Range

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

How to Randomize a List in Google Sheets (Shuffle Data)

Step 6: Click the Sort button.

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

Sample Data

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.

random sort google sheets

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.

Screenshot of example spreadsheet

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.

Copy the column with data using the keyboard shortcut CTRL + C and paste it in the adjuscent cell using CTRL + V.

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

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 3: Do the same for the remaining to 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.

Conclusion

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!

Related:

Most Popular Posts

Related Posts

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!