The importance and use cases of randomized data is increasing as the information technologies grows. You can lower the margin of error in analyzing methods and testing algorithms or eliminate the bias between subjects in the datasets. In this article will show you How to generate random data in Excel.
Excel has multiple functions to generate random numbers. Although, generating numbers does not necessarily mean generating random data as the data can include names, categories, dates, locations, or product numbers.
Number generating functions
There are multiple functions to generate random numbers in Excel:
- RAND
- RANDBETWEEN
- RANDARRAY (Excel 365, Excel 2021)
The RAND function returns a random number between 0 and 1. If your sole purpose is to return a single decimal value just type =RAND() and press the Enter button. No argument is needed.
Its sister function, the RANDBETWEEN, on the other hand, can return a random integer number between given limits.
The newest of all, the RANDARRAY, is a successor for both. You can use it to generate either decimal or integer numbers in given limits. But the most important feature of the new function is to generate multiple numbers at once. These advantages make this function a powerhouse. Unfortunately, you should be an Excel 365 subscriber or have Excel 2021 to use this function.
Generating random numbers
If you need uniqueness between generated values, choose either the RAND or the RANDARRAY function with integer = FALSE argument. Decimal numbers between 0 and 1 are essential for statistics.
Use the RANDBETWEEN if all you need is integer values between specified limits. The RANDARRAY is a no-brainer if you have the access.
Generating random date and time
This information may surprise you: According to Excel, date and time values are no more than formatted numbers. Excel assumes that the date 1/1/1900 is 1 and increases this number by 1 for each day. For example, 12/31/2018 is equal to 43465. Along with this, the time values are decimal numbers between 0 and 1. Excel displays 0.5 as 12:00:00 PM to you.
This functionality makes generating random date and time as easy as the previous topic. All you need to do is to change the number formatting.
Random date between 1/1/2022 and 12/31/2022
=RANDBETWEEN("1/1/2022","12/31/2022")
Because the date values are integers, you need to put limits to generate a random date.
Random time between 12:30:00 and 15:00:00
=RANDBETWEEN("1/1/2022","12/31/2022")
You do not need to define limits to generate a random time value. Either RAND or RANDARRAY function returns a number between 0 and 1 by default.
Generating random date/time data samples
You can enter the date and time limits by static numbers, string values or with helper functions like DATE and TIME functions.
Date
=RANDBETWEEN("1/1/2022","12/31/2022")
=RANDBETWEEN(DATE(2021,1,1),DATE(2021,12,31))
Time
=RAND()*("15:00:00"-"12:30:00")+"12:30:00"
=RAND()*(TIME(15,0,0)-TIME(12,30,0))+TIME(12,30,0)
Generating random data
As you may have guessed, you cannot easily generate random data in Excel like names, addresses, product names, etc. However, you can fetch your sample data randomly from a master database. Once again Excel's lookup and random generator functions can do the job when you set a master data which includes all the data fields you need. You can create your own, derive from an existing or historical data, or download from plenty of websites doing this. Here a few examples:
You have plenty of function options to generate your random data from the master. There are VLOOKUP, INDEX or the better INDEX & MATCH combinations as well as the newest of all the XLOOKUP.
The XLOOKUP is the clear winner among them. But it requires a Microsoft 365 subscription too. If you don't have Microsoft 365, you can still get what you need with good old VLOOKUP and INDEX functions.
The trick is to use a random number generator function to generate a random number for the position of the parsed value.
XLOOKUP
INDEX & MATCH
VLOOKUP
