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!
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1949.
Create Random Data.
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
Today I need to create a dataset.
Dataset to illustrate a concept.
And the one thing I know, I feel that we have here, is an “Asset ID” and the two examples that I have there, are five digit numbers up in the 80 thousand or something like that.
So I'm going to do =RANDBETWEEN(80000,99999), right.
Just based on the data that I have, it seems to create the right type of number.
All right, so I press Ctrl+Enter to stay in that cell.
I want to copy this down to a thousand rows.
So I'm going to do Ctrl+C, I check this out, now pick us up from MrExcel message board.
If I come up here to the name box, I say I want to go to row R[ 1000 ], which is a thousand rows below current column and then press Shift+Enter, it selects all the way down to a thousand cells below me and that would work from any cell.
So it could be a cell 842 and do that it would go down to a 1841 and I'll paste, Ctrl+V.
Alright, so there is my list of random asset IDs.
All right, simple sequential numbers; just need to create something within that range.
What if we have a list?
So, over here I have a list of 40 company names I want to randomly choose from that list.
All right, so that's =INDEX of all these names over here: Ctrl+Shift+Down Arrow.
I'll press F4 to put the dollar signs in, ($P$3:$P$43,RANDBETWEEN(1,40)), because there is 40 entries.
Don't forget the second parenthesis.
Now, that we have that first list of a thousand over there in the column A, I can just double click to copy that down.
Alright, “Contact”.
When I come up with a name: first name, last name, so I have a list of 400 first names, 500 of last names.
So, now I need to do two indexes and concatenate them together.
So, =INDEX of the first names: Ctrl+Shift+Down Arrow, F4, ($H$3:$H$402,RANDBETWEEN(1,400))&” “&INDEX over here of the “Last Names”, ($J$3:$J$502,RANDBETWEEN(1,500)).
All right, and that creates a random name for us, all right.
So there we have random companies, random names.
“Street Addresses”.
Now, let's do =RANDBETWEEN(100,9999)&” “&INDEX of these common Street names, F4, ($L$3:$L$252,RANDBETWEEN(1,250))&” “&INDEX of these Street Suffixes, F4, ($N$3:$N$8,RANDBETWEEN(1,6)).
All right, let's see.
Looks good.
Double-click to copy that down and then finally I want to come up with some values.
And here, these aren't sequential numbers.
I want to come up with something, let's say, in hundreds.
I always want everything to end with 00.
So, I’m going to use =RANDBETWEEN(15,95)*100 – like that.
Double-click to copy that down and we now have created a thousand rows of completely fake data, ready to illustrate some, you know, example or something along those lines.
Of course, now, every time that we change something, all of the data changes.
So, what we need to do, is select the whole data set.
Well, Ctrl+C to copy and then from the HOME tab open the Paste drop-down and choose Paste Values, like that.
Alright, at this point we can get rid of all those names out there on the right hand side and we're ready to roll.
Okay, I want to thank you for stopping by.
I’ll see you next time for another netcast – MrExcel.
Learn Excel from MrExcel podcast, episode 1949.
Create Random Data.
Hey, welcome back to the MrExcel netcast, I’m Bill Jelen.
Today I need to create a dataset.
Dataset to illustrate a concept.
And the one thing I know, I feel that we have here, is an “Asset ID” and the two examples that I have there, are five digit numbers up in the 80 thousand or something like that.
So I'm going to do =RANDBETWEEN(80000,99999), right.
Just based on the data that I have, it seems to create the right type of number.
All right, so I press Ctrl+Enter to stay in that cell.
I want to copy this down to a thousand rows.
So I'm going to do Ctrl+C, I check this out, now pick us up from MrExcel message board.
If I come up here to the name box, I say I want to go to row R[ 1000 ], which is a thousand rows below current column and then press Shift+Enter, it selects all the way down to a thousand cells below me and that would work from any cell.
So it could be a cell 842 and do that it would go down to a 1841 and I'll paste, Ctrl+V.
Alright, so there is my list of random asset IDs.
All right, simple sequential numbers; just need to create something within that range.
What if we have a list?
So, over here I have a list of 40 company names I want to randomly choose from that list.
All right, so that's =INDEX of all these names over here: Ctrl+Shift+Down Arrow.
I'll press F4 to put the dollar signs in, ($P$3:$P$43,RANDBETWEEN(1,40)), because there is 40 entries.
Don't forget the second parenthesis.
Now, that we have that first list of a thousand over there in the column A, I can just double click to copy that down.
Alright, “Contact”.
When I come up with a name: first name, last name, so I have a list of 400 first names, 500 of last names.
So, now I need to do two indexes and concatenate them together.
So, =INDEX of the first names: Ctrl+Shift+Down Arrow, F4, ($H$3:$H$402,RANDBETWEEN(1,400))&” “&INDEX over here of the “Last Names”, ($J$3:$J$502,RANDBETWEEN(1,500)).
All right, and that creates a random name for us, all right.
So there we have random companies, random names.
“Street Addresses”.
Now, let's do =RANDBETWEEN(100,9999)&” “&INDEX of these common Street names, F4, ($L$3:$L$252,RANDBETWEEN(1,250))&” “&INDEX of these Street Suffixes, F4, ($N$3:$N$8,RANDBETWEEN(1,6)).
All right, let's see.
Looks good.
Double-click to copy that down and then finally I want to come up with some values.
And here, these aren't sequential numbers.
I want to come up with something, let's say, in hundreds.
I always want everything to end with 00.
So, I’m going to use =RANDBETWEEN(15,95)*100 – like that.
Double-click to copy that down and we now have created a thousand rows of completely fake data, ready to illustrate some, you know, example or something along those lines.
Of course, now, every time that we change something, all of the data changes.
So, what we need to do, is select the whole data set.
Well, Ctrl+C to copy and then from the HOME tab open the Paste drop-down and choose Paste Values, like that.
Alright, at this point we can get rid of all those names out there on the right hand side and we're ready to roll.
Okay, I want to thank you for stopping by.
I’ll see you next time for another netcast – MrExcel.
