Randomise Range In Google Sheets In 2 Simple Steps (No Plugins Or Script Needed)

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:

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:

1 Apple =RAND()
2 Carrot =RAND()
3 Banana =RAND()
4 Tomato =RAND()
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:

=INDEX(original_range,RANK(random_cell_value, random_range))

Here is how this formula would look in the worked example:

1 Apple 0.4929732072 =INDEX($A$1:$A$4,RANK(B1,$B$1:$B$4))
2 Carrot 0.239103801 =INDEX($A$1:$A$4,RANK(B2,$B$1:$B$4))
3 Banana 0.0898711001 =INDEX($A$1:$A$4,RANK(B3,$B$1:$B$4))
4 Tomato 0.9137103201 =INDEX($A$1:$A$4,RANK(B4,$B$1:$B$4))
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:

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

Photo of author
Ryan Sheehy
Ryan has been dabbling in code since the late '90s when he cut his teeth exploring VBA in Excel. Having his eyes opened with the potential of automating repetitive tasks, he expanded to Python and then moved over to scripting languages such as HTML, CSS, Javascript and PHP.