Excel random numbers – There are several ways to generate random numbers in Excel. This article explains how to use the RANDBETWEEN function and the RANDARRAY function.
How to Generate Random Numbers in Excel
There are several ways to generate random numbers. It is recommended that you use the simplest method for your purpose. The random numbers you generate may simply be data for basic analysis, so there may be no need for a complicated method.
There are two simple ways to generate random numbers using Excel functions.
Using an Excel Random Function (RANDBETWEEN Function)
You can use the RANDBETWEEN function to generate a random number between two specified numbers.
The syntax for Excel’s RANDBETWEEN random function is as follows.
=randbetween(minimum number,maximum number)
Basic Example
Let’s look at a basic use of the random number function.
First, enter the starting and ending numbers for the range you want.
The following explains how to generate a random 12-digit number within the range between BOTTOM and TOP.
Below is the result of creating a 12-digit number with RANDBETWEEN and then using the Libre Barcode 128 Text font to create a barcode.
When you use the basic formula below, the result is returned as a number. If the number is long, not all digits may be displayed.
=RANDBETWEEN($B$2,$C$2)
To display all digits, use the TEXT function in the formula.
=TEXT(RANDBETWEEN($B$2,$C$2),“##”)
Advanced Example (Generate Random Numbers Without Duplicates)
RANDBETWEEN is a common function for generating random numbers in Excel, but it has a major drawback: it can generate duplicate random numbers.
You can easily find various methods involving Excel functions and duplicate-checking settings to avoid generating duplicate random numbers in Excel.
However, you should first consider whether those formulas are necessary. Since the purpose of generating random numbers is to use them, we will focus on generating the numbers.
Below is the result of using an Excel random number function to generate random numbers between 10 and 20.
As you can easily see, duplicate numbers are returned.
The most recommended way to eliminate duplicate random numbers is to use Excel’s built-in Remove Duplicates feature.
On the Data tab, use the Remove Duplicates feature to quickly remove duplicate values.
Using an Excel Random Function (RANDARRAY Function)
Above, we looked at RANDBETWEEN, one of Excel’s random functions. Now let’s look at RANDARRAY, another Excel random function that can be used to extract random numbers without duplicates.
Definition and Syntax
RANDARRAY is a function newly introduced in Excel 365. You can specify the size of the array for random numbers using the ROWS function and the COLUMNS function.
The RANDARRAY function syntax is as follows.
=RANDARRAY([rows],[columns],[min],[max],[integer])
- rows: The number of rows in the array. If omitted, the default is 1.
- columns: The number of columns in the array. If omitted, the default is 1.
- min: The minimum value of the random number. If omitted, the default is 0.
- max: The maximum value of the random number. If omitted, the default is 1.
- integer: A logical value that specifies whether to return integers or decimal numbers. TRUE returns integers. FALSE or an omitted argument returns decimal numbers.
Example (Generate Random Numbers Without Duplicates)
By default, the RANDARRAY function can also generate duplicate values.
The formula used in column C is as follows.
=RANDARRAY(10,1,$C$3,$D$3,TRUE)
You can apply the UNIQUE function to this basic formula to generate random numbers without duplicates.
The formula is as follows.
=UNIQUE(RANDARRAY(10,1,$C$3,$D$3,TRUE))
Because the UNIQUE function excludes duplicate values, you may not get as many numbers as you want in the column.
Conclusion
There are many ways to remove duplicate values when generating random numbers in Excel. You can combine multiple functions, and there are also methods that use paid features. However, clearly define why you need to generate random numbers and choose the easiest and fastest approach.