There are two different approaches in randomizing rows in Google Sheets. One is formula based and the other one is using a Google Sheets Menu option. Is any plugin required? Nope! Let’s see how to Shuffle Rows in Google Sheets with the above said two methods.
Before going to choose the method, let me explain to you the differences first. Then I’ll elaborate on how to Shuffle Rows in Google Sheets in different ways.
Formula Based Row Shuffling in Google Sheets
If you plan to go ahead with the formula based option, you should understand one thing. You can shuffle the data only in a new range and I’m using the RANDBETWEEN function in an Array form for this. It’s a volatile function. So whenever you make any changes in any cell in your sheet, the shuffling reoccur.
Pros:
We are not making any changes to our original data. The shuffled data is in a new range.
Cons:
The reshuffling of rows each time Google Sheet Recalculates, may not be ideal.
Menu Based Row Shuffling in Google Sheets
You can Shuffle Rows in Google Sheets using one of the Menu options. You just need to select the rows or data range and click that menu option. I’ll tell you how to do it later.
Pros:
No changes to the shuffled rows as there is no volatile function in use.
Cons:
It changes your original data if you apply it on the same range.
Now you can learn here how to rearrange rows in Google Sheets in random order.
How to Shuffle Rows in Google Sheets Without Plugin
Sample Data:
As I’ve mentioned above, there is no need to use any plugin to shuffle data range in Google Sheets. Here is that two options.
Menu Based Shuffling of Rows in Google Sheets:
Steps:
Select the data range A2: C11. Yes! No need to select the entire rows.
Go to the menu DATA and click Randomise range. It would instantly shuffle the data. Now if you are not satisfied with the shuffling, you can apply the same again.
Note: If there is any formula in the range, you may see an unwanted result in the shuffling. So make sure that the rows that you are shuffling do not contain any formulas.
Formula Based Shuffling of Rows in Google Sheets:
For the above data range, you can use the below formula for shuffling of rows.
=sort(A2:C11,ArrayFormula(randbetween(row(A2:C11)^0,10)),TRUE)
In this A2: C11 is your data range to shuffle and 10 is the total number of rows in your data range. Not sure about the total number of Rows? Just replace the number 10 with the below formula.
rows(A2:A11)
Please note that the above (main) formula output is volatile.
Formula Explanation:
The above formula to Shuffle Rows in Google Sheets what actually doing is Sorting the original data based on a virtual column that contain random numbers.
SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])
In this, the range is our original data range, i.e. A2: C11. As I’ve just mentioned Sort_Column is a virtual column with the Random number generated by the RANDBETWEEN and ROW function combination. It’s as below.
When you sort your data based on this randomly generated column, the data get shuffled. That’s all.
If you want any further detail about any of the formulas used above, please use the comment form below.
Similar Topics:
1. How to Pick a Random Name from a Long List in Google Sheets
2. The frequency of Recalculation of Google Sheets NOW, TODAY, RAND, RANDBETWEEN Functions
