How to Randomize List in Excel?
In our earlier articles, we have seen the types of sorting in excel right from text sort, number sort, icon sort, and color sort. But how do you sort the list randomly? Have you ever heard of sorting the data randomly? Sounds strange, isn’t it?
As a team leader you want to allocate shifts for your team members randomly, as a lottery company owner you may need to need to declare a winner by randomly, you may need to pick a random number to perform the cultural task in a picnic, like this there are situations we need to randomize the excel list.
Let us look at how we can randomize the list in excel.
#1 – Insert Random Number 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 RAND function. Using the numbers generated by 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 as 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 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.
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 data, tab select 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 select any one of the available options. I have selected Smallest to Largest.
Step 7: Click on 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 RAND function we can also pick a winner.
I have 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 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 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, 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 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 go the highest score when we have applied the RAND function.
Things to Remember about Randomize List in Excel
- RAND & RANDBETWEEN both are volatile function and will slow down the workbook considerably.
- Both the functions recalculates values when there is any change occurs in the worksheets.
- By using RAND we can generate random numbers which are greater than 0 but less than 1.
- Do not keep volatile function in any of the workbooks.
You can download this Randomize List Excel template here – Randomize List Excel Template
This has been a guide to Randomize List in Excel. Here we discuss how to Insert random number by using Rand function and to Pick a Lottery Winner Randomly along with practical examples and downloadable excel template. You may learn more about excel from the following articles –