Excel - How To Create Random Data In Excel - Episode 1949
Microsoft Excel Tutorial: Creating Fake Data in Excel.
Welcome back to the MrExcel podcast. In this episode, we will be discussing how to create random data in Excel. This is a useful skill for creating datasets to illustrate concepts or for any other purpose.
To start, we will be using the =RANDBETWEEN function to generate random numbers within a specific range. In this example, we will be creating a list of asset IDs, so we will use the range of 80000 to 99999. Simply enter =RANDBETWEEN(80000,99999) and press Ctrl+Enter to stay in the cell. Then, copy this formula down to a thousand rows by using Ctrl+C and selecting the desired range using the name box and pressing Shift+Enter.
But what if we have a list of items that we want to randomly choose from? In this case, we can use the =INDEX function. For example, if we have a list of 40 company names, we can use =INDEX($P$3:$P$43,RANDBETWEEN(1,40)) to randomly select one of the names. Again, copy this formula down to a thousand rows and you will have a list of random company names.
Next, we will move on to creating random names. We have a list of 400 first names and 500 last names, so we will use the =INDEX function twice and concatenate the results using the & symbol. The formula will look like this: =INDEX($H$3:$H$402,RANDBETWEEN(1,400))&" "&INDEX($J$3:$J$502,RANDBETWEEN(1,500)). Copy this formula down to a thousand rows and you will have a list of random names.
Now, let's move on to creating random street addresses. We will use the =RANDBETWEEN function to generate a random number between 100 and 9999, and then use the =INDEX function to randomly select a street name and suffix from our lists. The formula will look like this: =RANDBETWEEN(100,9999)&" "&INDEX($L$3:$L$252,RANDBETWEEN(1,250))&" "&INDEX($N$3:$N$8,RANDBETWEEN(1,6)). Copy this formula down to a thousand rows and you will have a list of random street addresses.
Finally, we will create random values. Instead of using sequential numbers, we will use the =RANDBETWEEN function to generate a random number between 15 and 95, and then multiply it by 100 to get a value in the hundreds. The formula will look like this: =RANDBETWEEN(15,95)*100. Copy this formula down to a thousand rows and you will have a list of random values.
Remember, every time you make a change, all of the data will change. To prevent this, select the entire dataset, copy it, and then use the Paste Values option to paste the values only. This will ensure that your data remains the same even if you make changes.
Thank you for watching this episode of the MrExcel podcast. I hope you found this tutorial on creating random data in Excel useful. Don't forget to subscribe to our channel for more Excel tips and tricks. See you next time!
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
Table of Contents:
(00:00) Creating Random Data
(00:17) Using RANDBETWEEN to Generate Asset IDs
(00:34) Copying Data to a Thousand Rows
(00:50) Selecting Rows Below Current Cell
(01:06) Creating Random Company Names
(01:26) Using INDEX to Choose from a List
(01:54) Creating Random Contact Names
(02:05) Using CONCATENATE to Combine Data
(02:38) Generating Random Companies and Names
(02:48) Creating random Street Addresses
(03:19) Copying and Pasting Values
(03:38) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #excelformula #randomdata #pastevalues #camelcase
This video answers these common search terms:
how to create a random list of names in excel
how to create random data in excel
how to make a random name generator in excel
how to create a random data set in excel
how to add random name generator to excel
how to get a random name in excel
how to create a random generator in excel
how to randomize list of addresses on excel
how to use randbetween formula in excel
excel how to use randbetween
how to do randbetween in excel
how to use randbetween excel
Techniques for creating fake random data in Excel. Create whole numbers, names, company names, addresses, or values that end in 00 in order to create sample data in Excel. Also here - a cool way to select the next 1000 rows using the Name box and R1C1 formatting!
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...
Watch video Excel - How To Create Random Data In Excel - Episode 1949 online without registration, duration hours minute second in high quality. This video was added by user MrExcel.com 11 February 2015, don't forget to share it with your friends and acquaintances, it has been viewed on our site 2,182 once and liked it 44 people.