How to Generate Random Data in Excel (9 Easy Methods)

In Microsoft Excel, we may need to generate random data when working with different scenarios and performing various analyses. In this article, I am going to explain how to generate random data in excel with some quick methods.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

9 Suitable Methods to Generate Random Data in Excel

Excel has some built-in methods for generating random data. The following methods are the easiest ways to generate random data.

1. Apply RANDBETWEEN Function to Generate Random Data in Excel

With the help of the RANDBETWEEN function, you can generate some random numbers within an upper and lower number.

Step 1:

• Select Cells for random data. I have selected cell (B4) to cell (B12).
• Apply the formula-

Where,

• The RANDBETWEEN function returns a random integer number between the specific numbers.

• Press CTRL+Enter.
• Here we got our random integer numbers between 1 to 100.

Sometimes duplicate values will appear. If you don’t have problems with duplicates then check the following steps.

Step 2:

• Select cells for random numbers.
• Apply the formula-

Where,

• The RANDBETWEEN function returns integers between the given numbers.

• Press CTRL+Enter.

You will find some duplicate values within the given range.

Read More: Excel Formula to Generate Random Number (5 examples)

2. Use RAND Function to Generate Random Data in Excel

With the RAND function generating random data is quite easy. Decimal numbers can be generated with the RAND function. Check this link too.

2.1 Generate Data Between 0 and 1

The main advantage of using the RAND function is it has no optional arguments. Following I am going to describe how to generate data between 0 and 1.

Steps:

• Select cells for the desired output.
• Apply the Formula-

Where,

• The RAND function will return a random number between 0 and 1.
• Press CTRL+Enter.

• Thus you will get random numbers between 0 and 1. It’s that easy.

2.2 Generate Data Between Any Two Numbers

Now, Let’s generate random data between any two numbers.

Steps:

• Select cells.
• Apply the formula-

Where,

• The RAND function returns random numbers within the given range.

• Click CTRL+Enter.

• This way you will get your desired random data between 1 to 10.

2.3 Produce Integer Data Between Any Two Numbers

As the RAND function produces decimal data automatically. Sometimes it becomes difficult to calculate. For this, I will show you how you can produce integer data between any two numbers with the RAND function.

Step 1:

• Select cells from B4 to B13.
• Apply the formula-

Where,

• The RAND function returns a random number within the range.
• The ROUND function rounds a number.
• Press CTRL+Enter to get the round values.

As you can see, we got our random rounded numbers within the range.

This same method can be applied to negative numbers too. Suppose you need to generate random integer data between -5 to 10.

Step 2:

• Select cells to generate random numbers.
• Apply the formula-

Where,

• The ROUND function rounds up to the nearest integer.
• The RAND function produces a random number between the upper and lower value.

• Press CTRL+Enter to get the result in all cells.

• Thus you can get your expected result with negative integers.

Read More: Generate Random Number between Two Numbers in Excel (4 Ways)

3. Combine RAND and RANK Functions to Get Unique Random Data

Generally applying the RAND function we will not get duplicate values. But to be on the safe side, we may need to check for duplicates. With the combination of the RAND and RANK functions, you will get your unique random data. Check out the following.

Step 1:

• Select cells to get random data.
• Apply the formula-

Where,

• The RAND function returns a random number between 0 and 1.

• Press CTRL+Enter.

Step 2:

• Select the data which we got by applying the RAND function.
• Press: CTRL+C to copy.

• Click the right button of the mouse and Paste as values.

• Here, we converted our data into values. So that it won’t change.

Step 3:

• Select a new column to get unique data.
• Apply the formula-

Where,

• The RANK.EQ function returns the rank of a number against a list of other numeric values.

• Click CTRL+Enter to get unique data from the previous column.

Thus we will get our unique random values by using the RANK function.

Read More: How to Generate Random Data in Excel (9 Easy Methods)

4. Perform RANDARRAY Function to Generate Random Data in Excel

If you want to create a full set of random data in excel then the RANDARRAY Function is the perfect solution for you. But It will work if you have Excel 365 or Excel 2021.

Steps:

• Select some rows and columns to get a full set of random data.
• Apply the following formula-

Where,

• The RANDARRAY function returns an array of random numbers between 0 and 1.

• Click CTRL+Enter.

This way you will get your random data quickly in Excel 365.

5. Combine VLOOKUP and RANDBETWEEN Functions to Generate Random Data in Excel

In the previous methods, we have generated random numbers. In excel you can get randomized text also. With the combination of the VLOOKUP function and the RANDBETWEEN function, I am sharing with you a quick approach to getting random text in excel.

Suppose we have a dataset of a fruit shop containing Product ID and Product name.

Step 1:

• Select the dataset and click the right button on the mouse.
• From the options box, select “Define Name”.

Step 2:

• A new window will appear named “New Name”.
• In the “Name” section type Fruits.
• Press OK.

Step 3:

• Select a column where random fruit names will be shown.
• Apply the formula-

Where,

• The RANDBETWEEN function distributes data within the given upper and lower value.
• The VLOOKUP function searches for a certain value and returns the value from a different column in the same row.

• Press CTRL+Enter.
• Now, drag down the Fill handle to get random fruit names in the column.

This is the quickest way to generate random text in excel.

Similar Readings

• Random 5 Digit Number Generator in Excel (7 Examples)
• Random 4 Digit Number Generator in Excel (8 Examples)
• Generate Random Number from List in Excel (4 Ways)
• Random Number Generator between Range in Excel (8 Examples)
• Random Number Generator in Excel with No Repeats (9 Methods)

6. Apply the Data Analysis Toolpak to Generate Random Data in Excel

We have used different functions to get random data in excel. But excel has a built-in toolpak to generate random data. In the following, I am going to explain generating random data by applying a “Data Analysis” toolpak.

Step 1:

• Choose the “Data” ribbon and go to “Data Analysis”.

• A new window will appear-” Data Analysis”.
• Select “Random Number Generation” from the analysis tools.
• Press OK.

Step 2:

• From the new window “Random Number Generation” input “Number of Variables” and “Number of Random Numbers” as per your demand.
• Number of Variables indicates the number of columns that you want to add in.
• Number of Random Numbers means the number of data in each column.
• In the drop-down list select “Uniform”.
• Choose parameters. Here I have chosen between 1 and 20.
• Click on “Output Range” and select a cell from the workbook.
• Press OK.

• As you can see, without using any type of function we got our random data with the “Data Analysis” toolpak.

Read More: Random Number Generator with Data Analysis Tool and Functions in Excel

7. Run a VBA Code to Generate Random Data in Excel

VBA stands for Visual Basic for Applications. In this method, I am going to run a VBA code to generate random data.

Step 1:

• From the keyboard Press: ALT+F11 to open up the VBA Editor.
• Go to Insert > Module.

• In the Module window, write down the code-

Sub Randomdata()
     MsgBox Rnd()
End Sub

• Now run the code.

• You will get a random number generated in the excel sheet.

The random number that we got from the macro code is a decimal value. In many cases, we may need round values generated. For that follow the following steps.

Step 2:

• Select “Module” from the “Insert” ribbon.
• Apply the following code in the module window.

Sub Randomdata()
 MsgBox Round((Rnd() * 10) + 5)
 End Sub

• Click Run to run the code.
• Here you will see we have successfully generated a round number between 5 to 15.

Think that you want to display your random data in the grid.

Step 3:

• In the Module window apply the code-

Sub RandomNumber()
 Dim I As Integer For I = 1 To 5
   Cells(I, 1) = Round((Rnd(10) * 10) + 5, 0)
 Next I
 End Sub

• Run the macro.
• Thus we will get our desired random data with VBA code simply.

Read More: How to Generate Random Number with Excel VBA (4 Examples)

8. Merge RANK.EQ and COUNTIF Functions to Generate Random Data without Duplicates

You can use the RANK.EQ and COUNTIF function to generate random data without duplicates if you don’t have access to the RANDARRAY function in excel.

Step 1:

• Select some cells from a column. Here I have selected cells (B4) to cells (B13).
• Apply the formula-

Where,

• The RANDBETWEEN function calculates a random number between two numbers.

• Press CTRL+Enter to generate random data between 1 to 20.

Here we got our random data within the given limit. But if you check you will find some duplicate numbers too in the list. To get data without these duplicates first we have to convert these data into values and then apply some functions.

Step 2:

• Select those data in the cells.
• Press: CTRL+C to copy.
• Click the right button of the mouse and from the paste option select values.

• So, we got all our data converted into values.

• Now it’s time to get the unique values only.

Step 3:

• Select a new column where new unique data without duplicates will be shown.
• Apply the formula-

Where,

• The EQ function calculates and returns the statistical rank of a given value.
• The COUNTIF function counts the number of cells in a given range.

• Press CTRL+Enter.

Here you will see we generated random data without duplicates.

Read More: How to Generate Random Numbers Without Duplicates in Excel (7 Ways)

9. Create a Dynamic Array to Generate Random Data in Excel

In order to avoid repeating values, you can use dynamic arrays in excel. This is supported only for Microsoft 365.

Steps:

• Select cells (B4) to cells (B13).
• Apply the formula-

Where,

• The INDEX function returns the value at a given location in an array.
• The SORTBY function sorts the data between an array.
• The SEQUENCE function generates a list of sequential numbers.
• The RANDARRAY function returns random numbers between 0 and 1.

• Press CTRL+Enter on the keyboard.

By creating this dynamic array method we can generate random data within a very short time.

Things to Remember

• When applying the “Data Analysis” toolpak method, you might not find it in the toolbar. You have to install it. Follow the instructions to install the toolpak.

File > Options > Select “Analysis Toolpak” from “Add-ins” window > OK > Put Tick in the “Analysis Toolpak” > OK.

• The RANDARRAY and the SORTBY function are only available in excel 365. If you are not a user of this version, you cannot apply these functions.
• After getting the random data don’t forget to convert them into values. Otherwise, the data will keep changing with every click.
• The RANDBETWEEN function generates duplicate data.

Conclusion

Hope you enjoyed the article. If you find any problems don’t forget to share in the comment section below. Have a great day!

Related Articles

• How to Generate Random 10 Digit Number in Excel (6 Methods)
• Generate Random Number in Excel with Decimals (3 Methods)
• Random Number Generator with Normal Distribution in Excel (4 Methods)
• Generate Random Number with Mean and Standard Deviation in Excel
• Auto Generate Invoice Number in Excel (with 4 Quick Steps)