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

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.

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.

• Click on the Data 

• Then, proceed to the Sort command.

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

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

Read More: How to Shuffle Numbers in Excel (7 Easy Methods)

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.

• Thus, the given function will generate some random number between 1 to 20.

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

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

Read More: How to Randomize a List in Excel Without Duplicates (4 Ways)

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

📌 Steps:

• Write the following formula in cell D5.

• Press Enter afterward.

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

Read More: How to Shuffle an Array Using Excel VBA (4 Examples)

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 list of names in Excel.

📌 Steps:

• Write the following formula in cell D5.

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

• Press Enter afterward.

• See the output given below.

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

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

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.

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

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.

• Then press Enter.

• Now see the output given below.

• Now Enter the following formula in cell E5.

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.

Read More: How to Shuffle Data in Excel (7 Simple Methods)

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. For your information, see About Power Query in Excel to learn more.

📌 Steps:

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

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

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

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

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

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

• Drag the Fill Handle tool then.

• Then type the following formula in cell D5. 

• 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

You can download and practice the dataset that we have used to prepare this article.

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.

Related Articles

• How to Do Factorials in Excel (6 Easy Methods)