# How To Generate Random Numbers In Google Sheets

**Updated:**July 26, 2021

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

Random number between 0 and 1:

Random whole number between low and high:

Random decimal number between low and high:

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

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

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:

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:

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:

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

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:

- Generate more than 5 random numbers (using the RANDARRAY function)
- Get the unique numbers (using the UNIQUE function)
- 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):

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

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:

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

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

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

*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

➜ 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

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

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