# How To Generate Random Numbers In Google Sheets

Here are a few useful formulas for generating random numbers in Google Sheets:

Random number between 0 and 1:

=RAND()

Random whole number between low and high:

=RANDBETWEEN(low,high)

Random decimal number between low and high:

=RAND()*(high-low)+low

Multiple random whole numbers between low and high with no repeats:

=ArrayFormula(ARRAY_CONSTRAIN(UNIQUE(TRUNC(RANDARRAY(unique_nums*10)*(high-low)+low)),unique_nums,1))

Keep reading for more formulas and explanations about how these ones work.

Can you generate random numbers in Google Sheets?

Yes!

Here's how to:

## 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):

=RAND()

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):

 A B 1 Refresh 2 Formula =RAND() 3 Output 0.226817

### Random Numbers Between Any Two Numbers

#### Get Whole Numbers

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

=RANDBETWEEN(low,high)

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):

 A B 1 Refresh 2 Formula =RANDBETWEEN(1,100) 3 Output 11

You can closely replicate the functionality of the RANDBETWEEN function using the RAND function from above:

=TRUNC(RAND()*(high-low)+low)

However, this formula is inclusive of the low and exclusive of the high.

To make it inclusive of both low and high you can use:

=TRUNC(RAND()*((high+1)-low)+low)

#### Get Decimal Numbers

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

=RAND()*(high-low)+low

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

Another option is:

=RANDBETWEEN(high,low-1)+RAND()

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):

 A B 1 Refresh 2 Formula =RAND()*(100-1)+1 3 Output 12.029081

### 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:

=ArrayFormula(ARRAY_CONSTRAIN(UNIQUE(RANDARRAY(unique_nums*10)),unique_nums,1))

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

 A B 1 Refresh 2 Formula =ArrayFormula( ARRAY_CONSTRAIN( UNIQUE( RANDARRAY( 5*10 )), 5, 1)) 3 Output 0.873311 4 0.416033 5 0.430275 6 0.925857 7 0.852605

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

=ArrayFormula(ARRAY_CONSTRAIN(UNIQUE(TRUNC(RANDARRAY(unique_nums*10)*(high-low)+low)),unique_nums,1))

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

 A B 1 Refresh 2 Formula =ArrayFormula( ARRAY_CONSTRAIN( UNIQUE( TRUNC( RANDARRAY( 5*10 )*( 100-1 )+ 1 )), 5, 1)) 3 Output 13 4 85 5 70 6 8 7 94

This formula (and the next one) is built based on the structure of the formula to get decimal numbers between any two numbers from above:

=RAND()*(high-low)+low

This formula is able to accept an array of random numbers (generated by the RANDARRAY function) and output an array because it's wrapped in an ArrayFormula function.

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

=ArrayFormula(ARRAY_CONSTRAIN(UNIQUE(RANDARRAY(unique_nums*10)*(high-low)+low),unique_nums,1))

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

 A B 1 Refresh 2 Formula =ArrayFormula( ARRAY_CONSTRAIN( UNIQUE( RANDARRAY( 5*10 )*( 100-1 )+ 1 ), 5, 1)) 3 Output 83.488113 4 84.070763 5 67.677849 6 57.812524 7 33.879656

FREE RESOURCE

### Google Sheets Cheat Sheet

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

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 InsertCheckbox 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:

 A B 1 Refresh 2 Formula =RANDBETWEEN(1,100) 3 Output 0.2268178817

## 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:

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.

### Want Better-Looking Google Sheets?

Get my 12-tip cheat sheet that will make your spreadsheets more user-friendly.

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

🗙