Generating Non-Repeating Random Integers in Excel
1. In a blank column, enter the formula
=RAND() in the first cell.
- Copy the formula down to the number of cells you want to generate random numbers for.
2. Select the range of cells with the random numbers and click on the
Data tab in the ribbon.
3. Click on the
Sort button and select
Smallest to Largest.
4. In a separate column, enter the formula
=RANK(cell reference, range of cells with random numbers) in the first cell. Replace "cell reference" with the first cell in the range of random numbers and "range of cells with random numbers" with the entire range of random numbers.
- Copy the formula down to the number of cells you want to generate random numbers for.
5. Select the range of cells with the RANK function and click on the
Data tab in the ribbon.
6. Click on the
Sort button and select
Smallest to Largest.
7. In a separate column, enter the formula
=cell reference + (minimum value - 1) in the first cell. Replace "cell reference" with the first cell in the range of RANK values and "minimum value" with the lowest number in the range you want to generate random numbers for.
- Copy the formula down to the number of cells you want to generate random numbers for.
You should now have a list of non-repeating random numbers within the range you specified.