Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
For a lot of people the term random number might sound a bit obscure, but it is a mathematical capability that has at least two good uses for wide applicability in Excel. One might be in the following situation. You might have a database. Now it could be about people, it might be about inventory, it might be about sales. Here and there and certain kinds of lists, you want to randomize the order. Maybe you have a certain primitive way of spot checking some data, your own idea of quality control.
How do we take this list and randomize it? Either add an empty column on the right- hand side or left side, it doesn't make any difference, and even though that doesn't say random, I am just going to type in random here just a small indicator here. We are going to be putting in a random number here that will actually allow us to sort. So one function is called =rand(). It's one of those functions that has parenthesis, but nothing between them so we need to only type the left one. We can press Ctrl+Enter here. It might look like that. We don't care what it looks like really. Double-click the lower right-hand corner.
If we were to see this, we could press comma if we wanted to but that's not critical. But this function simply generates a random number between 0 and 1. If we click anywhere in here is to the right of our data here, we will simply for a quick sort, click the Data tab > AZ. We have randomized this list. It is in no particular order at all. Although I probably wouldn't imagine doing this whole lot with a list of this type, here and there it makes good sense to randomize the order of the list. So that's one use of a function called Rand.
Now if you need to create sample data, and I suspect I do this more than a lot of people, but we might want to be testing out some formulas, we might want to be testing out some data, maybe what we would like to have here are some salaries or any kind of number you might imagine. If you want to generate whole numbers, and you could do this for a whole cluster of cells at once, you want to type =RANDBETWEEN. Now with any function if you start to type it and you see the pop-up list if you see it here you can certainly click it and press Tab there we go, and now what? I want a bunch of random numbers here. I am thinking of salaries, so I have got a group of them here starting at 30,000, comma, as high as 80,000 in this list is fine. That's it.
Enter, or in the case here if we want just to go into multiple cells, Ctrl+Enter. So we have got a bunch of random salaries. The other thing about this that might fool you though. If you were to click somewhere and maybe start putting in your headings or something like that you know, maybe this is for a certain city or whatever, as I press Enter watch these numbers. They all got regenerated. So if you create random numbers this way and want to use them for your sample data or whatever, right after or actually you don't have to do right after, but at some point here you might want to freeze these numbers and a good quick way to do this is to use one of our top ten tips where we simply take this data and with the right mouse button, drag any edge into another column and then right back on top of it and copy here as values only.
So we have turned these into pure values. Here and there when you need to create random numbers, whole numbers, you can easily do this with the function called RANDBETWEEN.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 97538 Viewers
80 Video lessons · 141067 Viewers
59 Video lessons · 59415 Viewers
52 Video lessons · 72741 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.