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 completely 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.
Supercharge your spreadsheets with GPT-powered AI tools for building formulas, charts, pivots, SQL and more. Simple prompts for automatic generation.
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.
Use AI to Write Formulas to Randomize Your List
As you can see, there are quite a few alternatives for randomizing a list within Google Sheets. You may be a little overwhelmed, but you don’t have to go at it alone.
You can use Coefficient’s free GPT Copilot to automatically generate any spreadsheet formula, pivot, or chart, including your conditional formatting formulas. To use GPT Copilot, you need to install Coefficient. The install process takes less than a minute.
First, you’ll get started for free by simply submitted your email.
Follow along with the prompts to install. Once the installation is finished, return to Extensions on the Google Sheets menu. Coefficient will be available as an add-on.
Now launch the app. Coefficient will run on the sidebar of your Google Sheet. Select GPT Copilot on the Coefficient sidebar.
Then click Formula Builder.
Type a description of a formula into the text box. To recreate a formula to randomize the same list above. Let’s type: randomize the list of names in column a on rows 2 through 10 in sheet19.
Then press ‘Build’. Formula Builder will automatically generate the formula from the first example.
As you can see, AI provided us a more complex formula that completes the entire task in just a few seconds. Simply place your formula in the desired cell.
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.
