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 |
Simple range of data
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 |
On an adjacent column insert the 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:
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:
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 |
Insert the random range wherever needed.
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:
Summary
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)).