What Is Randomize List In Google Sheets?
Randomize List in Google Sheets helps users to insert a cell range or a column of random numeric values, to sort a list of any data randomly, that keeps updating whenever we calculate, refresh or open the worksheet.
We can generate random numeric values for the Google Sheets Randomize List, using the RAND() and RANDBETWEEN() functions that give shuffled, non-repeated, unique values within a default range 0 to 1, or within a defined range, respectively.
For example, select cell A1, enter the formula =RAND(), press “Enter”, and drag the formula using the fill handle till cell A10.
The output is shown above. i.e., 10 random numeric values within the default range 0 to 1 are generated upto default 10 to 11 decimal places. We can increase or decrease the decimal places as desired. Note that each value is unique and not repeated.
Key Takeaways
- The Randomize list in Google Sheets is a feature used to generate a range of random numeric values with a default range 0 to 1 using the RAND function or within a defined range using the RANDBETWEEN function, such as 0 to 100, 45000 to 98000, etc.
- It is an important feature because it helps avoiding biased data which may occur in an ordered list. For example, in scenarios like choosing lottery winners the results will be unbiased, random, and unpredictable.
- The data sharing or modifications will remain confidential, as we will see only the sorting of random numbers, but not the other columns with important details.
- If we do not want the RAND() and the RANDBETWEEN() function generated values to recalculate when the worksheet is opened, or refreshed, then we can copy the generated values with the shortcut keys Ctrl + C and as paste them as cell values and not as the formula results. Therefore, we will have a set of constant values.
- We can use the SORT and FILTER functions to sort the random values and also run a query using the QUERY function.
How To Randomize List In Google Sheets?
We can Randomize List in Google Sheets using the “Data” Menu or Tab, as follows:
Step 1: Select the cell range with values to randomize.
[Note: The cell values can be textual values, numeric or alpha-numeric, symbols, blank cells, etc. However, when the randomize list generates, then all the blank or empty cells are pushed to the end or bottom of the list.]
Step 2: Select the “Data” tab – click the “Randomize list” option, as shown below.
Examples
We will consider some Randomize list in Google Sheets examples using some functions such as, RAND(), SORT(), RANDARRAY(), FILTER(), QUERY(), etc.
Example #1 – Using RAND & Sort Formula
Consider the student details given in the dataset below, such as their names, id’s, etc. We will use the Google Sheets Randomize list feature using RAND & Sort Formula.
The steps to generate a randomized list using RAND and the SORT function are,
Step 1: Select cell D2, enter the formula =RAND(), press “Enter” and drag the formula from cells D2 to D16 using the fill handle, as shown below.
Step 2: Select cell E2, enter the formula =SORT(D2:D16) and press “Enter”, as shown below.
We see the output as above. Here, it generates the the random list using the RAND() in column D and sorts it using the SORT function in column E. However, we must remember that the RAND() function keeps on updating every time we refresh the dataset, execute the function or open the worksheet, due to which the sorted values update too. Hence, it is important to secure the values, if necessary.
Step 3: To secure the values we can copy-paste the generated values and then apply the sort function, that will help avoid the data updating, as shown below.
Example #2 – Using RANDARRAY & Sort Formula
Let us consider the Example #1 dataset once again, except that we will delete the SN and the ID columns and keep only the names. We will use the Google Sheets Randomize list feature using RANDARRAY & Sort Formula.
The steps to generate a randomized list using RANDARRAY and the SORT function are,
Step 1: Select cell B2, enter the formula =RANDARRAY(COUNTA(A2:A16)) and press “Enter”. The random numbers generate till cell from B2 to B16, as shown below.
Step 2: Select cell C2, enter the formula =SORT(A2:A16,B2:B16,False), as shown below.
Step 3: Press “Enter”, to get the following sorted names.
Example #3 – Combine FILTER, SORT and RANDARRAY
Consider the employee details given in the dataset below, such as their names, employee id’s, department names, their region, etc. We will Combine FILTER, SORT and RANDARRAY to Randomize list in Google Sheets.
The steps to generate a randomized list using FILTER, RANDARRAY and the SORT functions are,
Step 1: Select cell F2 and enter the formula =SORT(FILTER(D2:D11, E2:E11=”South”), RANDARRAY(COUNTA(FILTER(D2:D11, E2:E11=”South”))), TRUE), as shown below.
Step 2: Press “Enter”, to get the following retrieved “South” region employee names.
Example #4 – Using RAND & QUERY Formula
Let us consider the Example #3 dataset once again, except that we will delete the SN, department, region and the ID columns and keep only the names. We will use the Google Sheets Randomize list feature using RAND & QUERY Formula.
The steps to generate a randomized list using RAND and the QUERY function are,
Step 1: Select cell B2, enter the formula =RAND(), press “Enter” and drag the formula from cells B2 to B11 using the fill handle, as shown below.
Step 2: Select cell C2, enter the formula =SORT(A2:A16,B2:B16,False), as shown below.
Important Things To Note
- Both the RAND and RANDBETWEEN are volatile functions. It will slow down the workbook considerably. Both functions recalculate values when there is any change that occurs in the worksheets.
- When using the “Data” menu method, the cell values can be textual values, numeric or alpha-numeric, symbols, blank cells, etc. However, when the randomize list generates, then all the blank or empty cells are pushed to the end or bottom of the list.
- Using the RAND function, we can generate random numbers greater than 0 but less than 1. We can use the RANDBETWEEN if we want other values by giving a defined range.
- We must remember that when we apply a formula using the RAND() results as cell reference, that data will update as well whenever the RAND formula generated results get refreshed.
- We can secure the generated values, if we do not want them to keep on updating, by copy-pasting the generated values and overwriting it on the formula results.
Frequently Asked Questions (FAQs)
The FILTER function in Google Sheets is inserted as follows:
First, choose an empty cell – select the “Insert” tab click the “Function” option right-arrow – click the “Filter” option right-arrow – select the “FILTER” function, as shown below.
The same path can be used to insert the SORT function in Google Sheets, as we can see that the function lies just below the “FILTER” function, in the above image.
We find and insert the RAND function using the following way,
First, choose an empty cell – select the “Insert” tab – click the “Function” option right-arrow – click the “Math” option right-arrow – select the “RAND” function, as shown below.
The same path can be used to insert the RANDARRAY and the RANDARRAY functions in Google Sheets, as we can see that these functions lie just below the “RAND” function, in the above image.
The QUERY function is quite tricky to find as it is available only in Google Sheets and not found in MS Excel. Therefore, it is found and inserted using the following way,
First, choose an empty cell – select the “Insert” tab – click the “Function” option right-arrow – click the “Google” option right-arrow – select the “QUERY” function, as shown below
The alternate helper option of using the “Data” menu option is,
a. Select a cell range with existing cell values and right-click on it.
b. Scroll down and click the “View more cell action” right-arrow.
c. Select the “Randomize range” option, as shown below.
Remember, that this option appears only when we select a cell range with or without cell values. It does not appear when we just select a cell.
We can Randomize List in Google Sheets using the following formulas,
a. RAND() function returns random values within the default 0 to 1 range.
b. RANDBETWEEN() function returns random values within the given range.
c. RANDARRAY() function returns a grid of random values within the 0 to 1 range depending on the row and column value we provide.
Download Template
This article must help understand Randomize list in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Randomize List In Google Sheets. We learn how to randomize a list and sort them using functions like RAND with examples. You can learn more from the following articles. –
Leave a Reply