Generate Random numbers between a Range in Excel
This is how you Generate Random numbers between a Range in Excel
Enter the formula =RANDBETWEEN(1,10) in a cell. Replace the 1 with the lowest number in your desired range and 10 with the highest number.
Drag the formula to create the number of values you need.
There are a couple of things you’ll notice. One, each time the sheet calculates, the random numbers change. Two, the same number may be generated more than once.
Prevent the numbers from changing
Highlight the cells, right click Copy, right-click Paste Special, Values, OK;
Unique Random Numbers
If you require your list to have unique numbers, there are many possible solutions.
Here are a couple that I often teach because they are easy for most Excel users to understand.
Using RANDBETWEEN function and Remove Duplicates:
1) Enter the formula =RANDBETWEEN(low number, high number) in a cell;
2) Drag the formula to create the number of values you need plus some extra to account for duplicate values;
3) Convert to formulas to values by highlighting the cells, right-click Copy, right-click Paste Special, Values, OK;
4) With the cells still highlighted, from the Data tab click Remove Duplicates and click OK;
5) Finally, delete any extra numbers you don’t need.
Using the RAND function and Sorting:
Here’s another non-technical option for generating random non-duplicate numbers.
1) Enter all of the possible values in column B;
2) In column C enter =RAND() and copy down;
3) Click a cell in column C (the column containing the RAND functions) and sort A to Z (Data , Sort, Ascending) to randomize the order of the values;
4) Now you can select however many numbers you want starting from the top.