How-To

How to Create a Random Number Generator in Excel

By Adam Davidson

Published September 27, 2022

Do you need to generate a series of random numbers? Excel makes this simple and easy to do. Here’s how to create a random number generator in Excel.

Do you need to generate a list of random numbers? If so, it’s fairly easy to create a random number generator using Excel.

You can quickly generate a set of random integers between two values of your choice. You can also generate random decimal values, or even create a random list of each value from 1 to 20.

If you’re ready to get random, here’s how to create a random number generator in Excel.

How to Generate Random Numbers in Excel using RANDBETWEEN

If you want to generate random numbers in a particular range, the RANDBETWEEN function is a quick and easy way to do so. This allows you to generate random integers between any two values that you choose.

To generate random numbers in Excel using RANDBETWEEN:

1. Click in the cell where you want your first random number to appear.
2. Type =RANDBETWEEN(1,500) replacing ‘1’ with the lowest random number you want to generate, and ‘500’ with the highest.
   
3. Press Enter and a random number in the range will be generated.
   
4. Click and hold the small square in the bottom-right of the cell and drag downwards to fill up as many cells as you wish with the same formula.
   
5. The numbers will regenerate each time you make a change in Excel. If you want to keep them the same, copy and paste your formula as values.
   

How to Generate Random Numbers in Excel using RAND

You can also achieve the same result by using the RAND function, although this takes a little more effort if you want to generate integers.

To generate random numbers in Excel using RAND:

1. Click in the cell where you want your first number to appear.
2. Type =RAND().
   
3. Press Enter and a random decimal between 0 and 1 is generated.
   
4. If you want to convert this to an integer, change your formula to =ROUND(RAND()*100,0).
   
5. This will generate random numbers between 1 and 100, by multiplying the decimal by 100 and rounding it to the nearest whole number. You can change ‘100’ to whatever value you wish.
   
6. To create more random numbers in the same way, click and hold the small square in the bottom right-hand corner of the cell and drag it downwards.
   
7. Any time a new formula is processed in Excel, the random numbers will recalculate. To keep one specific set of values, copy and paste your formula as values.
   

How to Generate Unique Random Numbers in Excel

Due to the way that both RAND and RANDBETWEEN generate numbers, you may find that you get the same value more than once in your list of random numbers. This is to be expected; if the numbers were truly random, repeated values would be likely to occur if you generated enough values.

If you want to randomize a specific set of values, however, then it’s possible to do so. For example, you might want to put the numbers 1 to 20 into a random order.

To generate unique random numbers in Excel:

1. Click in the cell where you want to generate your first random number.
2. Type =RAND() and press Enter.
   
3. Click and hold the small square in the bottom-right of the cell.
   
4. Drag down to create 20 random values.
   
5. In the cell next to your first random value, type =RANK(B2, B$2:B$21) where ‘B’ is the column where you created your random values, ‘2’ is the row of the first value and ‘21’ is the row of the last value.
   
6. Press Enter and a value between 1 and 20 will generate.
7. Click and hold the square in the bottom-right corner of the cell and drag it down to create 20 values.
   
8. Excel will create a series of random numbers between 1 and 20 in randomized order.
9. In the unlikely event that you get a duplicate random decimal in your first row, type = in any empty cell and press Enter. The values will regenerate.

Harness the Power of Excel

Learning how to create a random number generator in Excel is just one of the many ways that you can use Excel for far more than just storing a table of data.

For example, did you know that you can ring an alarm in Excel when certain criteria are met? You can also insert checkboxes in Excel to create useful checklists for work or home. You can also create all manner of charts, from radar charts to box plots.