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.

## How to Generate Random Data in Excel: 9 Suitable Methods

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-

`=RANDBETWEEN(1,100)`

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-

`=RANDBETWEEN(1,10)`

Where,

- The
**RANDBETWEEN**function returns integers between the given numbers.

- Press
**CTRL+Enter**.

You will find some duplicate values within the given range.

### 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.

#### 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-

`=RAND()`

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-

`=RAND()*9+1`

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

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-

`= ROUND( RAND( ) * ( 19 +1 ), 0 )`

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-

`= ROUND( RAND( ) * ( 10 - -5 ) + -5, 0 )`

Where,

- The
**ROUND**function rounds up to the nearest integer. - The
**RAND**function produces a random number between the upper and lower values.

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

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

### 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-

`=RAND()`

Where,

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

- Press
**CTRL+Enter**.

**Step 2:**

- Select the data that 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-

`=RANK.EQ(B5,$B$5:$B$13)`

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.

### 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-

`=RANDARRAY(4,3,10,30,TRUE)`

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-

`=VLOOKUP(RANDBETWEEN(1,10),Fruits,2)`

Where,

- The
**RANDBETWEEN**function distributes data within the given upper and lower values. - 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.

### 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
**P****arameters**. 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.

### 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.

### 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-

`=RANDBETWEEN(1,20)`

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
**V****alues**.

- 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-

`=RANK.EQ(B4,$B$4:$B$13)+COUNTIF($B$4:B4,B4)-1`

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.

### 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-

`=INDEX(SORTBY(SEQUENCE(10),RANDARRAY(10)),SEQUENCE(10))`

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**functions 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.

**Download Practice Workbook**

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

## 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!

**<< Go Back to Random Number in Excel |** **Randomize in Excel** **| Learn Excel**