How can you randomise a range in Google Sheets without needing a plugin or Google App Script?
To randomise a range, simply use the
RAND()
formula in a column to set the random numbers for each element in your range, and then with the
INDEX()
and
RANK()
formulas combined, produce the new random range.
Here’s an example of what this looks like in a Google Sheet spreadsheet starting with the range you’d like to randomise.
For the sake of this first example I’m using a simple range with one column of data:
| A | |
|---|---|
| 1 | Apple |
| 2 | Carrot |
| 3 | Banana |
| 4 | Tomato |
Step 1:
RAND()
For Each Cell
The first step is to generate a random number for each cell in the range. Google Sheets has a function called
RAND()
, which generates a random number between
0
and
1
.
Place this random range alongside your range, which would make my working example above look something like this:
| A | B | |
|---|---|---|
| 1 | Apple |
=RAND()
0.4929732072 |
| 2 | Carrot |
=RAND()
0.239103801 |
| 3 | Banana |
=RAND()
0.0898711001 |
| 4 | Tomato |
=RAND()
0.9137103201 |
RAND()
formula for each cell
You could further add a conditional
IF()
formula if you wanted to check there was a value in the cell, for example, something like this:
=IF(LEN(A1),RAND(),)
This formula simply means that if there is a value in the cell
A1
range to generate a random number using the
RAND()
function, otherwise do nothing. This formula would then be copied down to the remaining cells beneath.
Step 2 – Use
INDEX()
And
RANK()
To Generate Random Range
The final step is to create your random range, which is achieved using the
INDEX()
and
RANK()
formulas together.
The combined formula is as follows:
=INDEX(original_range,RANK(random_cell_value, random_range))Here is how this formula would look in the worked example:
| A | B | C | |
|---|---|---|---|
| 1 | Apple | 0.4929732072 |
=INDEX($A$1:$A$4,RANK(B1,$B$1:$B$4))
Tomato |
| 2 | Carrot | 0.239103801 |
=INDEX($A$1:$A$4,RANK(B2,$B$1:$B$4))
Apple |
| 3 | Banana | 0.0898711001 |
=INDEX($A$1:$A$4,RANK(B3,$B$1:$B$4))
Carrot |
| 4 | Tomato | 0.9137103201 |
=INDEX($A$1:$A$4,RANK(B4,$B$1:$B$4))
Banana |
As you can see from the final output in column
C
the new range produces a randomised range of the original.
You can explore a copy of this spreadsheet here with the public Google Sheet here:
Randomise Range: Google Sheets
To randomise a range in Google Sheets without using a plugin or scripting something up with Google App Script simply create a
RAND()
range for every cell and then create your random range with
INDEX(original_range,RANK(random_cell_value, random_range))
.
