How to Randomize List in Excel?
Randomizing a list in excel means selecting a random value from the data, to randomize a whole list in excel there are two excel formulas one is the =RAND() function which gives any of the random values to the cell and then we can sort the list or we can use the =RANDBETWEEN() function which gives random values to the cell from the number range a user provides.
#1 Insert Random Numbers in the List by Using RAND Function
I don’t know whether you are aware or not; we can insert random numbers in the list from 0 to less than 1 in excel by using the RAND function. Using the numbers generated by the RAND function, we can sort the list of numbers randomly.
RAND function does not have any kind of parameters to satisfy. We just need to enter the formula and close the bracket. RAND function automatically assumes the parameter is greater than 0 and less than 1.
In the above example, I have just entered formulas.
I have a list of some products, and I want to sort the data randomly for some confidentiality.
If I try to sort the data based on the Product name, it will sort either from A to Z or from Z to A. If I try to sort the data based on Gross Sales, it will sort either from Lowest to Highest or from Highest to Lowest.
But I want neither of them to happen; I Want to sort randomly.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
- Step 1: Apply RAND function in the immediate after the Gross Sales column. Name the new column as Helper.
- Step 2: Remember, RAND is a volatile function and keeps changing the values after every action in excel. So remove the formula by using Paste Special.
- Step 3: After paste values, select the entire data range and go to the DATA tab.
- Step 4: Under the data tab, select the SORT option.
- Step 5: Now, under Sort by select the newly inserted column, i.e., Helper
- Step 6: In the same window, under Order, select the option you want. Because we are sorting manually, we can select any one of the available options. I have selected the Smallest to Largest.
- Step 7: Click on the OK button. It will sort the data randomly.
#2 Pick a Lottery Winner with Randomize List in Excel
If you have seen the lottery industry, they will pick the winner randomly without any kind of bias. By using excel and by using the RAND function, we can also pick a winner.
I have a few names and their lottery ticket numbers; we need to pick a lottery winner from this lot.
The criteria to pick a winner is we need to generate random numbers from 1 to 100 against each name, and whoever gets the maximum score, they will be declared as the winner.
By using the RAND function, I will generate some numbers against each name. The problem is RAND can give me all the values which are less than 1, so I will multiply the number 100 with the numbers generated by the RAND function.
Since RAND is a volatile function, I will remove the formula and retain only values.
Now I have the score ready against each name. Criteria are whoever gets the maximum score will be declared as the Winner. I will sort the numbers from largest to smallest.
- Step 1: Select the data, and Under data, the tab selects the SORT option.
- Step 2: Now, under Sort by select the column SCORE.
- Step 3: Now comes the important part of the process. Under Order, we need to select the criteria as Largest to Smallest so that the largest value from the list comes first.
- Step 4: Click on the OK; it will sort the data from Largest to Smallest of the scoring column.
Wow!!! We have a winner here, and the winner is Ramela, who got the highest score when we have applied the RAND function.
Things to Remember
- RAND & RANDBETWEEN both are volatile functions and will slow down the workbook considerably.
- Both the functions recalculates values when there is any change that occurs in the worksheets.
- By using RAND, we can generate random numbers that are greater than 0 but less than 1.
- Do not keep volatile functions in any of the workbooks.
This has been a guide to the Randomize List in Excel. Here we discuss how to Insert a random number by using the Rand function and to Pick a Lottery Winner Randomly, along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –