If you’re in business operations, you’ve likely encountered a situation where it was important to know how to randomize a list in Google Sheets.
For example, maybe you’re balancing lead distribution for a sales team. Knowing how to harness the power of randomness in your spreadsheet can ensure each rep gets their fair share of hot, warm, and cold leads.
This guide will provide a tutorial on fours different ways to randomize a list in Google Sheets.
Let’s dive in!
1. Randomize List Using Random Sort from Data Menu
The Randomize Range feature is the easiest method to randomize a list in Google Sheets. Let’s walk through how to apply it:
First, select the range of cells you want to randomize. This could be a list of names, numbers, etc.
Tip: Don’t accidentally highlight column headers in the first row of your spreadsheet. If you do, Google Sheets will shuffle them in, randomizing your headers along with the data.
Navigate to the top ribbon of your spreadsheet and open the Data dropdown.
Click ‘Randomize Range.’
Google Sheets will automatically rearrange your list into a new, random order.
2. Randomize List Using Random Sort from Helper Menu
Google Sheet’s Helper Menu offers another easy-to-access option for randomizing your list in just a few clicks.
To start, select the range of cells you want to randomize.
Right-click on your mouse to open the Google Sheets Helper Menu.
Scroll down the menu until you see ‘View more cell actions.’
Hover over it and select ‘Randomize Range’ from the options.
The selected ranges will now appear in a compeltely random order.
3. Randomize List Using Random Sort Order with the SORT and RANDARRAY Functions
Though not as straightforward as the Randomize Range feature, the SORT and RANDARRAY functions provide more power and flexiblity when randomizing a list.
Let’s walk through how to use them with our existing dataset.
Start by adding a new column in your Google Sheet. In this example, “Random Sort.”
Apply the rand function formula, in the example: =RANDARRAY(COUNTA(A:A))
This will generate a column of random numbers for each entry in column A.
Next, we’ll sort the list using the random numbers we just generated.
Enter the following formula in an open cell in column C: =SORT(A:A, B:B, FALSE)
Note: The ‘FALSE’ argument sorts your list in descending order. Use ‘TRUE’ if you want to sort your list in ascending order. The results will still be randomized.
Click enter. Your randomized list will now appear in column C.
4. Randomize List Using Random Sort Order with the QUERY Function
Google Sheets’ QUERY function allows you to perform SQL-like queries on your data. When combined with the RAND function, you can use it to randomize a list.
Let’s return to our original dataset to see how it works.
To begin, click into an empty cell next to your list. Type in the formula: =RAND()
Click enter to generate a random number for each entry in your list.
Now we can use the QUERY function to randomize the list.
Select an empty cell in column C.
Enter the following formula: =QUERY(A:B, “SELECT A ORDER BY B”, 0)
- ‘A:B’ is the range of your data
- “SELECT A ORDER BY B” represents your query. In this example we want Google Sheets to select the data from column A and order it by the random numbers in column B.
- ‘0’ indicates there are no header rows. Change it to 1 if your list has headers.
Click enter to apply the formula down column C. This will re-randomize the list in column B.
Randomize a List in Google Sheets: 4 Different Ways
And that’s it! Now you know how to randomize a list in Google Sheets.
But that’s only scratching the surface. Google Sheets is powerful on its own. Add-on’s like Coefficient make it unstoppable.
Get started for free today to learn how.
