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

Certainly, Microsoft Excel excels at arranging and manipulating data. Now, it is a well-known fact that Excel has numerous dedicated functions to randomize and generate numbers. Granted this, in this article, we’ll take a deep dive into how to randomize a list in Excel without duplicates. In addition, we’ll also learn to randomly select from a list, make selections based on criteria, and generate unique random numbers.

How to Randomize a List in Excel Without Duplicates: 4 Ways

To begin with, let’s assume the List of Stock Prices dataset shown in the B4:C13 cells contains the “Company” names and the “Stock Prices” in USD. Here, we want to return a random “Company” name from the list using various functions in Excel. Henceforth, let’s see each method on how to randomize a list in Excel without creating duplicates, with the appropriate illustrations.

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

1. Using INDEX, SORTBY, and SEQUENCE Functions (Applicable for Latest Excel Versions)

First of all, let’s start by combining the INDEX, SORTBY, RANDARRAY, ROWS, and SEQUENCE functions, which collectively select 3 random “Companies” from the list shown in the B4:B13 cells.

📌 Steps:

• Initially, move to the B16 cell >> enter the expression given below.

Here, the B5:B13 array refers to the “Company” names.

📃 Note: This formula works for the Excel 365 and Excel 2021 versions, in case you’re using an older version of Excel, you can apply the next method.

Boom! That’s how easy it is! This returns a randomized list in Excel without any duplicates.

Read More: List of Names for Practice in Excel

2. Utilizing INDEX, RANK.EQ, and COUNTIF Functions (Compatible with Older Version)

Alternatively, we can combine the RAND, INDEX, RANK.EQ, and COUNTIF functions to obtain a randomized list of the “Company” names. So just follow along.

📌 Steps:

• First, go to the C5 cell >> use the RAND function to generate a random value >> use the Fill Handle tool to copy the formula into the cells below.

• Second, select the C5:C13 cells >> press CTRL + C keys to copy the values.

• Third, choose the C5:C13 cells >> click the Paste drop-down >> select the Paste Values option.

• Finally, navigate to the B16 cell >> type in the following expression.

In this case, the B5:B13 and C5:C13 ranges represent the “Company” and the “Random Values” respectively while the C5 cell points to the first “Random Value”.

📃 Note: Please make sure to use Absolute Cell Reference by pressing the F4 key on your keyboard.

3. Employing RAND, INDEX, and RANK.EQ Functions

Conversely, a simpler way involves using the RAND, INDEX, and RANK.EQ functions to retrieve 3 of the “Company” names at random.

📌 Steps:

• To start with, follow the steps shown previously or the GIF to copy and paste the values in the “Random Value” column.

• Afterward, enter the B16 cell >> insert the expression into the Formula Bar.

For instance, the B5:B13 and C5:C13 range of cells indicate the “Company” and the “Random Values” and the C5 cell represents the initial “Random Value”.

4. Applying UNIQUE, RANDARRAY, INDEX, and RANK.EQ Functions

Last but not least, we also apply the UNIQUE, RANDARRAY, INDEX, RANK.EQ functions to fetch a list of random values in Excel. So, let’s see it in action.

📌 Steps:

• First of all, click the C5 cell >> insert the following formula.

In the above formula, 9 is the row number, 1 is the column number, 1 is the minimum number, and 9 is the maximum number. Next, the UNIQUE function ensures that the RANDARRAY function returns an array of unique numbers.

📃 Note: To stop the C5:C13 array from changing, copy and paste only the values or follow the steps shown in the prior method.

• Following this, apply the following equation to the B16 cell.

In this scenario, the B5:B13 and C5:C13 range point to the “Company” and the “Random Values”.

Subsequently, this concludes the process of how to a randomize list in Excel without duplicates.

Read More: Excel Practice Exercises PDF with Answers

How to Randomly Select from a List with No Duplicates in Excel

For one thing, we can choose entire rows at random from a list, on this occasion, we can obtain the “Company” names and their corresponding “Stock Prices” using the SORTBY, RANDARRAY, INDEX, SEQUENCE, and ROWS functions.

📌 Steps:

• At the very beginning, click to enter the B16 cell >> copy and paste the equation given below.

How to Randomly Select with Criteria in Excel

Moreover, Excel enables us to randomly select values based on set criteria. For instance, we’ll use the INDEX, LARGE, IF, ROW, INT, RAND, and COUNTIF functions to calculate the “Company” with a “Stock Price” worth more than “$150 USD”.

📌 Steps:

• In the first place, enter the C16 cell >> insert the expression given below.

📃 Note: In the Microsoft Excel 365 version, you can run the array formula by pressing the ENTER key. But, in the older versions of Excel, you must press CTRL + SHIFT + ENTER to use the array formula.

Admittedly, some of the Methods to randomly select with criteria have been skipped, which you may explore if you wish.

How to Make Unique Random Number Generator in Excel

Finally, we’ll discuss how to make a unique random number generator in Excel by utilizing the SORTBY, SEQUENCE, and RANDARRAY functions.

📌 Steps:

• First and foremost, insert the formula into the B5 cell as shown in the image below.

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

Download Practice Workbook

Conclusion

In short, this tutorial explores all the ins and outs of how to randomize a list in Excel without duplicates. Now, we hope all the methods mentioned above will prompt you to apply them in your Excel spreadsheets more effectively. Furthermore, if you have any questions or feedback, please let me know in the comment section.

<< Go Back to Randomize in Excel | Learn Excel