Do you need to generate random numbers in Google Sheets?

They can be a great way to create sample data or they might even be essential to some statistical methods.

The good news is, there are several easy ways to generate random numbers in Google Sheets.

I’m going to show you five different methods that will help make generating random numbers easier than ever before.

1. RAND function
2. RANDBETWEEN function
3. RANDARRAY function
4. Apps Scripts
5. Third party Add-ons

Read on to find out just how simple it is with this step-by-step guide! Make sure you get the example workbook to follow along.

Generate Random Numbers with the RAND Function

The first method to look at when creating random numbers is the RAND function.

It’s a simple function whose only purpose is to create random numbers.

Syntax for the RAND Function

RAND ( )

The RAND function takes no argument inputs and returns a random number between 0 and 1.

Notes

The number returned will be a decimal number greater than 0 and less than 1. It will never return 0 or 1 as a result.

Numbers that are returned follow a uniform distribution. This means that every number between 0 and 1 is equally likely to occur in the results.

The RAND function is volatile. This means the function will recalculate and a new result will be returned every time you make a change in the spreadsheet, refresh calculations, refresh the browser, or open the spreadsheet.

Simple Example with the RAND Function

= RAND ( )

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

No arguments are needed and the function returns random decimal numbers between 0 and 1.

Generate Random Numbers Between A and B with the RAND Function

The RAND function is very useful for creating random numbers between 0 and 1, but what if you want to get numbers between 1 and 3, 1 and 5.5, or -1 and 1?

With a simple formula involving the RAND function, it is possible to generate random numbers between any two given numbers.

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

The above formula will generate a random decimal number between A and B, where A and B are any two numbers such that A is less than B.

Since RAND will generate a uniform random distribution across 0 and 1, this formula will generate a uniform distribution across A and B.

This is a great way to extend the capabilities of the RAND function!

= 20 * RAND ( ) - 10

In the example above you can see a set of random numbers generated between -10 and 10.

Generate Random Numbers with the RANDARRAY Function

Another function that will generate random numbers is the RANDARRAY function.

The RANDARRAY function is nearly the exact same as the RAND function but has one key difference.

While the RAND function returns a single random number, the RANDARRAY function can be used to return an array of random numbers.

Syntaxt for the RANDARRAY Function

The RANDARRAY function will return an array of random values. Each value in the returned array will be between 0 and 1.

= RANDARRAY ( rows, columns )

• rows is the number of rows in the returned array.
• columns is the number of columns in the returned array.

Notes

If you omit the row and column index arguments from the RANDARRAY function, they will default to 1.

RANDARRAY is a volatile function and will recalculate and return a new array of values any time the spreadsheet is changed.

The values returned will follow a uniform distribution over 0 and 1, where any single value is equally likely to occur.

Simple Example with the RAND Function

= RANDARRAY ( 4, 3 )

In this example, an array of 4 rows and 3 columns is produced. Each of the 12 resulting cells contains a random number between 0 and 1.

Generate Random Numbers Between A and B with the RANDARRAY Function

The RANDARRAY function is limited like the RAND function and only allows you to return numbers between 0 and 1.

This can also be extended with a simple formula in order to generate an array of random numbers between any two given numbers A and B.

= ARRAYFORMULA( ( B - A ) * RANDARRAY ( N, M ) + B )

The above formula will generate an array of random numbers between A and B with N rows and M columns.

Since the RANDARRAY function is uniformly distributed across 0 and 1, the formula above will produce uniformly distributed random numbers across N and M.

= ARRAYFORMULA( 15 * RANDARRAY ( 4, 3 ) + 5 )

For example, the above formula will produce an array of 4 rows and 3 columns. Each of the numbers in the resulting array will be a random number between -10 and 5.

Generate Random Numbers with the RANDBETWEEN Function

So far, you have seen a way to generate decimal random numbers using functions.

There is also a function to generate random integer numbers.

Syntaxt for the RANDBETWEEN Function

The RANDBETWEEN function allows you to generate a random integer number between any two given numbers.

= RANDBETWEEN ( minimum, maximum )

• minimum is the lower bound of the random number to return.
• maximum is the upper bound of the random number to return.

Notes

The RANDBETWEEN function can return values equal to the minimum or maximum.

The RANDBETWEEN function returns random values that are uniformly distributed, so any single number between the upper and lower bounds is equally likely to be returned.

The RANDBETWEEN function is volatile and will recaculate and return new values when you edit your spreadsheet.

Simple Example with the RANDBETWEEN Function

= RANDBETWEEN ( -10, 5 )

In this example, a single random integer value between -10 and 5 is returned.

Generate Random Numbers with Apps Scripts

Another interesting way to generate random numbers is by using Apps Scripts!

Check out this beginner’s guide to Apps Scripts for the complete run-down on how to use this tool.

Apps Scripts use the JavaScript programming language so you can leverage various JavaScript functions such as Math.random() to create a set of random numbers.

This is great if you want to create a set of static random numbers that don’t change when you update your workbook. The previous methods all used volatile functions that recalculate every time you update your workbook.

Go to the Tools menu and select the Script editor option to open the script editor.

Paste the below code into the script editor and save the script.

function fillRandom() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveRange();
  for (var col = 1; col <= range.getWidth(); col++) {
    for (var row = 1; row <= range.getHeight(); row++) {
      range.getCell(row, col).setValue(Math.random());
    }
  }
};

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Fill",
    functionName : "fillRandom"
  }];
  sheet.addMenu("Random", entries);
};

The above code has two function.

• fillRandom will fill each cell in the active range with a randomly generated number using the Math.random() function.
• onOpen will create a new menu in the UI which will allow you to run the fillRandom function.

The output will be similar to the RANDARRAY function, as it produces uniformly distributed decimal numbers between 0 and 1.

The random numbers inserted into the workbook are values and not formulas though, so won’t change when you edit your workbook.

You can then run this script from the menu.

1. Select a range of cells which you want to fill with random values.
2. Go to the Random menu.
3. Click on Fill.

This will run the script. The first time it runs you will need to give it the required permission to run.

The selected range will then get filled with random numbers between 0 and 1. These are values and not formulas, so they will remain unchanged when you edit your spreadsheet.

Generate Random Numbers with an Add-On

The last method for creating random numbers is to use an add-on.

Add-ons are third-party software that can extend the features available in Google Sheets.

There is even an add-in by Ablebits that is specifically for generating random numbers and it comes with many more options than the methods mentioned above.

You will be able to use this add-on for the following.

• Generate integer or decimal random numbers with lower and upper bounds.
• Generate unique values without repeats.
• Genrate random dates.
• Generate values from a custom list like a set of products from your company.
• Generate random text strings for passwords.

To install this add-on follow these steps.

Go to the Add-ons menu and click on Get add-ons from the options.

Search for Random Generator in the search bar and choose the Random Generator add-on by Ablebits from the resulting search options.

Click on Individual install to install the add-on.

Now you will be able to use this add-on from the Add-ons menu.

1. Go to the Add-ons menu.
2. Select Random Generator from the options.
3. Select Start from the submenu.

This will open up the Random Generator menu on the right side of the screen where you’ll find many options for generating random integers, decimals, Booleans, dates, custom list values, and strings.

Choose your desired options and click on the Generate button. It’s that easy to generate many different types of random output.

Conclusions

Google Sheets has many options for generating random numbers.

Google provides three functions for random numbers. The RAND, RANDARRAY, and RANDBETWEEN functions can all produce uniformly distributed random numbers.

If you’re looking to produce a set of static random numbers, you can also use Apps Scripts to fill a range with random numbers which won’t change.

You can even install an add-in for many more options to generate random numbers.

Hopefully, this post helps you with your random number needs. Let me know in the comments below!