You can generate a list of random numbers using 2 functions, =RAND and =RANDBETWEEN.

=RAND() can be used to generate a number, in decimal, between 2 numbers. You can use it in the following manner: –

  • =RAND() generates a number between 0 to 1, in decimal.
  • =RAND()*100 generates a number between 0 to 100, in decimal.
  • =RAND()*50 generates a number between 0 to 50, in decimal.
  • =RAND()*(b-a)+a generates a number between any 2 numbers, ‘a’ and ‘b’ being the 2 numbers.
    • Eg., =RAND()*(10-5)+5 will generate a number between 5 and 10.
=RAND formula

Different ways of using =RAND

 

=RANDBETWEEN(bottom, top) can be used to generate a Whole number between any 2 numbers.
Egs.: –

  • =RANDBETWEEN (100, 200) will generate a whole number between 100 and 200.
  • =RANDBETWEEN (200, 100) will generate #NUM! error, as ‘bottom’ number cannot be bigger than the ‘top’ number.
=RANDBETWEEN(bottom, top) formula

Bottom number cannot be bigger than Top number in =RANDBETWEEN

 

Note that generated random number will keep changing every time anything on the sheet is calculated.

If you want to avoid this and freeze the number generated, paste it special as ‘values’.
Click here to know how to ‘paste as values’ and convert a formula to the value itself.

If you want to generate a list of random numbers using any of these formulas, follow these steps: –

  1. Select all the cells.
  2. Enter the formula in the 1st cell.
  3. Press Ctrl + Enter, instead of only Enter.
Use Ctrl+Enter to generate list of random numbers

Use Ctrl+Enter to generate list of random numbers

Keep Excel-ling!