To Randomise Range in Google Sheets
- Go to Data.
- Randomise range.
- Easily shuffle selected data into a random order.
OR
- Create a new column.
- Use the RANDBETWEEN function to generate random numbers.
- Sort the original data based on these random numbers.
- Delete the helping column once the data is randomized.
OR
- Create a custom formula that utilizes the SORT, ArrayFormula, and RANDBETWEEN functions.
- This formula generates a random order without changing the original data.
- The formula can be saved as a named function for easy reusability.
Hi everyone. In this article, we are going to learn how to randomise a range in google sheets.
What is Randomise Range in Google Sheets?
Randomizing a range is the same as shuffling the data items or range items in random order. We often organize our data in ascending order or descending order, but when we want to just randomize it with no defined order, so it’s called randomizing a range. We need this feature for generating a random order, we may use randomize range feature when selecting a random number of items from a list. It can be used for showing or displaying random data on a chart, or there can be many other reasons for it. So today we will see the methods that can be used to randomize a range in google sheets.
Importance of Randomise Range in Google Sheets
When working with sheets, we use so many formulas and functions, sometimes we need to have a random range of numbers or items to see the sorting effect on it. The random order gives a new combination after every shuffle which can be useful for picking up random items from the list every time. There can be many other use cases for using a randomized range. We need to learn how to randomize a range to make a random selection of the data and get a new order every time we shuffle the list.
This is very useful when we are performing some sorting tasks and want to see the effect of the function on a differently randomized range every time. I personally use this method. A randomized range can also be used for making sample data or dummy data for your practice work.
How to Randomise Range in Google Sheets
From this section, we will see each method for randomizing a range. We commonly have three methods which are very easy and can be very useful. The best way to learn it is to practice each method on dummy data which helps you remember the limitations and behavior of each method on dummy data.
For this article, I have simple data, it’s a simple range of items. We will use this for the implementation of the methods.
Randomise Range in Google Sheets – Using Built-in Feature
In this section, we will learn the easiest method to randomize a range in google sheets, it’s a built-in feature, which is a randomized range. It is recently added to google sheets, and it simply shuffles the selected data into a random order.
So, let’s do this.
Step 1
Select the range(s) you want to randomize
Step 2
Go to Data > Randomise range
Or
Step 2b
Right-click on the selected range, view more cell actions > Randomise range
Step 3
Clicking on the button will randomize your range
Step 4
If you did not get a good combination, you can do it again, Randomise range button can be used again and again to make continuous changes to your range order.
This is how simple this is, you can easily randomize your range(s) again and again using this simple and quick method.
Randomise Range in Google Sheets – Using a Helping Column
In this section, we will learn how to randomize a range in google sheets using a helping temporary column with the RANDBETWEEN function in it. Have you used the RANDBETWEEN function before? A RANDBETWEEN function is used to generate random numbers between two values, which means you can generate random numbers between 1 and 5, between 10 and 20, and in any range. So, what we will do is, we will make a helping column and generate random numbers in this column, and then we will sort our original column with a helping column, so the random numbers in the helping column will sort our original range in a random order, and then we will delete the helping column. This is a very good technique for doing many other things in google sheets.
Let’s see how we do it.
Step 1
Sample original data
Step 2
In the next column, write the randbetween function
Step 3
Pass the starting number of the random range
Step 4
Pass the ending number of the random range
Step 5
Press Enter key and you will get a random number between your selected starting and ending numbers
Step 6
Fill down the formula by double clicking the fill handle
Step 7
The column has all the random numbers
Step 8
Select both columns
Step 9
Go to Data > Sort range > Advanced range sorting options
Step 10
check on “Data has header row” if you have selected the headers
Step 11
Select column B in the “sort by” section dropdown.
Step 12
Select an order A-Z or Z-A
Step 13
Click on Sort
Step 14
Lists are randomized
The list will be randomized, you can repeat these steps again and again to get a random sequence every time. After getting your desired order, you can now delete the helping column
Another thing to note here, is if you noticed while sorting the range the helping column numbers are changing on every edit, so if you don’t want them to be changed every time you sort the range you can copy all the numbers and paste them as value, so the formula will be removed, and there will be no further randomization in the numbers, then the original list will be sorted by their fixed sequence.
Randomise Range in Google Sheets – Using a Custom Formula
If somehow, you did not like the above methods, and you are looking for some easy method, here is another way to randomize a range in google sheets, that is by using a custom formula. The formula used the rand function inside and some more functions to combine to make a random range logically.
Using this formula we will get a copy of our original list in a randomized order, the formula is not mutable, and it does not commit any change to our original data. Let’s see how it is done
Step 1
The original range
Step 2
In the next column write down the custom formula
=SORT(A2:A10,ArrayFormula(RANDBETWEEN(SIGN(ROW(A2:A10)),1000000)),True)
Step 3
The above formula will generate a random order of your provided range each time you refresh the sheet.
Step 4
For better reusability, you can save this custom formula into “My named function”
This is how you can use this function again and again by calling it to form its name. It works like the named range in google sheets. The function will appear just like a normal function when you call it starting with an equal sign.
I hope you have learned all the methods, and now you can easily randomize your ranges using any of the above methods.
Download/Copy Google Sheets Workbook
Important Notes
- The best and most simplistic method is the first method which is the built-in method to randomize the range
- The helping column method is very common in google sheets, we use this technique in sorting, filtering, and with other functions.
- The custom formula method is also a great method to randomize your ranges if you add this formula to the name function and then call it various times without bothering about remembering the formula. You can call it just the built-in function in google sheets.
- The RANDBETWEEN function is a very useful function that can generate any numbers between two defined starting and endpoints
Frequently Asked Questions
How to quickly randomize a range in google sheets?
Randomizing a range in google sheets is very easy and can be done using various methods. In this article we have covered three useful methods to randomize a range in google sheets, you can use any of the above methods as per your preferences. All the methods are easy and legit. The custom formula method is the best when you don’t want to risk your data. It does not make any change to your original data, you can test the random order again and again by refreshing the sheet and you will get a random range every time, after you get your desired range you can replace the range with your original range, and this is why this method is more secure and less risky.
Can Named Ranges be Randomized in Google Sheets?
While it is not possible to directly randomize named ranges in google sheets, you can create a separate column and use a formula to generate random numbers. Then, you can sort the named range based on these numbers, effectively achieving a randomized order. This workaround can be handy in situations where you want to shuffle data within named ranges in Google Sheets.
How to stop a helping column from further randomizing?
It’s a very common question, we often face the problem when we use the rand formula, or RANDBETWEEN formula, we use these formulas to generate random ranges, but once a random range is finalized, users want the ranges not to randomize further, it’s very obvious that we don’t want to randomize the range further after we have found a good combination of the items. A very simple solution to stop a helping column randomize further is to copy the values by the rand formula and paste them as values in the same position. This will remove the function, and the auto-randomization will be stopped.
Conclusion
So today, we learned how to randomise a range in google sheets. We learned what are randomized ranges and why we need them in google sheets. We discussed the methods to change our ranges or list in random order. This also can be said as the shuffling of the data inside a range or list without any order. We saw the first and most used method, it’s a built-in method named “Randomise range” which can be found in the Data menu. If not working great for you, then don’t worry we discussed two more methods for doing this. The second method was a helping column method, which is a common technique, and then we saw a custom formula method which makes it very easy and reusable if we add this formula as a named function in google sheets.
That’s all from how to randomize a range in google sheets. I will see you soon with another helping tutorial. Keep learning with Office Demy.