How to Randomize List of Names in Excel (8 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Data sorting is frequently a crucial step in any data study. It can help in data organization, detect patterns, or discover the minimum and maximum values. However, there may be times when you need to undo any prior ordering and randomize your data! If you need to randomly pick a sample from your data, randomizing the order of a list can be helpful. So, in this article, we will talk about how to randomize a list of names in Excel.

Let’s assume we have a dataset, namely “Starting Eleven of Argentina in 2022 WC”. You can use any dataset suitable for you.

How to Randomize List of Names in Excel

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.


1. Utilizing Sort Feature

There are a bunch of ways to manipulate your data. However, Sort is a useful feature among them. To learn more about this, follow the steps that we are going to describe below.

📌 Steps:

  • First, select the entire dataset for which you want to do your task.
  • Here we copied C5:C15 data in D5:D15. In the next step, we will randomize the copied data in the same D5:D15 cell.

Utilizing Sort Feature

  • Click on the Data tab.

Utilizing Sort Feature

  • Then, proceed to the Sort command.

Utilizing Sort Feature

  • Thus, a prompt will pop up where we select Continue with the current selection.
  • Click on the Sort button afterward.

Utilizing Sort Feature

  • In the Sort by option, select the name of the column if it does not appear automatically. In our case, our intended data was in Column D.
  • Then select Cell Values and A to Z as described below.
  • Press OK.

  • Consequently, our output will look like given below.


2. Using RANDBETWEEN Function and Sort Feature

In this method, we first assign some random numbers generated using the RANDBETWEEN function and then sort the data based on the corresponding numbers.

📌 Steps:

  • To begin with this method, enter the following formula in cell B5.
=RANDBETWEEN(1,20)
  • Thus, the given function will generate some random number between 1 to 20.

randomize list of names in excel Using RANDBETWEEN Function and Sort Feature

  • Drag the Fill Handle tool from B5 to B15 to get the rest of the value.

randomize list of names in excel Using RANDBETWEEN Function and Sort Feature

  • Select the range of your data (B5:C15 in our case)

  • Then click the Home tab > Editing group > Sort & Filter button > Sort Smallest to Largest (any of the two options will work).

  • Now see the output given below.


3. Using SORTBY Function

Using the SORTBY function is another way to accomplish our task appropriately. In this method, we will incorporate the SORTBY and RANDARRAY functions to randomize our data. The main objective of incorporating the RANDARRAY function is to assign a random value to every name so that we can sort the data by calling the unique numbers, as we did in the previous method.

📌 Steps:

  • Write the following formula in cell D5.
=SORTBY(C5:C15,RANDARRAY(COUNTA(C5:C15)))

Formula Explanation:

In the above formula, the COUNTA function returns the number of cells from the C5:C15 cell range containing any text values. Later, the RANARRAY function generates an array of random numbers, with the row numbers determined by the output of COUNTA(C5:C15). Finally, the SORTBY function performs sorting based on the output of the RANDARRAY function.

  • Press Enter afterward.

randomize list of names in excel Using SORTBY Function.

  • Not a hard task as you already understand. See the output given below.

randomize list of names in excel Using SORTBY Function.


4. Applying the Combination of SORTBY and RANDARRAY Functions

You can add the ROWS function within the RANDARRAY function instead of using COUNTA as we did before to randomize the list of names in Excel.

📌 Steps:

  • Write the following formula in cell D5.
=SORTBY(C5:C15,RANDARRAY(ROWS(C5:C15)))

Here, the ROWS(C5:C15) syntax returns the number of rows.

  • Press Enter afterward.

randomize list of names in excel Applying the Combination of SORTBY and RANDARRAY Functions

  • See the output given below.

randomize list of names in excel Applying the Combination of SORTBY and RANDARRAY Functions


5. Applying CHOOSE and RANDBETWEEN Functions

Incorporating the CHOOSE and RANDBETWEEN functions in your formula editor is another option if you don’t want to assign some random number to your data. As you have already learned in Method 2. So, let’s stick to the method we are going to describe below.

📌 Steps:

In this method, we will assign the numbers as well as randomize our data with a single function.

  • Write the following formula in cell D5.
=CHOOSE(RANDBETWEEN(5,12),$C$3,$C$4,$C$5,$C$6,$C$8,$C$7,$C$9,$C$10,$C$11,$C$12,$C$13,$C$14)

Formula Explanation:

  • RANDBETWEEN(5,12) returns a random value starting from 5 to 12.
  • Then the values followed by RANDBETWEEN assign the number to the individual Player Name using absolute cell reference.
  • The CHOOSE function returns the value from the input data to the created number.

randomize list of names in excel Applying CHOOSE and RANDBETWEEN Functions

  • To get the other values, drag the Fill Handle tool from D5 to D15.


6. Using INDEX and RANDBETWEEN Functions

If the previous method seemed tiresome to you, then this method might help. In this case, we will use the INDEX and RANDBETWEEN functions simultaneously.

📌 Steps:

  • Enter the following formula in cell D5.
=INDEX($C$5:$C$15,RANDBETWEEN(1,11),1)

The RANDBETWEEN function randomly returns a number from 1 to 11. In our first case, 1 is the initial number. This unique value will be used as an index number and will thus be referred to as the corresponding cell value in the respective cell.

randomize list of names in excel Using INDEX and RANDBETWEEN Functions.

  • Drag the Fill Handle tool from D5 to D15. So here is our output given below.

randomize list of names in excel Using INDEX and RANDBETWEEN Functions.


7. Employing VLOOKUP and RANDBETWEEN Functions

Using the RANDBETWEEN and VLOOKUP functions of Excel, a user can shuffle a list. So, the method we are now describing will help all of you randomize the data.

📌 Steps:

  • First of all, we need to generate a number to attribute our “Player Name”, thus type the following formula in cell D5.
=SORTBY(B5:B15,RANDARRAY(COUNTA(B5:B15)))
  • Then press Enter.

randomize list of names in excel Employing VLOOKUP and RANDBETWEEN Functions

  • Now see the output given below.

  • Now Enter the following formula in cell E5.
=VLOOKUP(D5, $B$5:$C$15, 2, FALSE)

In this formula, D5 is the lookup value, B5:C15 is the table array, 2 is the column index number as the lookup value is in the 2nd column from Column B, and finally FALSE is for exact matching.

Note: Here, we have used the dollar sign ($) to fix the cell range while using the Fill Handle tool for copying the formula to the rest cells.

  • Drag the Fill Handle tool to get the other value.


8. Incorporating the Power Query Feature

With the Power Query, you may randomize your data accordingly. Though you need to go through some extra steps to accomplish this, once you learn the process, there will be no burden that we can tell you.

📌 Steps:

  • Select any cell inside your data.
  • Go to the Data tab > From Table/Range command.

randomize list of names in excel Incorporating the Power Query Feature

  • Thus, a prompt will appear and ask you to create a table. Select your data area, $B$4:$C15$ for instance.
  • Press the OK.

randomize list of names in excel Incorporating the Power Query Feature

  • Now, double-click on the region denoted by 1 and name the sheet where you want your output to appear.

randomize list of names in excel Incorporating the Power Query Feature

  • Click on Add Column > Custom Column command.

  • Add a name such as Random Number in the New column name box.
  • Now, type the following formula into the Custom Column Formula editor.
= List.Random(1)

Thus, each row gets a list as a result. You must extract the values from each list’s single random number, which ranges from 0 to 1, in order to utilize them to sort the data.

  • Click on OK.

  • Click on the Filter toggle in the Random Number column which contains all the List items.
  • Then select the Extract values option.

  • Select None > OK button.

  • Immediately, some random number will be generated as below.

  • Now, click on the Filter toggle in the Random Number
  • Choose either the Sort Ascending or Sort Descending However, we choose Sort Descending option.
  • Press OK afterward.

  • The Random Number column is no longer required because the data has already been sorted based on it, so you can delete it.

  • To delete those numbers, right-click on the Random Number column heading containing the random numbers.
  • Select the Remove option from the menu.

  • Now go to the Home tab and select the Close & Load option.

  • See the output given below now.


How to Randomize a List into Groups in Excel

Now we are going to articulate how to randomize a list into some groups using Excel. So, stick to us one last time.

📌 Steps:

  • To do this, first, we assign some random values in column C by using the RAND function.

randomize list of names in excel How to Randomize a List into Groups in Excel

  • Drag the Fill Handle tool then.

randomize list of names in excel How to Randomize a List into Groups in Excel

  • Then type the following formula in cell D5. 
=ROUNDUP(RANK(C5, C5:C15)/$C$17,0)

  • Drag the Fill Handle tool to get the other value.


Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it yourself.


Download Practice Workbook


Conclusion

Have you ever tried any of the methods discussed to randomize list of names in Excel? Could you tell us how it went? Feel free to share your thoughts in the comments section below.


<< 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.
Mohammad Shah Miran
Mohammad Shah Miran

Mohammad Shah Miran has a professional background spanning over a year at Softeko. Initially starting as an Excel and VBA writer, he authored more than 50 articles for the ExcelDemy project. Currently, Miran is engaged in the LinuxSimply project as a Linux content developer, completed over 40 articles. His analytical approach extends across various domains, including Excel, VBA, Bash scripting, Linux, data analysis, and Python programming. In his leisure time, Miran enjoys watching movies and series or listening... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo