Skip to Content

How To Generate Random Numbers in Google Sheets

Creating a set of random numbers is often necessary. Writing random numbers into a bunch of cells by hand, whether to make sample data or test a script, can take a long time and be boring.

This is where Google Sheets’ RAND and RANDBETWEEN functions come in. It lets you use a simple formula to generate random numbers.

This article will detail how to do that in your spreadsheets.

Read on to learn how to Generate random numbers in Google Sheets.

The RANDBETWEEN and RAND Functions

Using the RAND formula gives you a random decimal number between 0 and 1, so you can use it in your spreadsheet as a random number generator. Here is how the RAND function is written:

= RAND ( )

There are no arguments for the formula. If you put anything in the bracket, the formula will give you the #N/A error.

The RAND function is volatile, meaning every time you change the spreadsheet or reload the Google Sheets tab, it recalculates the value. Since the Function recalculates every time, it can slow things down, mainly if you use the formula a lot in your spreadsheet.

You can also use the following syntax to utilize the RANDBETWEEN function:

=RANDBETWEEN (low, high)

Generate Random Numbers in Google Sheets

There are two parts to the formula:

  • Lower – This number is used to set the bottom of a range. The number picked randomly will be the same or more significant size than this parameter.
  • Upper – This number is the highest point in the range. The number picked randomly will be the same or less than this parameter.

Remember that, like RANDBETWEEN in Excel, both of these arguments must have an integer, which can be either the address of a cell or a value. Also, remember that the number on the bottom must be less than the number on the top.

How to Generate Random Numbers in Google Sheets using the RANDBETWEEN Function

The best way to generate random numbers in Google Sheets is to use the RANDBETWEEN function. Obey the below steps to do so:

  1. Launch your Google sheets document.
  2. Click on a cell where you want the random numbers to begin.
  3. Enter this formula in that cell: =RANDBETWEEN(1,10) to generate a random number between 1 and 10. Please replace 1 and 10 with your own range.
  4. Using the Fill Handle, drag to the other cells to copy the formula to generate the random numbers for those cells as well.
How to Generate Random Numbers in Google Sheets using the Rand Between Function

And there you have it.

What’s different about RAND and RANDBETWEEN?

Here are a few ways that the RAND function and the RANDBETWEEN function are different to help you tell them apart:

  • The RAND function gives back decimal numbers, while the RANDBETWEEN function gives back whole numbers.
  • There are no arguments for the RAND function, so the brackets are left empty. On the other hand, RANDBETWEEN needs two ideas: the upper limit and the lower limit.
  • The RAND function can make up any number between 0 and 1, while the RANDBETWEEN function can make up a random number between limits set by the user.

Using the RAND Function to make random numbers.

The RAND function is the first thing to look at when you want to make a random number. It’s a simple function whose only job is to create random numbers.

The RAND Function’s Syntax

The RAND function doesn’t need any arguments and gives back a random number between 0 and 1.

The number returned will be a decimal number greater than 0 and less than 1. It will never come back with 0 or 1.

The numbers that are given back are spread out uniformly. This means the chances of getting any number between 0 and 1 are the same.

The RAND function changes frequently. This means that every time you change the spreadsheet, refresh calculations, refresh the browser, or open the spreadsheet, the Function will be recalculated, and a new result will be returned.

With the RAND Function, a Simple Example

=RAND()

Use the RAND Function to make random numbers.

In this example, the RAND function has been typed into cell B3 and copied down the row.

The Function doesn’t need any arguments and gives back random decimal numbers between 0 and 1.

Use the RAND Function to create random numbers between two numbers

The RAND function is excellent for making random numbers between 0 and 1, but what if you want numbers between 1 and 3, 1 and 5.5, or -1 and 1? You could use the RANDBETWEEN function for that.

But what if you want to experiment with more options and would like to use the RAND function instead? It’s possible.

Random numbers between any two given numbers can be made with a simple formula that uses the RAND function.

= (B – A) * RAND () + B

The above formula will make a random decimal number between A and B, where A is less than B.

Let’s suppose the value of A = 20 and B = 10;

Since RAND will make a random distribution that is the same for both 0 and 1, this formula will make a random distribution that is the same for both A and B.

This is a great way to add to what the RAND function can do. As per the formula,

= (10 – 20) * RAND () + 10

In the example above, a set of random numbers between -10 and 10 were generated.

Use the RANDARRAY Function to make random numbers.

The RANDARRAY Function is another way to make numbers that are picked at random.

The RANDARRAY Function is similar to the RAND function, with one crucial difference.

The RAND function gives back a single random number, while the RANDARRAY Function gives back an array of random numbers.

The syntax for the Function RANDARRAY

Use the RANDARRAY Function to make random numbers.

The RANDARRAY Function will give back a list of random numbers. Every number in the array that is returned will be between 0 and 1.

= RANDARRAY (rows, columns)

  • Rows are how many rows are in the array that was returned.
  • Columns are how many columns are in the array that is returned.

If you don’t give the RANDARRAY Function the row index and column index, they will be set to 1 automatically.

RANDARRAY is a volatile function, meaning every time the spreadsheet is changed, it recalculates and returns a new set of values.

The values returned will be evenly spread between 0 and 1, so any single value has the same chance of being returned.

With the RANDARRAY Function, a Simple Example

= RANDARRAY (11, 12)

In this case, an array is made with four rows and three columns. Each of the 12 cells that are left has a random number from 0 to 1.

Changing the refresh rate for functions that change a lot

RANDBETWEEN and RAND are volatile, meaning that the numbers they come up with change over time. A new value is calculated whenever the sheet is changed, or the page is reloaded.

But you can change the settings for recalculating to make it work better. Follow these steps to do this:

  1. On the top bar, click File. Click on Settings there.
  2. In the new window, click Calculation to open a new tab where you can change the settings for recalculating.
  3. Click the button that says “Recalculate”. This will bring up a list of choices in a drop-down menu.
  4. Choose the one that works best for your work.
  5. Click “Save settings” to keep your changes.

Changing the rate at which RAND and RANDBETWEEN are updated

Both RAND and RANDBETWEEN are functions that change over time. This means that the numbers made by these two functions stick around for a short time.

When a change is made to the sheet or when the sheet is reloaded, they figure out a new value.

Also, any formula that uses numbers made by RAND or RANDBETWEEN needs to be recalculated every time.

Follow these steps to change how often recalculations happen:

  1. Select “File” from the menu.
  2. Go to Settings for Spreadsheet.
  3. In the box that comes up, choose the “Calculation” tab.
  4. Click the drop-down menu next to “Recalculation” and choose how often you want the Function to recalculate.

This guide showed you how to use the RAND function and the RANDBETWEEN function to make random numbers in Google Sheets.

Changing the rate at which RAND and RANDBETWEEN are updated

We also discussed the main differences and how to use these functions in your spreadsheets. Our tips and this tutorial will be helpful to you.

Do you need Google Sheets to come up with numbers at random?

They can be a great way to get sample data; in some statistical methods, they may even be a must.

The good news is that Google Sheets has several easy ways to make random numbers.

Google Sheets has an easy-to-use function that lets you make random numbers in your spreadsheet without leaving the document or installing an add-on. The Function gives back a random number between two given numbers.

Putting an End to the Google Sheets Random Number Generator

Google Sheets has functions called RAND, RANDBETWEEN, and RANDARRAY that can be used to make a random number. This can be very helpful if you need random numbers in your spreadsheets.

Hope you find the article informative. Follow us to read more articles on Google Sheets like this.

Disclosure: This page may contain a few affiliate links, which means if you buy something through them, we may get a commission (without any extra cost to you).