Google Sheets Cheat Sheet

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

AB
1Refresh
2Formula=RAND()
3Output0.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):

AB
1Refresh
2Formula=RANDBETWEEN(1,100)
3Output11

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

AB
1Refresh
2Formula=RAND()*(100-1)+1
3Output12.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):

AB
1Refresh
2Formula=ArrayFormula( ARRAY_CONSTRAIN( UNIQUE( RANDARRAY( 5*10 )), 5, 1))
3Output0.873311
40.416033
50.430275
60.925857
70.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):

AB
1Refresh
2Formula=ArrayFormula( ARRAY_CONSTRAIN( UNIQUE( TRUNC( RANDARRAY( 5*10 )*( 100-1 )+ 1 )), 5, 1))
3Output13
485
570
68
794

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

AB
1Refresh
2Formula=ArrayFormula( ARRAY_CONSTRAIN( UNIQUE( RANDARRAY( 5*10 )*( 100-1 )+ 1 ), 5, 1))
3Output83.488113
484.070763
567.677849
657.812524
733.879656

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

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:

AB
1Refresh
2Formula=RANDBETWEEN(1,100)
3Output0.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:

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

shows how volatile functions that generate random numbers recalculate whenever a change is made in google sheets

And/or slow down your sheet considerably:

shows the progress bar that appears when formulas take a long time to calculate in google sheets

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

shows how to stop random numbers from recalculating in google sheets by copying and pasting as values

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

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

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.