Randomize Drop list items in a drop list means every time you use the drop list the contents are the same but appear in a different order. This functionality is extremely user for collecting accurate answers from users whether it’s for a client feedback questionnaire, a test or quiz for school or college, may be also a survey to avoid robotic selection of options and in many other real work situations.
There are different ways of creating Randomize Drop List Items:
Method 1: Using a RAND() function and 2 helper columns
We have a list of phone types in Column F. So will be creating a corresponding list of random numbers in a helper column in column G using a RAND() function.
The RAND() function is an argument-less function that returns a decimal number between 0 and 1
These numbers change every time the worksheet recalculates or by hitting F9.
Next, we will be creating a second helper column (in Column D) that sorts the list of fruits based upon the random numbers in column G.
We will be using a dynamic array function to do that to ensure that sorting is recalculated when entering, changing or refreshing values in the worksheet.
The function we will use is the SORTBY function, and here are the arguments:
=SORTBY(array,by_array,sort_order,…)
Like any dynamic array function, the SORTBY function:
Requires using Office 365 (Now called Microsoft 365)
Is created once in cell D1
It spills to the cell below (unless there are contents)
Does not require locking the ranges used
=SORTBY(F1:F7,G1:G7,1)
F1:F7 ► is the range to sort and return
G1:G7 ► Is the range to base the sort upon
1 ► for ascending
Every time we hit F9 the function recalculates and returns a different order.
We are now ready to create our drop list in Cell B2. Select it and on the Data tab click on Data Validation.
Note: Alternatively, we can use the shortcut ALT + D + L
Under “Allow” ► Select List. In the source box we want to refer to the spilled array range, so we click on cell D1 and type #, which is the spilled array symbol ► Hit OK
=$D$1#
and we have the randomizing drop list. You can hide the helper columns if you want.
Method 2: Using RANDARRAY function
The previous method required creating 2 helper columns beside the original column with a total of 3. This method will use only one column (when completed).
We have a list of cell phones in column F and we want to ask users about their preferred phone type in a survey.
We start by creating a SORTBY function in cell D1, but for the second argument, we use a nested RANDARRAY function.
The RANDARRAY function is the one generating the randomizing numbers for the SORTBY. It has the following arguments:
=RANDARRAY(rows,columns,min,max,integer)
Rows ► the number of items we have in column F (8)
Columns ► We have one single column of items
Min ► The lowest number returned (I will set it to 1)
Max► The highest number returned (Select any number >=8)
=RANDARRAY(8,1,1,20)
Let’s put them together in cell D1:
=SORTBY(F1:F8,RANDARRAY(8,1,1,20),1)
The last “1” is for ascending sort.
The next step is to hard code the list of items (Cell phones) inside our function. To do that, put the function in the edit mode (hit F2) ► select the range F1:F8 ► hit F9. We see an array of values in curly brackets.
=SORTBY({“IPhone”;”Alcatel”;”Samsung”;”Sony”;”Huawei”;”LG Escape”;”Nokia”;”Ericsson”},RANDARRAY(8,1,1,20),1)
Instead of undoing (CTRL+ Z) as we usually do, hit “ENTER” to hard code the value
The function works as previous but it’s not relying anymore on column F
So, delete the values in column F and just keep one column of values returned by the nested functions.
We now have a single column to be used for our data validation drop list in cell B2. Every time we hit F9 the function recalculates and returns a different order.
With B2 selected, go to the Data tab click on Data Validation.
Alternatively, we can use the shortcut ALT > D > L (sequentially) followed by TAB > L > TAB
Under “Allow” ► Select List. In the source box we want to refer to the spilled array range, so we click on cell D1 and type #, which is the spilled array symbol ► Hit OK
=$D$1#
and we have the randomizing drop list. You can hide the helper column if you want.
You can watch the video tutorial from my YouTube channel here below
