How to Randomize a List in Excel Into Groups (5 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

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 skills. However, follow the procedures step-by-step. I have added pictures for your better understanding.


How to Randomize a List in Excel Into Groups: 5 Suitable Ways

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.

dataset of how to randomize a list in excel into groups


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.

Randomize a List in Excel Into Groups Using RAND Function

Steps:

  • First, select the C5 cell and copy the following formula into it.
=RAND()

Randomize a List in Excel Into Groups Using RAND Function

  • After pressing Enter, you will find the following number.

Randomize a List in Excel Into Groups Using RAND Function

  • Then, Fill Handle the formula to copy down from C5 to C10.

Randomize a List in Excel Into Groups Using RAND Function

  • Then, go to the Formula tab in your toolbar.
  • After that, select the Calculation option.
  • Hence, select the manual option.

Randomize a List in Excel Into Groups Using RAND Function

  • Then, select the Home tab and go to the Editing option.

Randomize a List in Excel Into Groups Using RAND Function

  • Then, select the Sort and Filter option.

Randomize a List in Excel Into Groups Using RAND Function

  • Meanwhile, a pop-up window will appear. Select the Sort Largest to Smallest option.

Randomize a List in Excel Into Groups Using RAND Function

  • Then, like the following window, select the Expand the Selection option and select the Sort button.

Randomize a List in Excel Into Groups Using RAND Function

  • As a result, Excel will sort the Names like the following picture.

Randomize a List in Excel Into Groups Using RAND Function

This is how the randomization of a list can happen.

Read More: List of Names for Practice in Excel


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 skills. Follow the procedure step by step.

Steps:

  • First, select the C5 cell.

Implement RANDBETWEEN Function to Randomize a List in Excel Into Groups

  • Then, copy down the following formula in the selected cell.
=RANDBETWEEN(1,6)

Implement RANDBETWEEN Function to Randomize a List in Excel Into Groups

  • After pressing Enter, Excel will show the following result.

Implement RANDBETWEEN Function to Randomize a List in Excel Into Groups

  • Then, copy down the formula to the C10 cell.
  • Hence, you will get random numbers like the picture given below.

Implement RANDBETWEEN Function to Randomize a List in Excel Into Groups

  • Moreover, to stop the automatic changing of the numbers, go to the Formulas tab and select the Calculation Options.
  • Then, select the Manual option.

Implement RANDBETWEEN Function to Randomize a List in Excel Into Groups

  • Then, go to the Home tab and select the Editing option.

Implement RANDBETWEEN Function to Randomize a List in Excel Into Groups

  • After that, select the Sort & Filter option.

Implement RANDBETWEEN Function to Randomize a List in Excel Into Groups

  • Hence, select the Sort Largest to Smallest option.

Implement RANDBETWEEN Function to Randomize a List in Excel Into Groups

  • Meanwhile, the following window will appear. Select the Expand the selection option and then press the Sort button.

Implement RANDBETWEEN Function to Randomize a List in Excel Into Groups

  • As a result, Excel will show the following sorted result.

Implement RANDBETWEEN Function to Randomize a List in Excel Into Groups

This is how you can use the RANDBETWEEN function to randomize a list in Excel.


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.


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.


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 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: Excel Practice Exercises PDF with Answers


Things to Remember

  • You should bear in mind that the SORTBY function can only be found in Excel 365.

Download Practice Workbook


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. 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.


<< Go Back to Randomize in Excel Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Souptik Roy
Souptik Roy

Souptik Roy, a BSc graduate in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, dedicated 1.5 years to the ExcelDemy project. During this time, he authored 50+ articles and reviewed 20+ for ExcelDemy. Presently, he is a designer and content developer at YouHaveGotThisMath and Brainor, sister concerns of ExcelDemy. His educational content spans science, mathematics, and grammar. Roy's interests include developing creative ideas, visualizing concepts with tools like Adobe Illustrator, and problem-solving within Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo