How To Generate Random Numbers In Google Sheets

Kieran Dixon Updated:  July 26, 2021

Can you generate random numbers in Google Sheets?

Yes!

Here's how to:

• Generate random numbers:
  • Between 0 and 1
  • Between any two numbers
    • Whole numbers
    • Decimal numbers
  • No repeats
• Create a button to refresh random numbers
• Stop random numbers from changing

Getting Random Numbers In Google Sheets

Random Numbers Between 0 and 1

There's a function in Google Sheets that generates random numbers between 0 (inclusive) and 1 (exclusive):

It doesn't accept arguments like most functions. Instead you simply open and close the brackets and get a random decimal number that's >= 0 and < 1.

Put another way: 0 <= RAND() < 1.

Here's what it looks like (click the checkbox to refresh):

Random Numbers Between Any Two Numbers

Get Whole Numbers

Once again, Google Sheets has a function for exactly this:

You get a random integer (whole number) between the low and high (both inclusive).

Put simply: low <= RANDBETWEEN(low,high) <= high

Some common uses of this function is to get a random number from 1 to 10 or 1 to 100.

Here's an example of getting a random number from 1 to 100 (click the checkbox to refresh):

Get Decimal Numbers

If you need a decimal number between a low and high you'll need to use a formula.

This is inclusive of the low and exclusive of the high.

Another option is:

However, this function calls for a random number twice which makes it less efficient to calculate.

Here's an example of the first formula generating random decimals between 1 and 100 (click the checkbox to refresh):

Generate Random Numbers With No Repeats

Ensuring there are no repeats in random number sets generated by Google Sheets is difficult because the nature of randomness allows repetition.

The way to get no repeats is a little hacky but it gets the job done.

The trick is that if you want 5 non-repeating random numbers, you need to:

1. Generate more than 5 random numbers (using the RANDARRAY function)
2. Get the unique numbers (using the UNIQUE function)
3. Show only 5 of them (using the ARRAY_CONSTRAIN function)

By generating more than 5 random numbers you increase the likelihood that at least 5 of the numbers will be unique - and that's what you really want. In the examples below you will generate 10 times the required amount of numbers.

There is a small chance that all of the numbers you generate do repeat.

But it's so unlikely that you don't need to worry about it.

If you need random numbers from 0 to 1, you can use:

Here's what that will look like for 5 random numbers (click the checkbox to refresh):

If you need random whole numbers from low (inclusive) to high (exclusive):

This is an example with 5 numbers between 1 and 100 (click the checkbox to refresh):

If you need random decimal numbers from low (inclusive) to high (exclusive):

Here's an example that pulls 5 decimal numbers between 1 and 100 (click the checkbox to refresh):

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Get The Cheat Sheet ➜

You'll get updates from me with an easy-to-find "unsubscribe" link.

How To Create A Button To Refresh Random Numbers In Google Sheets

To refresh random numbers in Google Sheets you need to change a value.

This is because functions that generate random numbers (RAND, RANDBETWEEN, and RANDARRAY) are volatile. This means they update whenever a change occurs in the sheet.

You can make a 'button' to generate random numbers by adding a checkbox somewhere in your Google Sheet.

Simply select a cell and go to Insert ➜ Checkbox in the main menu.

When you click on a checkbox in Google Sheets the underlying value in the cell changes from FALSE to TRUE (and vice versa for unchecking).

This is a change in the spreadsheet and is all that needs to happen for random numbers to refresh.

Here's an interactive example of what that looks like in a Google Sheet:

How To Stop Random Numbers From Changing In Google Sheets

As mentioned above, functions that generate random numbers (RAND, RANDBETWEEN, and RANDARRAY) are volatile.

Volatile functions recalculate every time a change (to the contents or structure of a sheet) is made in a Google Sheet.

Examples of 'a change' include:

• Contents: data being added/removed/amended, or
• Structure: columns/rows being added/removed

Formatting changes (like text or cell color) do not trigger recalculation of volatile functions.

The volatility of these functions can cause changes that you don't want (which is really annoying):

And/or slow down your sheet considerably:

If these things are happening to you, you'll want to freeze the random numbers to keep them from changing in your Google Sheet.

To make this happen you need to remove the formulas. Here's how:

STEP 1: Select the cells you want to stop from changing.

STEP 2: Copy the selection you've made. You can use Ctrl+C (Windows) or ⌘+C (Mac).

STEP 3: Paste the selection as values where it is now. You can use Ctrl+Shift+V (Windows) or ⌘+Shift+V (Mac).

Now you have the required data without the formulas.

No formulas means no volatile functions and nothing to recalculate.

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Get The Cheat Sheet ➜

You'll get updates from me with an easy-to-find "unsubscribe" link.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.

You might like:

MIN Google Sheets Function [With Quiz]

The MIN function in Google Sheets finds the minimum! It doesn't matter how many numbers, MIN will quickly find the smallest / most negative.

How To Round In Google Sheets

Need to round up and down in Google Sheets? Find out how to round using both formulas and formatting on both desktop and mobile.

How To Highlight Duplicates In Google Sheets

Do you need to find and highlight duplicates in Google Sheets using conditional formatting? The custom formulas you need are right here.