Build a Random Number Generator in Google Sheets in 2 Mins

A sequence of numbers is said to be statistically random when it does not contain any recognizable patterns.

In statistics, randomness can be quite crucial. For example, one often needs to use random numbers when selecting random samples, to ensure that the study is conducted without bias.

Unfortunately, expecting non-biased results from computers can be quite far-fetched.

However, spreadsheet software like Google Sheets uses clever techniques and functions to generate random numbers.

In this tutorial, we will take a look at two Google Sheets functions that help generate random numbers:

• The RAND function
• The RANDBETWEEN function

Each function is a random number generator in Google Sheets, but they differ in the type of input they take in and the type of output they return.

How to Use a Random Number Generator in Google Sheets

You can use the RAND function as a Google Sheet random number generator. All you need to do is type the formula RAND() into a cell. This formula doesn’t have any arguments. You can then copy and paste the formula to the rest of your column by clicking ad dragging the blue square at the corner of the cell.

Learning More

If you’re learning how to make a random number generator in Google Sheets, chances are you will nest it into more complex formulas that can get a little tricky. We touch on how to do this in the article, but may not show the exact formula you need to create. We recommend studying a full Google Sheets course so you can become adept and building formulas to help with this and any other trouble you run into.

Using the RANDBETWEEN Function to Generate Random Numbers in Google Sheets

The RANDBETWEEN function of Google Sheets lets you generate random integer numbers within a given range.

Therefore it lets you specify upper and lower limits and then returns a random number between the two integers.

Syntax of the RANDBETWEEN Function

The syntax for the RANDBETWEEN function is as follows:

=RANDBETWEEN([lower_limit],[upper_limit])

As can be seen from the above syntax, the RANDBETWEEN function takes two inputs:

• lower_limit: This is an integer that demarcates the lower limit of the range. It specifies that the Google Sheets random number generated should be greater than or equal to this parameter.
• upper_limit: This is an integer that demarcates the upper limit of the range. It specifies that the random number generated should be less than or equal to this parameter.

Note that both upper_limit and lower_limit parameters must contain integer values. This could be a numeric integer value or reference to a cell containing an integer.

It goes without saying that the lower limit value should be less than the upper limit value.

How to Use the RANDBETWEEN Function in Google Sheets

Using the RANDBETWEEN function to generate random numbers is really easy. Let us take an example to see how it works.

In this example, we will generate five random numbers between 1 and 10:

1. Click on a cell in Google Sheets where you want to insert a random number.
2. Type in the function: =RANDBETWEEN(1, 10)
3. Press the Return key.
4. This should display any random integer between 1 and 10 in the same cell.
   

   

5. Since we want to generate 5 random numbers like this, just drag down the fill handle (located at the lower right corner of the cell) till you reach the fifth cell.
6. You should now have a set of 5 randomly generated numbers between 1 and 10.
   

   

You can use this method to generate integers between any range of integer values.

If you want to use an integer value from a different cell in the function, you can enter a reference to that cell as follows:

Note that the RANDBETWEEN and the RAND function are volatile. This means that these would recalculate in case there are any changes in the worksheet. In case you don’t want these values to recalculate, you can convert these formula results into static values.

Limitations of RANDBETWEEN Function

The RANDBETWEEN function works great when all you need are a bunch of random integers. However, it’s not the most reliable method if you’re looking to generate a set of unique integers.

RANDBETWEEN will most probably give you unique results if you have a really large range and need to generate a small number of random integers.

For example, if you want to generate 10 numbers between 10 and 1000. However, it’s hard to get a random number generator with no repeats in Google Sheets, especially with a small range.

Moreover, as mentioned before, it only returns integers. So if you want random decimal values, the RANDBETWEEN function will not really serve many purposes.

You can, however, use the RAND function instead of applications like these.

Using the RAND Function to Generate Random Numbers in Google Sheets

The RAND function of Google Sheets lets you generate random decimal numbers between 0 and 1. The range includes the 0 and excludes the 1. In other words, you can have Google Sheets generate random numbers that are more than or equal to 0 and less than 1.

Syntax of the RAND Function

The syntax for the RAND function is as follows:

=RAND()

As can be seen from the above syntax, the RAND function takes no inputs or parameters.

How to Use the RAND Function in Google Sheets

Using the RAND function to generate random numbers is easy too. Let us take an example to see how it works.

In this example, we will generate five random numbers between 0 and 1:

1. Click on a cell in Google Sheets where you want to insert a random number.
2. Type in the function: =RAND()
3. Press the Return key.
4. This should display a random decimal between 1 and 10 in the same cell.
   

   

5. You can either round off the number to the number of decimal places you like or format the cell to display your required number of decimal places.
   

   

6. Since we want to generate 5 random numbers like this, just drag down the fill handle (located at the lower right corner of the cell) till you reach the fifth cell.
7. You should now have a set of 5 randomly generated decimal numbers between 0 and 1.
   

   

Although the function generates only decimal numbers between 0 and 1, you can modify the formula to generate both decimal numbers and integers within any range.

For example, if you want to have Google Sheet generate random numbers between 1 and 10, you can tweak the original rand formula as follows:

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

In fact, you can use the function to generate numbers within any range by generalizing the above formula as follows:

=RAND() * (upper_limit – lower_limit) + lower_limit

where upper_limit is the maximum value of your required range, and lower_limit is the minimum value.

If you want to generate integer numbers between 1 and 10 instead of decimals, you only need to wrap the above formula inside a TRUNC function as follows:

=TRUNC(RAND() * (upper_limit – lower_limit) + lower_limit)

So, to generate random integers between 1 and 10, your formula would be:

=TRUNC(RAND() * (10 - 1) + 1)

As can be seen, the RAND function is a much more versatile function than RANDBETWEEN.

Moreover, since the RAND function uses the range 0 to 1, there is a good probability that the numbers generated by it will not repeat (at least 100,000 times!).

How To Create Random Arrays (RANDARRAY function

You can also have a random number generator in Google Sheets for arrays by using the RANDARRAY function. The RAND function normally doesn’t have any arguments, but once you add an array, it gets two arguments. The syntax for the RANDARRAY function becomes:

RANDARRAY(rows, columns)

Rows: the number of rows to be filled with random values.

Columns: the number of columns to be filled with random values.

Let’s look at the example below:

Using the formula RANDARRAY(10,2) we have filled 10 rows and 2 columns with randomly generated numbers.

How to Create a Google Spreadsheet Random Number Generator Button

Unfortunately, there is currently no Google Sheets random number generator button for generating random numbers. However, you can create one using the App Scripts. Thanks to stackoverflow, we were able to obtain the following script that can do this:

function button() {

  const ss = SpreadsheetApp.getActive();

  const sh = ss.getActiveSheet();

  const rf = sh.getRange('A2').getValue();

  const rn = Math.floor(Math.random() * rf) + 1;

//const rn = Math.random() * rf + 1; //if you want a float number

  sh.getRange('B2').setValue(rn);

  SpreadsheetApp.flush();

}

Here’s how to make a random generator in Google Sheets using app scripts:

1. Assign the limit of the random number in cell A2. In our example, we used 50.
2. Go to Extensions > App scripts.

3. Paste the script in the app script window.

4. Click Save, then Run. You may have to allow permissions.
5. Go to your spreadsheet and add an image. You can do this by copy-pasting.

6. Right-click the image and choose Assign script.

7. Write the name of the script, which is the name of the function “button,” and click OK.

If you click the image, a number will be randomized in cell B2. Every time you click the image, it will return a different random number that is less than 50.

Differences between RAND & RANDBETWEEN

To sum up, let us go over the main differences between RAND and RANDBETWEEN:

• RANDBETWEEN takes two parameters that specify the range within which we want the random number generated. RAND, on the other hand, does not take any parameters. It can generate any number between 0 and 1.
• RANDBETWEEN returns an integer value, while RAND returns a random decimal number.
• If the range is small, RANDBETWEEN is less likely than RAND to generate unique random numbers.

Altering the Refresh Rate of RAND and RANDBETWEEN

Both RAND AND RANDBETWEEN are volatile functions. This means the number generated by these two functions doesn’t remain for long.

They recalculate a new value whenever there is a change in the sheet or when the sheet is re-loaded.

Moreover, any formula that uses the numbers generated by RAND or RANDBETWEEN also recalculates each time.

To change the frequency of recalculations, follow these steps:

1. Click on the File menu.
2. Select Spreadsheet Settings.
3. In the dialog box that appears, select the ‘Calculation’ tab.
4. Click on the dropdown under ‘Recalculation’ and select how often you want the function to recalculate.

Final Thoughts

In this tutorial, we showed you how to use two types of functions that can be used as a random number generator in Google Sheets – the RAND and RANDBETWEEN functions.

We also explained the basic differences between the two and how you can apply these functions to your spreadsheets. We hope you find our tips and this tutorial helpful. You can also have a look at our guide for the forecast function in Google Sheets or check out our paid templates to speed up your work. You can use the promo code SSP to save 50% on all the templates!

Other Google Sheets tutorials you may like:

• • How to Round Numbers in Google Sheets
  • How to Divide in Google Sheets (Numbers, Cells, or Columns)
  • How to Number Rows in Google Sheets (Add Serial Numbers)
  • ISNUMBER Function in Google Sheets (Check if Cell Contains Number)