Generate Random numbers between a Range in Excel

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.

 

Non-technical solution to generate unique random numbers in Excel


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.

 

Non-technical solution to generate unique random numbers in Excel