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-

`=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.

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

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

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-

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

`=RAND()`

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-

`=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**.

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

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

`=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
**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-

`=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.

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

`=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**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)**