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-
=RANDBETWEEN(1,100)

Where,

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

Apply RANDBETWEEN Function to Generate Random Data in Excel

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

Apply RANDBETWEEN Function to Generate Random Data in Excel

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.

Apply RANDBETWEEN Function to Generate Random Data in Excel

  • Press CTRL+Enter.

Apply RANDBETWEEN Function to Generate Random Data in Excel

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.

Generate Data Between 0 and 1

Generate Data Between 0 and 1


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.

Generate Data Between Any Two Numbers

  • Click CTRL+Enter.

Generate Data Between Any Two Numbers

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

 Produce Integer Data Between Any Two Numbers

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

 Produce Integer Data Between Any Two Numbers

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.

 Produce Integer Data Between Any Two Numbers

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

 Produce Integer Data Between Any Two Numbers

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

Combine RAND and RANK Functions to Get Unique Random Data

  • Press CTRL+Enter.

Combine RAND and RANK Functions to Get Unique Random Data

Step 2:

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

Combine RAND and RANK Functions to Get Unique Random Data

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

Combine RAND and RANK Functions to Get Unique Random Data

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

Combine RAND and RANK Functions to Get Unique Random Data

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.

Combine RAND and RANK Functions to Get Unique Random Data

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

Combine RAND and RANK Functions to Get Unique Random Data

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.

Combine RAND and RANK Functions to Get Unique Random Data

  • Click CTRL+Enter.

Perform RANDARRAY Function to Generate Random Data in Excel

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.

Combine VLOOKUP and RANDBETWEEN Function to Generate Random Data in Excel

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.

Combine VLOOKUP and RANDBETWEEN Function to Generate Random Data in Excel

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.

Combine VLOOKUP and RANDBETWEEN Function to Generate Random Data in Excel

  • 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


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

Apply the Data Analysis Toolpak to Generate Random Data in Excel

  • 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

Run a VBA Code to Generate Random Data in Excel

  • 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

Run a VBA Code to Generate Random Data in Excel

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

Run a VBA Code to Generate Random Data in Excel

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 a VBA Code to Generate Random Data in Excel

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

Run a VBA Code to Generate Random Data in Excel

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.

Run a VBA Code to Generate Random Data in Excel

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.

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

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

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

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

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

  • Press CTRL+Enter.

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

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.

Create a Dynamic Array to Generate Random Data in Excel

  • Press CTRL+Enter on the keyboard.

Create a Dynamic Array to Generate Random Data in Excel

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

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo