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

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

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

- Drag the
**Fill Handle**tool from**B5 to B15**to 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 Larges**t (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.

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

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

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

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

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

`=SORTBY(B5:B15,RANDARRAY(COUNTA(B5:B15)))`

- Then press
**Enter**.

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

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

`= 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**.

- Drag the
**Fill Handle**tool then.

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