Souptik Roy
In this article, I will show you how to randomize a list in Excel into groups. While working on an Excel worksheet, you need to do a massive type of work. Sometimes you may need to rank any dataset randomly. In this article, I will show you how to randomize a list in Excel properly. I hope this article will increase your excel skill. However, follow the procedures step-by-step. I have added pictures for your better understanding.
Download Practice Workbook
Please download the workbook to practice yourself.
5 Effective Ways to Randomize a List in Excel Into Groups
You can see the dataset I have used in this article in the following picture. The dataset has two columns called B and C. The dataset has the values called Student Id, and Name of students of a certain school. I will use this dataset to show how to randomize a list in Excel into groups. Follow the steps of every method one by one.
1. Randomize a List in Excel Into Groups Using RAND Function
In this method, you will know the randomization method of a list using the RAND function. The process is simple. Follow the procedures one by one. Hopefully, you will find interest in this method. I have made a slight change in the dataset. I am considering only the name column and added a new column called Random Rank here. However, follow the steps carefully.
Steps:
- First, select the C5 cell and copy the following formula into it.
=RAND()
- After pressing Enter, you will find the following number.
- Then, Fill Handle the formula to copy down from C5 to C10.
- Then, go to the Formula tab in your toolbar.
- After that, select the Calculation option.
- Hence, select the manual option.
- Then, select the Home tab and go to the Editing option.
- Then, select the Sort and Filter option.
- Meanwhile, a pop-up window will appear. Select the Sort Largest to Smallest
- Then, like the following window, select the Expand the Selection option and select the Sort
- As a result, Excel will sort the Names like the following picture.
This is how the randomization of a list can happen.
Read More: How to Randomize List of Names in Excel (8 Easy Methods)
2. Implementing Excel RANDBETWEEN Function to Randomize a List
In this portion of this article, I will implement the RANDBETWEEN function to randomize a list in Excel. This is another short method. I hope this will increase your Excel skill. Follow the procedure step by step.
Steps:
- First, select the C5 cell.
- Then, copy down the following formula in the selected cell.
=RANDBETWEEN(1,6)
- After pressing Enter, Excel will show the following result.
- Then, copy down the formula to the C10
- Hence, you will get random numbers like the picture given below.
- Moreover, to stop the automatic changing of the numbers, go to the Formulas tab and select the Calculation Options.
- Then, select the Manual option.
- Then, go to the Home tab and select the Editing
- After that, select the Sort & Filter option.
- Hence, select the Sort Largest to Smallest option.
- Meanwhile, the following window will appear. Select the Expand the selection option and then press the Sort button.
- As a result, Excel will show the following sorted result.
This is how you can use the RANDBETWEEN function to randomize a list in Excel.
Read More: How to Randomize a List in Excel Without Duplicates (4 Ways)
3. Combine RANDARRAY, SORTBY, and ROWS Functions
In this part of this article, I will Combine RANDARRAY, SORTBY, and ROWS Functions. This method is not as easy as the former two methods. Follow the method step by step given below.
Steps:
- First, select the C5 cell.
- Then, copy the following formula in the selected cell.
=SORTBY(B5:B10,RANDARRAY(ROWS(B5:B10)))Here,
- ROWS(B5:B10): Returns the row numbers of the array.
- RANDARRAY(ROWS(B5:B10): Returns an Array according to the ROWS
- SORTBY(B5:B10, RANDARRAY(ROWS(B5:B10))): Returns the newly sorted names and spills the names throughout the whole column.
- After pressing Enter, Excel will show the following result.
Read More: How to Shuffle an Array Using Excel VBA (4 Examples)
4. Using INDEX and RANDBETWEEN Functions
In this part of this article, I will use INDEX and RANDBETWEEN functions to randomize a list in Excel into groups. Follow the following steps to randomize the list.
Steps:
- Select the C5 cell first.
- Then, write down the following formula in the selected cell.
=INDEX($B$5:$B$10,RANDBETWEEN(1,6),1)Here,
- RANDBETWEEN(1,6): Returns a random number in a cell between 1 to 6.
- INDEX($B$5:$B$10, RANDBETWEEN(1,6),1): Returns randomly sorted names in a certain cell.
- After pressing Enter, the following result will be shown.
- After copying down the formula, Excel will show the following result.
We can randomize a list in Excel using the INDEX and RANDBETWEEN Functions.
Read More: How to Shuffle Numbers in Excel (7 Easy Methods)
5. Applying CHOOSE and RANDBETWEEN Functions
In this method, I will apply CHOOSE and RANDBETWEEN functions to randomize a list in Excel into groups. Follow the following steps.
Steps:
- Select the C5 cell first.
- Write down the following formula in the selected cell.
=CHOOSE(RANDBETWEEN(1,6),$B$5,$B$6,$B$7,$B$8,$B$9,$B$10)Here,
- CHOOSE(RANDBETWEEN(1,6),$B$5,$B$6,$B$7,$B$8,$B$9,$B$10): Returns the shuffled names in the C column.
- After pressing Enter, the following result will be shown.
- Then, Copy down the formula from C5 to C10
- Hence, the following result will be found.
This is the procedure how to randomize a list in Excel into groups.
Read More: How to Shuffle Data in Excel (7 Simple Methods)
Things to Remember
- You should bear in your mind that the SORTBY function can only be found in Excel 365.
Conclusion
In this article, I have tried to explain how to randomize a list in Excel into groups. I hope, you have learned something new from this article. Now, extend your skill by following the steps of these methods. You will find such interesting blogs on our website ExcelDemy.com. I hope you have enjoyed the whole tutorial. If you have any queries, feel free to ask me in the comment section. Don’t forget to give us your feedback.
