How to Randomise Range in Google Sheets [3 Methods]

how to Randomize a Range in Google Sheets 12
Key Takeaways: How to Randomise Range in Google Sheets

To Randomise Range in Google Sheets

  1. Go to Data.
  2. Randomise range.
  3. Easily shuffle selected data into a random order.

OR

  1. Create a new column.
  2. Use the RANDBETWEEN function to generate random numbers.
  3. Sort the original data based on these random numbers.
  4. Delete the helping column once the data is randomized.

OR

  1. Create a custom formula that utilizes the SORT, ArrayFormula, and RANDBETWEEN functions.
  2. This formula generates a random order without changing the original data.
  3. 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

how to Randomize a Range in Google Sheets 1

Step 2

Go to Data > Randomise range

how to Randomize a Range in Google Sheets 2

Or

Step 2b

Right-click on the selected range, view more cell actions > Randomise range

how to Randomize a Range in Google Sheets 3

Step 3

Clicking on the button will randomize your range

how to Randomize a Range in Google Sheets 3

how to Randomize a Range in Google Sheets 4

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.

how to Randomize a Range in Google Sheets 5

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

how to Randomize a Range in Google Sheets 6

Step 2

In the next column, write the randbetween function

how to Randomize a Range in Google Sheets 7

Step 3

Pass the starting number of the random range

how to Randomize a Range in Google Sheets 8

Step 4

Pass the ending number of the random range

how to Randomize a Range in Google Sheets 9

Step 5

Press Enter key and you will get a random number between your selected starting and ending numbers

how to Randomize a Range in Google Sheets 10

Step 6

Fill down the formula by double clicking the fill handle

how to Randomize a Range in Google Sheets 11

Step 7

The column has all the random numbers

how to Randomize a Range in Google Sheets 12

Step 8

Select both columns

how to Randomize a Range in Google Sheets 13

Step 9

Go to Data > Sort range > Advanced range sorting options

how to Randomize a Range in Google Sheets 14

Step 10

check on “Data has header row” if you have selected the headers

how to Randomize a Range in Google Sheets 15

Step 11

Select column B in the “sort by” section dropdown.

how to Randomize a Range in Google Sheets 16

how to Randomize a Range in Google Sheets 17

Step 12

Select an order A-Z or Z-A

how to Randomize a Range in Google Sheets 18

Step 13

Click on Sort

how to Randomize a Range in Google Sheets 19

Step 14

Lists are randomized

how to Randomize a Range in Google Sheets 20

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

how to Randomize a Range in Google Sheets 21

Step 2

In the next column write down the custom formula

how to Randomize a Range in Google Sheets 22

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

how to Randomize a Range in Google Sheets 23

Step 4

For better reusability, you can save this custom formula into “My named function”

how to Randomize a Range in Google Sheets 24

how to Randomize a Range in Google Sheets 25

how to Randomize a Range in Google Sheets 26

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq, I am a highly skilled technical writer working full-time for Office Demy. I am specialized in Google Workspace and Microsoft Office applications. With a background in Software Engineering, I possess a deep understanding of the intricate functionalities and features of these productivity tools. Connect me on Linkedin https://www.linkedin.com/in/shaiq-ansari/

OfficeDemy.com
Logo
Enable registration in settings - general