generate a list of non repeating random numbers in excel

Generate Unique Random Numbers in Excel

By Emily Clark
AD

Creating a list of unique, random numbers in Excel is a common task when you need to generate a sequence of random values without duplication. Here's how you can do it using simple Excel functions.

Exelon Corporation uses Excel extensively for data analysis and reporting. Random number generation is a skill often employed to ensure data integrity and security. This guide will help you master the process of generating non-repeating random numbers in Excel.

Using the Remove Duplicates Feature

Firstly, let's consider a straightforward method using Excel's built-in Remove Duplicates feature, assuming you have a large pool of numbers from which you want to generate a smaller, unique subset.

AD
Excel - Generate Random Numbers, No Repeats (No Duplicates), Unique List
Excel - Generate Random Numbers, No Repeats (No Duplicates), Unique List
Generate Random Numbers: 2 Quick and Practical Ways in Excel
Generate Random Numbers: 2 Quick and Practical Ways in Excel
How to Create Random Numbers in Excel (including no repeats)
How to Create Random Numbers in Excel (including no repeats)

1. Generate a large pool of random numbers. Click on a cell (e.g., A1), then select 'Fill' in the 'Home' tab, choose 'Series', and fill the 'Step value' with '1' and the 'Stop value' with the desired number of elements. Then, select 'Random' under the 'Series' options and click 'OK'.

Remove Duplicates from the Pool

Next, select the entire column (or range) containing the numbers. Go to the 'Data' tab, and click on 'Remove Duplicates'. An alert box will appear, prompting you to confirm the removal. Click 'OK', and the duplicates will be removed.

an image of a computer screen with numbers in the bottom left corner and on the top right
an image of a computer screen with numbers in the bottom left corner and on the top right
Random Number Generator in Excel with No Repeats (9 Methods) - ExcelDemy
Random Number Generator in Excel with No Repeats (9 Methods) - ExcelDemy
a computer screen showing the keyboard and numbers in excel
a computer screen showing the keyboard and numbers in excel

Now, you have a list of unique, random numbers. However, this method does not generate truly random numbers. For more control over the randomness, we'll use a different approach.

Using the RandParams Function

The '=RAND' function generates random decimal numbers between 0 and 1, while the '=RANDBETWEEN' generates whole numbers between the specified minimum and maximum values. But, these functions don't guarantee uniqueness. To achieve this, we'll use the '=RANDBETWEEN' function inside an '=IF' function and wrap it in an '=ArrayFormula'.

AD

Preparing the Data Range

Assume you want to generate 50 unique, random numbers between 1 and 100. In cell A1, enter the range 'A1:A50'.

I Generate Random Numbers in Excel With These Simple Formulas
I Generate Random Numbers in Excel With These Simple Formulas
How to Generate Random Number from List in Excel (4 Ways) - ExcelDemy
How to Generate Random Number from List in Excel (4 Ways) - ExcelDemy
How to Create a 1 to 999 Number Generator in Excel – Step by Step Guide
How to Create a 1 to 999 Number Generator in Excel – Step by Step Guide
How to Use Random 5 Digit Number Generator in Excel (7 Methods) - ExcelDemy
How to Use Random 5 Digit Number Generator in Excel (7 Methods) - ExcelDemy
Excel Secrets: How to Generate Random Numbers Like a Pro
Excel Secrets: How to Generate Random Numbers Like a Pro
Random Number Generator Within Range in Excel (8 Examples) - ExcelDemy
Random Number Generator Within Range in Excel (8 Examples) - ExcelDemy

In cell B1, enter the following formula, and press Enter:

=ArrayFormula(IF(A1:A50="", "=RANDBETWEEN(1,100)",))

AD

After pressing Enter, you'll see a formula error. To fix it, click on the error cell, then click 'Array' in the 'Formulas' tab, and select 'Convert Formula to Array'. Now, the formula will work correctly.

Analysis of the investment project to download in Excel
Analysis of the investment project to download in Excel

Removing Blanks and Errors

You'll notice that there may be some blank cells. To fill them with random numbers, in a new cell (e.g., E1), enter:

=IF(B1="", RANDBETWEEN(1, 100), "")

AD

Then, drag the formula down to E50. Finally, select the range B1:E50, go to the 'Home' tab, click on 'Filter'. Scroll down to any blank cell (e.g., B51), uncheck 'Blanks' under 'Filter', and click 'OK'. The blank cells will be removed, leaving you with 50 unique, random numbers.

In conclusion, understanding the nuances of Excel's functionality enables you to generate unique, random numbers with ease, saving you time and enhancing your work accuracy. With these methods, you'll be well-equipped to tackle any dataset you encounter, further expanding your Excel skillset and benefiting Exelon Corporation. Happy Excel-ing!"

AD