Don’t forget to share this post

There are many reasons why you may need a random number generator, such as creating sample datasets, testing code, or even playing dice. While the generation of true random numbers remains problematic, pseudo-random number generators are random enough for common uses, including those already mentioned above. Fortunately, Google Sheets provides three functions for random number generation: RAND, RANDARRAY, and RANDBETWEEN.

In this guide, you will learn how to use the RAND, RANDARRAY, and RANDBETWEEN functions in Google Sheets. In addition to the step-by-step instructions on using each function, you will also learn how to combine them and use them in formulas.

Can I Generate a Random Number with Google Sheets?

There are three Google Sheets functions you can use to generate random numbers. RAND generates decimals between 0 (inclusive) and 1 (exclusive). RANDARRAY works the same way but generates an array instead of a single number. RANDBETWEEN, however, generates integers between the boundaries you specify.

From a strictly mathematical perspective, these are not true random number generators but pseudo-random number generators. However, unless you’re currently working on a mathematical proof, the numbers generated should be random enough.

How to Use the RAND Function in Google Sheets?

Follow the steps below to use the RAND function in Google Sheets.

  1. 1. Open Google Sheets.
How to Make a Random Number Generator in Google Sheets Open Google Sheets
How to Make a Random Number Generator in Google Sheets - Open Google Sheets
  1. 2. In an empty cell, type the RAND function as shown below. The function doesn’t take any arguments, so inserting anything between the parentheses will lead to an error.
How to Make a Random Number Generator in Google Sheets Type RAND Function
How to Make a Random Number Generator in Google Sheets - Type RAND Function
  1. 3. Press ‘Enter’ to generate the number.
How to Make a Random Number Generator in Google Sheets Random Number
How to Make a Random Number Generator in Google Sheets - Random Number
  1. 4. Since RAND is a volatile function, it will generate a new number every time the sheet recalculates or refreshes.
How to Make a Random Number Generator in Google Sheets Volatile Function
How to Make a Random Number Generator in Google Sheets - Volatile Function
  1. 5. If you need to create a static dataset for testing, you can do this by copying the cell or cells and using Edit > Paste special > Values only.
How to Make a Random Number Generator in Google Sheets Paste Values Only
How to Make a Random Number Generator in Google Sheets - Paste Values Only
  1. 6. Like any other formula, you can grab the fill handle and drag it to copy it to other cells.
How to Make a Random Number Generator in Google Sheets Copy Formula
How to Make a Random Number Generator in Google Sheets - Copy Formula
Top Free Google Sheets Templates and Financial Models
Top Free Google Sheets Templates for 2023

Top Free Google Sheets Templates and Financial Statements to help you manage your business financials, monitor performance, and make informed decisions.

READ MORE

How to Use the RANDARRAY Function in Google Sheets?

If you need to generate an array of decimal values, you don’t need to use multiple instances of RAND. Instead, use RANDARRAY to specify the number of rows and columns you want.

  1. 1. Open Google Sheets.
How to Make a Random Number Generator in Google Sheets Open Google Sheets 2
How to Make a Random Number Generator in Google Sheets - Open Google Sheets
  1. 2. Type the RANDARRAY function in an empty cell.
How to Make a Random Number Generator in Google Sheets RANDARRAY Function
How to Make a Random Number Generator in Google Sheets - RANDARRAY Function
  1. 3. For the first parameter, specify the number of rows you want in your array.
How to Make a Random Number Generator in Google Sheets Specify Number of Rows
How to Make a Random Number Generator in Google Sheets - Specify Number of Rows
  1. 4. For the second parameter, specify the numbers of columns you want to generate.
How to Make a Random Number Generator in Google Sheets Specify Number of Columns
How to Make a Random Number Generator in Google Sheets - Specify Number of Columns
  1. 5. Press ‘Enter’ to get the array.
How to Make a Random Number Generator in Google Sheets Random Array
How to Make a Random Number Generator in Google Sheets - Random Array

How to Use the RANDBETWEEN Function in Google Sheets?

Follow the steps below to use RANDBETWEEN to generate integers between the specified lower and upper bounds.

  1. 1. Open Google Sheets.
How to Make a Random Number Generator in Google Sheets Open Google Sheets 3
How to Make a Random Number Generator in Google Sheets - Open Google Sheets
  1. 2. In an empty cell, type the RANDBETWEEN function.
How to Make a Random Number Generator in Google Sheets RANDBETWEEN Function
How to Make a Random Number Generator in Google Sheets - RANDBETWEEN Function
  1. 3. Add the upper and lower boundaries. In this case, the function will generate numbers between 1 and 100.
How to Make a Random Number Generator in Google Sheets Generate Numbers
How to Make a Random Number Generator in Google Sheets - Generate Numbers
  1. 4. Press ‘Enter’ to see the result.
How to Make a Random Number Generator in Google Sheets Generate Random Integer
How to Make a Random Number Generator in Google Sheets - Generate Random Integer
How to Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet

READ MORE

How to Set Upper & Lower Boundaries with the RAND Function in Google Sheets?

In this section, you will learn how to use RAND to generate random numbers from a range larger than the default (0-1). Follow the steps below to set an upper and lower boundary for the RAND function.

  1. 1. Open Google Sheets.
How to Make a Random Number Generator in Google Sheets Open Google Sheets
How to Make a Random Number Generator in Google Sheets - Open Google Sheets
  1. 2. In an empty cell, type the RAND function followed by the multiplication operator.
How to Make a Random Number Generator in Google Sheets RAND Function Multiplication Operator
How to Make a Random Number Generator in Google Sheets - RAND Function Multiplication Operator
  1. 3. Add the upper and lower boundaries using the following formula: (upper boundary - lower boundary) + (lower boundary)
How to Make a Random Number Generator in Google Sheets Upper and Lower Boundaries
How to Make a Random Number Generator in Google Sheets - Upper and Lower Boundaries
  1. 4. Press ‘Enter’ to see the result.
How to Make a Random Number Generator in Google Sheets Generate Random Decimal
How to Make a Random Number Generator in Google Sheets - Generate Random Decimal

How to Combine the RAND & RANDBETWEEN Functions in Google Sheets?

As you have seen, RAND only generates decimal numbers between 0 (inclusive) and 1 (exclusive). RANDBETWEEN, on the other hand, only generates integers between an upper and lower boundary. Therefore, you can easily combine them to get decimals between the boundaries you specify.

For example, you can add RAND and RANDBETWEEN to get random decimals in a given range, just like in the previous example using RAND.

How to Make a Random Number Generator in Google Sheets RAND RANDBETWEEN
How to Make a Random Number Generator in Google Sheets - RAND + RANDBETWEEN

You can also use it to get a negative range by subtracting them, as shown below.

How to Make a Random Number Generator in Google Sheets RAND RANDBETWEEN
How to Make a Random Number Generator in Google Sheets - RAND - RANDBETWEEN

To generate multiple values, grab the fill handle and drag it down as far as you need.

How to Make a Random Number Generator in Google Sheets Use Fill Handle
How to Make a Random Number Generator in Google Sheets - Use Fill Handle

Conclusion

As you have seen, generating random numbers in Google Sheets is easy. There are three functions you can use separately or combine. The first function, RAND, takes no arguments and generates decimals between zero and one. The second function, RANDARRAY, works just like RAND but generates an array of decimals. You can specify the size of the array through the function’s arguments: rows and columns. The third function, RANDBETWEEN, generates integers from a given range. You can specify the upper and lower limits of this range through the function’s arguments.

You now know how to use all three functions in Google Sheets. You also know that they are volatile functions, so the values generated will change with every recalculation or refresh on that sheet. If you need static values, make sure that after you generate them, you copy them and use the special paste option to keep values only. Additionally, you know how to combine these functions and adapt them to suit your needs.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jan 29 2023, Updated Jun 26 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets