How can you randomise a range in Google Sheets without needing a plugin or Google App Script?
To randomise a range, simply use the
formula in a column to set the random numbers for each element in your range, and then with the
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:
For Each Cell
The first step is to generate a random number for each cell in the range. Google Sheets has a function called
, which generates a random number between
Place this random range alongside your range, which would make my working example above look something like this:
RAND()formula for each cell
You could further add a conditional
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
range to generate a random number using the
function, otherwise do nothing. This formula would then be copied down to the remaining cells beneath.
Step 2 – Use
To Generate Random Range
The final step is to create your random range, which is achieved using the
The combined formula is as follows:
Here is how this formula would look in the worked example:
As you can see from the final output in column
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
range for every cell and then create your random range with