8 Easy Tricks to Generate Random Data in Excel

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.

Dataset to generate random data in Excel

APPROACH

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)

Using the RANDBETWEEN function to generate random data in Excel

  • Thirdly, press Ctrl+Enter.
  • Finally, you will see the random data as required.

Result of using the RANDBETWEEN function to generate random data in Excel

Undoubtedly, this is a very handy approach to work on.

📕 Read More: 5 Ways to Generate Random Number Between 0 and 1 in Excel

APPROACH

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.

Using the ROUND and RAND functions to generate random data in Excel

  • Afterward, tap Ctrl+Enter.
  • Finally, you will find the random data as required.

Result of using the ROUND and RAND functions to generate random data in Excel

This is unquestionably a highly useful strategy to develop.

📕 Read More: 7 Ways to Create Random 5 Digit Number Generator in Excel

APPROACH

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

Using the RAND function to generate random data in Excel

  • Thirdly, press Ctrl+Enter.

  • Now press Ctrl+C.
  • Then RightClick and select Paste Values from the Paste Options.
  • Now the data are converted to values.

Using the Paste Options

  • Fourthly, select cells D6 to D15.
  • Write the following formula.

=RANK.EQ(B6,$B$6:$B$15)

  • Press Ctrl+Enter.

Using the RANK.EQ function to generate random data in Excel

  • Consequently, you will see the results as well.

Result of using the RANK.EQ function to generate random data in Excel

Definitely, this is a really effective technique to implement.

📕 Read More: 4 Ways to Introduce Random Number in Excel with Decimals

APPROACH

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)

Using the RANDARRAY function to generate random data in Excel

  • 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

APPROACH

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 RightClick and select Define Name.

  • A dialogue box will appear to you as name New Name.
  • Choose the Name and tap OK.

New Name dialogue box

  • 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 VLOOKUP and RANDBETWEEN functions to generate random data in Excel

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

APPROACH

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.

Using the RANDBETWEEN function to generate random data in Excel

  • Now press Ctrl+C.
  • Then RightClick 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.

Using the RANK.EQ and COUNTIF functions to generate random data in Excel

Naturally, it will be very beneficial to use this strategy.

📕 Read More: Generating Random Number Between Two Numbers in Excel

APPROACH

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.

Applying the Data Analysis feature to generate random data in Excel

  • Then you will get a dialogue box name Data Analysis.
  • Thirdly, select Random Number Generation and then press OK.

Data Analysis dialogue box

  • 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

APPROACH

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.

VBA window

  • 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

VBA code to generate random data in Excel

  • Fourthly, press Run.
  • Finally, you will find the desired result.

Result of using VBA code to generate random data in Excel

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.

Using the fill handle icon to auto-generate numbers in Excel

  • 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

VBA code to generate random numbers without duplicates or to generate unique numbers

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


Related Article

(Visited 45 times, 1 visits today)
Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo