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

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 pick participants for an event, or you might want to randomly group people or items together.

Google Sheets has recently introduced the new ‘Randomize range’ feature specifically to cater to this requirement. 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 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 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:

  • Select the list of items (cell range A2:A10 in our example).
  • Right-click on your selection 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 sorted in random order.

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

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

  • In a blank column next to your list, type the formula: =RAND() and press the return key.
google sheets random sort
  • Drag down or double click the fill handle of this cell to copy the formula to the rest of the cells in the column.
randomize google sheets
  • You should now see a list of randomly generated numbers between 0 and 1. Select any one of the cells in this column.
  • From the Data menu, select the option ‘Sort Sheet by Column B A→Z’.
  • 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.

While this method is quick and easy, 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 list items get sorted:

  • In a blank column next to your list, type the formula: =RAND() and press the return key.
  • Drag down or double click the fill handle of this cell to copy the formula to the rest of the cells in the column.
  • 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)
  • Right-click on your selection and click on ‘Sort Range’.
  • 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)
  • Click the Sort button.
  • Select the column containing the random numbers (column B in our example) and delete it.

Now only your selected cells will get randomized, instead of the whole sheet.

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 this topic here.

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. We hope we have been able to make this topic easy for you to understand and follow.

Nahid

Nahid

Nahid Akhter is a writer, programmer, and online course content creator. She has an MS in Computer Science and has been in the education line for more than 14 years. Her specialization is in programming and Tech-writing, and her areas of interest include Office productivity, Artificial Intelligence, and Web design and development.