Often you need to generate a set of random numbers. Whether to create sample data or just to test a script, writing random numbers manually inside a bunch of cells can be time-consuming and tedious. This is where the RAND function in Google Sheets comes in. It allows you to generate a random number using a simple formula.

This article will take an in-depth look at how you can do that in your spreadsheets. Read on to master random number generation in Google Sheets.

The RAND and the RANDBETWEEN Function

Using the RAND formula generates a random decimal number between 0 and 1 which is why you can use it as a random number generator in your spreadsheet. Here is the syntax for the RAND function: 

=RAND()

The formula has no arguments. If anything is written inside the bracket, the formula will return the #N/A error.

The RAND function is volatile, which means that it recalculates the value every time you make a change to the spreadsheet or if you reload the Google Sheets tab. Because the function recalculates each time, this can lead to slowdowns, especially if you have a lot of instances of the formula.

You can also use the RANDBETWEEN function using the following syntax: 

=RANDBETWEEN(lower, upper)

The formula has two arguments:

  • lower - This integer defines the lower limit of a range. The random number generated will be equal to or larger than this parameter.
  • upper - This integer represents the upper limit of the range. The random number generated will be equal to or less than this parameter.

Keep in mind that, similarly to RANDBETWEEN in Excel, both of these arguments must contain an integer, which can have either a cell’s address or a value. Also, keep in mind that the lower integer needs to be less than the upper integer.

Differences Between RAND and RANDBETWEEN

To help you differentiate between the two functions, here are a few ways the RAND and the RANDBETWEEN function aren’t alike:

  • The RANDBETWEEN function is less likely to produce unique random numbers with smaller ranges than the RAND function.
  • The RAND function returns decimal values, while the RANDBETWEEN function returns integer values.
  • The RAND function does not take any arguments, meaning the brackets are left empty. RANDBETWEEN, on the other hand, takes two arguments: the upper and lower limit.
  • RAND can generate any number between 0 and 1, while RANDBETWEEN can generate a random number between user-defined limits.

Random Number Generator Between Two Values

Generating a random number between two values needs some clever use of the RAND formula. First, let’s assume X and Y as the upper and lower boundaries. Keeping that in mind, the formula looks something like this: 

=RAND() * (X - Y) + Y

Assuming you have the values of 100 and 1, here is what the formula will look like in action: 

=RAND() * (100-1) + 1

A screenshot showing a Random Number Generator Between Two Values using the RAND function

Alternately, you can also use the RANDBETWEEN function for a similar purpose. Using the formula is pretty simple. You must write the upper and lower limits inside the formula, following the proper syntax. In this example, we will write the upper and lower bounds in the spreadsheet and the formula’s cell address.

Here are the steps you need to follow to do this:

  1. Write the upper and lower limits inside their respective cells. It doesn’t matter where the location of the cell is. The output should be fine as long as we write the addresses in the formula with the proper syntax.
    A screensho of the sample data for a random number generator in Google Sheets
  2. Type in the =RANDBETWEEN( formula. Enter the lower limit first and then the upper limit, separated by a comma. Finish it by putting a closing bracket.
    A screenshot showing the sample formula for building a random number generator
  3. Finally, press Enter to execute the formula. You should see a random number appear between the upper and lower limit.
    A screenshot showing the output for a randome number generator
  4. To generate the random number for two more cells in the column, You can use the autofill feature or click on the cell containing the formula, then tap and drag down the blue dot at the bottom right of the selected cell.
    a screenshot showing how to use the fill handle to move a formula

Create an Array of Random Numbers

You can also create an array of random numbers in your spreadsheet using the RANDARRAY function. ARRAY functions are like great little Google Sheets hacks that allow you to apply formulas in bulk. Here is the syntax for the formula: 

=RANDARRAY(row, column)

The formula has two optional arguments:

  • row - The number of rows you wish to display, a random number. This argument becomes necessary if you specify a value in the column argument.
  • column - The number of columns you wish to display a random number.

Here are the steps to follow if you wish to create an array with random numbers:

  1. Select the cell you wish to execute the formula in.
  2. Start writing =RANDARRAY( Now type in the number of rows and the number of columns separated by a comma.
  3. Finish off the formula by writing a closing bracket.
    A screenshot showing how to use the RANDARRAY function
  4. Press Enter to execute the formula.
    Results for several repetitions of the RANDARRAY Function

Changing the Refresh Rate for Volatile Functions

Both the RANDBETWEEN and RAND are volatile, meaning that the numbers generated by the two functions don’t stay the same. A new value is recalculated whenever a change is made to the sheet, or the page is reloaded.

However, you can change the recalculation settings to better suit your needs. To do this, follow these steps:

  1. Click on File on the top bar. This will open a dropdown menu. There, click on Settings.
  2. In the window that opens up, click on Calculation to open the new tab that allows you to change the recalculation settings.
  3. Click on the Recalculation option. This will open a dropdown menu with a few options.
    The options for Changing the Refresh Rate for Volatile Functions
  4. Select the option that suits your work.
  5. Click on Save settings to save the changes.

Wrapping Up the Random Number Generator in Google Sheets

There are several ways to generate a random number in Google Sheets, namely the RAND, RANDBETWEEN, and RANDARRAY functions. Having this can be great if you’re someone who needs random numbers in your spreadsheets.