How to Randomize a List in Google Sheets


This is not frequently used but sometimes you may need to randomly select or pick out something from your data. And this article will show you how to do this.

How to Randomize range

  1. Select the range of data you want to randomize.
  2. Now right click and from the pop-up select randomize range.
Randomize-Range-2

3. Now you list will be randomly sorted or the data will get shuffled.

How to pick a random from the list

If you desire to pick any random from your list, google sheet provides you with few functions for this purpose and they are-

RANDOMBETWEEN

Syntax – =RANDOMBETWEEN(low, high) here, low, smallest number from where you want to randomly pick. high, is the largest number from where you want to randomly pick.

For example, if you want to pick any random number from 1 to 50 then use the formula- =RANDOMBETWEEN(1, 50) and it will pick any random number between 1 to 50 for you.

INDEX

It will return the content of a certain cell. Syntax for the same is – =INDEX(reference,[row], [column]) here, reference, represents the selected range of cell row, represents index of the row that you want to be returned column, represents the index of the column that you want o return

If both RANDOMBETWEEN and INDEX functions will be combined, it will let you randomly choose a cell.

COUNTA

It helps to return number of values from a selected data. The syntax for the function is –

=COUNTA(value1, [value2,…]) 
value1 – The number or range to be counted.
value2 – Additional values or ranges to count. This parameter is not required.

This function will count how many cells in our range have a value in them. We’ll utilize it in conjunction with RANDBETWEEN to determine the highest number from which to choose at random.

How to randomly pick a name from the list

Let’s take an example to implement all the three abovementioned functions to know the results better.

  • The initial step is to choose the cell in which the random choice should appear.
pick random-1
  • After that, we’ll use our first function. Press the Tab key on your keyboard and type “INDEX.” Then choose the range from which you want to choose at random. Type a comma after you’ve entered your range (,)
  • Then, on your keyboard, type “RANDBETWEEN” and click Tab. Press the comma button after adding a one as the first number (,)
  • After that, we’ll add our COUNTA formula. Press Tab after typing “COUNTA.” Set the COUNTA function’s range to count the complete range of data we’re picking at random (the same range as in step 2). After you’ve entered your range, add three closing parenthesis and press ENTER.
pick random-4
  • Your formula has now calculated and chosen one name at random from the list.
pick random-5
  • The Formula I used here in this example is =INDEX(A2:A,RANDBETWEEN(1,COUNTA(A2:A)) The INDEX function is used in this calculation, and the parameters of the function are specified to choose a random integer between 1 and the count of your cell range. So the formula is set up so that it randomly chooses one cell in your data range.

There are other ways to randomize data in Google Sheets, as with anything else. We’ve showed how to use the randomize range option to quickly randomize a list, as well as how to use a function to pick one item from a list at random.

Harshita Mathur

My name is Harshita Mathur. I come from Jaipur, Rajasthan. I am a law student. Apart from this I am an amateur writer.

Recent Content