Random data generation is often used to test functions included in Excel, in case you don’t have a master dataset to practice with.
Well, that said, you already guessed the main content in this article, right? Well, in this article I will share with you how to generate random data in Excel in the simplest way.
Okay, now let’s get to the details!
#first. Use Formula Bar to generate random data on Excel
To get started, I will enter a formula into Excel’s Formula bar.
In this example I will use the function RANDBETWEEN to add a random integer in Excel offline.
Here, we can specify a range of random numbers, and in this example we will take from 1 to 1,000 hectares.
Perform:
+ Step 1: Click the first cell where you want to add a random number.
Copy the formula below and paste it into Excel’s Formula bar. You can change the number inside the parentheses to suit your needs.
This formula will select a random number from 1 to 1,000 => Press Enter to use the formula.
=RANDBETWEEN(1,1000)
+ Step 2: In the lower right corner of that cell, hover your mouse over that cell until a black “+” symbol appears => finally hold and drag down the cells where you want to apply the formula.
+ Step 3: By default, the RANDBETWEEN function only returns integers, if you want to change to currency or thousandth-separated number format, do the following:
Highlight the column or cells you want to change => right click => and select Format Cells.
Choose Currency => then choose format Tiền tệ or Number => and press OK.
#2. Generate a random list of days – months – years
Similar to the above method, we still use the function RANDBETWEEN but use extra function DATE to create a list of days, months, and years extremely quickly.
You can copy and paste the following formula into Formular.
=RANDBETWEEN(DATE(2020;1;1);DATE(2020;12;31))
Then do Fill formula down to other cells, similar to Bước 2 above,
By default, Excel outputs the result as an integer. To switch back to the date/month/year format, right-click in the first data cell => then select Format Cells.
Choose Date => and select the format you want and you’re done.
#3. Use Define Name to generate random data
How to generate random data in Excel is not limited to numbers or dates. And when using the VLOOKUP function, we can also create a list of products, give it a name, and then generate a random list in another column.
+ Step 1: To start, we need to create a list of products. In the box:
A1you title isProduct ID(product code).- And oh
B1you enter the title asProduct Name(Product’s name)
=> Then you enter data for these 2 columns.
+ Step 2: Please scan and select / highlight from the box A2 come to B11 => then right click and select Define Name.
+ Step 3: Put your name in box Name (for example, I set it as pets) => and press OK.
+ Step 4: Select any cell you want to start generating data. For example the picture below..
+ Step 5: Copy and paste the following formula into the Format bar.
=VLOOKUP(RANDBETWEEN(1;10);pets,2)
=> And press Enter to apply. You can change the values (1,10) and name in cell B to better suit your needs.
Finally, fill the formula to apply to the cells you want.
Read more:
#4. Epilogue
Yes, so I have detailed instructions for you how to generate random data in excel (like creating a list of random integers, creating a list of random birth dates, .. ) in a very simple way.
Similarly, you can completely use the RANDBETWEEN function to combine with many different formulas and create more richer data samples, better suited to your needs…
Hope the article will be useful to you. Good luck !
CTV: Nguyen Hong Khanh – techtipsnreview
Note: Was this article helpful to you? Don’t forget to rate the article, like and share it with your friends and family!
Source: Instructions for 3 ways to generate random data in Excel
– TechtipsnReview
