Sometimes we need to generate random data in Excel. In this article, we will show you how to generate random data in Excel with 8 easy approaches. We have also included some relevant concerns at the end of this article. So, let’s get started.
📂 Download Excel File
Download free workbook from here:
Learn to Generate Random Data in Excel with These 6 Approaches
While writing this article, “8 Approaches to Generate Random Data in Excel”, we’ve considered a dataset that contains approximately 3 columns and 15 rows. In this dataset, we added Brand, and Search Volume to make you better understand. But if you want, you can change the entities and put your own values.
1. Using RANDBETWEEN Function to Generate Random Data or Number Within Range in Excel
Using the RANDBETWEEN function is a very good strategy to generate random data in Excel. It is effortless and simple. You won’t face any trouble here. You just have to follow the given steps to achieve this process.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cells from C6 to C15.
- Secondly, write the following formula.
=RANDBETWEEN(1,100)
- Thirdly, press Ctrl+Enter.
- Finally, you will see the random data as required.
Undoubtedly, this is a very handy approach to work on.
📕 Read More: 5 Ways to Generate Random Number Between 0 and 1 in Excel
2. Adopting RAND Function to Generate Random Data in Excel
The RAND function is a very effective method for generating random data in Excel. It is extremely simple and easy. There won’t be any issues for you here. To complete this process, you simply need to adhere to the instructions.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, you need to select cells from C6 to C15.
- Secondly, you need to write the following formula.
= ROUND( RAND( ) * ( 19+1 ), 0 )
🔨 Formula Breakdown
👉 The RAND function generates a number at random from the given range.
👉 The ROUND function is applied to round numbers which means it will provide us with the integer value.
- Afterward, tap Ctrl+Enter.
- Finally, you will find the random data as required.
This is unquestionably a highly useful strategy to develop.
📕 Read More: 7 Ways to Create Random 5 Digit Number Generator in Excel
3. Combining RAND and RANK Functions to Generate Number from Lists
A fairly efficient way to generate random data in Excel is to combine the RAND and RANK functions. It is incredibly easy and uncomplicated. You won’t experience any problems here. You just need to follow the directions to finish this process.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, you need to select cells from C6 to C15.
- Secondly, you need to write the following formula.
=RAND()
- Thirdly, press Ctrl+Enter.
- Now press Ctrl+C.
- Then Right–Click and select Paste Values from the Paste Options.
- Now the data are converted to values.
- Fourthly, select cells D6 to D15.
- Write the following formula.
=RANK.EQ(B6,$B$6:$B$15)
- Press Ctrl+Enter.
- Consequently, you will see the results as well.
Definitely, this is a really effective technique to implement.
📕 Read More: 4 Ways to Introduce Random Number in Excel with Decimals
4. Using RANDARRAY Function
Using the RANDARRAY function to generate random data in Excel is a somewhat efficient method. It is straightforward and easy. You will have no problems here. To complete this process, merely adhere to the instructions.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, you have to select cells from C6 to C15.
- Secondly, put the following formula.
=RANDARRAY(1,1,5,30,TRUE)
- Belatedly, just tap Ctrl+Enter.
- Consequently, you will notice the random data as required.
Obviously, using this strategy will be quite beneficial.
📕 Read More: 8 Ways to Make Random 4 Digit Number Generator in Excel
5. Merging VLOOKUP and RANDBETWEEN Functions
A relatively effective technique for generating random data in Excel is to merge the VLOOKUP and RANDBETWEEN functions. It is incredibly easy and uncomplicated. You won’t run across any issues here. Just follow the directions to complete this process.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, we have considered a dataset containing Brand Serial and Brand.
- Now Right–Click and select Define Name.
- A dialogue box will appear to you as name New Name.
- Choose the Name and tap OK.
- Afterward, create a new column name Arbitrary.
- Belatedly, write the following function on cell E6.
=VLOOKUP(RANDBETWEEN(1,10),Food_Brands,2)
🔨 Formula Breakdown
👉 The RANDBETWEEN function spreads values between the greater and lesser values specified.
👉 When using the VLOOKUP function, a value is sought out and the value from a specified order within the same row is returned.
- Using the fill handle drag the formula to the entire column.
- Eventually, the result will appear to you.
Of course, employing this tactic will be quite advantageous.
6. Uniting RANK.EQ and COUNTIF Functions
Uniting the RANK.EQ and COUNTIF tools in Excel seem to be an efficient approach to generating random data. It is really simple and easy. There will be no challenges for you here. To finish this procedure, adhere to the instructions.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select cells from C6 to C15.
- Secondly, you need to write the following formula.
=RANDBETWEEN(1,20)
- Then tap Ctrl+Enter.
- Now press Ctrl+C.
- Then Right–Click and select Paste Values from the Paste Options.
- Now the data are converted to values.
- Fourthly, select cells D6 to D15.
- Write the following formula.
=RANK.EQ(C6,$C$6:$C$15)+COUNTIF($C$6:C6,C6)-1
🔨 Formula Breakdown
👉 The RANK.EQ(C6,$C$6:$C$15) function determines and provides an investment’s empirical rank. Eventually, it will add up the values from cells C6 to C15.
👉 The COUNTIF function determines how many cells are present in a certain range.
- Press Enter.
- Finally, use the fill handle icon to copy the formula for the entire column.
Naturally, it will be very beneficial to use this strategy.
📕 Read More: Generating Random Number Between Two Numbers in Excel
7. Applying Data Analysis Feature
To generate random data in Excel, applying the Data Analysis feature seems to be a practical method. It is incredibly easy and uncomplicated. There won’t be any troubles for you here. Just follow the directions to complete this process.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, go to the Excel top ribbon.
- Secondly, choose the Data tab and then click on the Data Analysis option.
- Then you will get a dialogue box name Data Analysis.
- Thirdly, select Random Number Generation and then press OK.
- Now you will get a dialogue box name Random Number Generation.
- Select the Number of Variables that refers to the Number of Columns.
- Then select the Number of Random Numbers that refers to the Number of Rows.
- The Distribution should be Uniform.
- We chose the number from 1 to 30.
- Afterward, give the Output Range.
- Finally, tap OK.
- Consequently, you will get the result that you wanted.
Therefore, executing this tactic will be quite lucrative.
📕 Read More: 6 Ways to Generate Random 10-Digit Number in Excel
8. Application of VBA Macros
Application of VBA code appears to be a practical approach to generating random data in Excel. It is effortless and easy. You won’t encounter any obstacles here. To finish this procedure, adhere to the instructions.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, go to the worksheet.
- Secondly, press Alt+F11.
- Now insert a Module.
- Then you will see a module there.
- Thirdly, paste the following code.
Code
Sub RandomNumber1()
Dim i1 As Integer
i1 = 15
For i1 = 6 To i1
Cells(i1, 3).Value = Int((60 - 50 + 1) * Rnd + 50)
Next i1
End Sub
- Fourthly, press Run.
- Finally, you will find the desired result.
As a result, using this strategy will be quite profitable.
Auto Generate Number in Normal Distribution in Excel
You can auto-generate number in Excel. It is straightforward. Just follow the given steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, go to the worksheet.
- Secondly, write 1 and 2 to cells C6 and C7.
- Now select those two cells.
- Thirdly, use the fill handle icon to drag the numbers downs there.
- Eventually, you will see the auto-generated number as well.
Indeed, it is a very easy and handy procedure to work on.
How to Generate Random Number Without Duplicates or Unique Numbers in Excel
If you want to generate random number without duplicates or unique number in Excel then this portion is for you. Follow the given steps.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, go to the worksheet.
- Secondly, press Alt+F11.
- Thirdly, insert a Module.
- Then you will see a module there.
- Thirdly, paste the following code.
Code
Public Sub NumberWithoutRepeat()
lowerbound1 = 1
upperbound1 = 30
Set cellRange1 = Range("C6:C15")
cellRange1.Clear
For Each Rng In cellRange1
randomNumber = Int((upperbound1 - lowerbound1 + 1) * Rnd + lowerbound1)
Do While Application.WorksheetFunction.CountIf(cellRange1, randomNumber) >= 1
randomNumber = Int((upperbound1 - lowerbound1 + 1) * Rnd + lowerbound1)
Loop
Rng.Value = randomNumber
Next
End Sub
- Fourthly, press Run.
- Finally, you will find the required result.
Finally, executing this tactic will be quite lucrative.
📄 Important Notes
You should be aware of the following things while performing the processes mentioned above:
🖊️ You need to be careful while using the Excel functions.
🖊️ While using the VBA code you should be conscious.
📝 Takeaways from This Article
If we summarize the whole article, we have got some points.
📌 Firstly, we applied the RANDBETWEEN functions to generate random data in Excel.
📌 Secondly, we use used the RAND function.
📌 Thirdly, we combined the RAND and RANK functions.
📌 Fourthly, we use used the RANDARRAY function.
📌 Fifthly, we merged the VLOOKUP and RANDBETWEEN functions.
📌 Sixthly, we united the RANK.EQ and COUNTIF functions
📌 Seventhly, we used the Data Analysis feature.
📌 Afterward, we applied the VBA code.
📌 Belatedly, we tried to show you how to auto-generate number in Excel.
📌 Finally, we tried to show you how to generate random number without duplicates in Excel.
Conclusion
We wish that you would be able to generate random data in Excel using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If there is any skepticism in your mind, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.