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.

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.

1. 3. Press ‘Enter’ to generate the number.

1. 4. Since RAND is a volatile function, it will generate a new number every time the sheet recalculates or refreshes.

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.

1. 6. Like any other formula, you can grab the fill handle and drag it to copy it to other cells.

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.

1. 2. Type the RANDARRAY function in an empty cell.

1. 3. For the first parameter, specify the number of rows you want in your array.

1. 4. For the second parameter, specify the numbers of columns you want to generate.

1. 5. Press ‘Enter’ to get the 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.

1. 2. In an empty cell, type the RANDBETWEEN function.

1. 3. Add the upper and lower boundaries. In this case, the function will generate numbers between 1 and 100.

1. 4. Press ‘Enter’ to see the result.

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.

1. 2. In an empty cell, type the RAND function followed by the multiplication operator.

1. 3. Add the upper and lower boundaries using the following formula: (upper boundary - lower boundary) + (lower boundary)

1. 4. Press ‘Enter’ to see the result.

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.

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

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

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.