When working in spreadsheets there may be times when you want to randomize data.
For example, maybe you have a list of names and you want to randomly select one name from your list. With little know-how, this is completely possible in Google Sheets.
In this tutorial, I will show you how to randomize a list in Google Sheets.
Table of Contents
How to Use Randomize Range
There are a few different ways to randomize data in Google Sheets. One quick option is using the built-in random range option.
In this example, I will show you how to use randomize range to randomly sort a list of names.
Here are the steps:
1. The first step is to highlight the range of data that you want to randomize
2. Next, right-click and select Randomize range
3. Your list will now be randomly sorted
This is a quick way to randomly sort a list, but there are other ways to do this as well. If you want to randomly choose one value from a list, we will cover that below
Functions to Pick a Random from a List
If you want to randomly pick from a list, there are a few functions in Google Sheets that will come in handy for this task. We will cover each of these functions and what they do here.
RANDBETWEEN
The RANDBETWEEN function returns a random integer between two values.
The syntax of the function is:
=RANDBETWEEN(low, high)
- low – the smallest number you want to randomly pick from
- high – the largest number you want to randomly pick from
RANDBETWEEN is a pretty simple function to use. You just put in the range that you want to randomly pick from. For example =RANDBETWEEN(1, 10) will pick a random number between 1 and 10. The low and high parameters are inclusive, meaning they can choose the numbers you have used, and any numbers in between.
INDEX
The INDEX function in Google Sheets returns the content of a specific cell. This is done by specifying a row and column in the formula.
The syntax of this function is:
=INDEX(reference, [row], [column])
- reference – This is the range of cells that you are using in your function
- row – This is the index of the row that you want to be returned, this parameter is optional and will be 0 by default
- column – This is the index of the column that you want returned, this parameter is optional and will be 0 by default
By combining RANDBETWEEN and INDEX we can randomly choose a specific cell, we just need to use one last function to get the input values for the RANDBETWEEN function
COUNTA
COUNTA is the last function we will be using. This function returns the number of values in a set of data.
The syntax of the function is:
=COUNTA(value1, [value2, …])
- value1 – The value or range that you want to count
- value2 – Other values or ranges you want to count. This parameter is optional
This function will count the number of cells in our range that contain a value. We will use it in combination with RANDBETWEEN to figure out the high number that we want to randomly choose from
How to Pick a Random Name From a List
Now that we have given you an overview of the functions we will be using, let’s combine them all to show you how to pick a random name from a list.
Here are the steps:
1. The first step is to select the cell where you want the random pick to appear
2. Then, we need to use our first function. Type “INDEX” and press the Tab button on your keyboard. Then select the range that you want to randomly choose from. After putting in your range, type a comma (,)
3. Next type “RANDBETWEEN” and press Tab on your keyboard. Add a one as the first number, and then press the comma button (,)
4. Next we need to add our COUNTA formula. Type “COUNTA” and then press Tab. Set the range for the COUNTA function to count the entire range of data we are randomly choosing from (the same range as in step 2). Add three closing parenthesis after you put in your range, and press Enter on your keyboard
5. Your formula will now have calculated and selected one random name from the list
The full formula that I have used in this is:
=INDEX(A2:A,RANDBETWEEN(1,COUNTA(A2:A)))
The reason this formula works is that you are using the INDEX function, with the parameters of the function set to choose a random number 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.
Closing Thoughts
As with anything in Google Sheets, there is more than one way to randomize data. Here we have shown how to quickly randomize a list with the randomize range option, and we have shown using a function to randomly pick one value from a list.
Make sure to follow along with the example carefully and understand how it works. Then you will be ready to implement it into your own spreadsheets!
More Google Sheets Tutorials:
How to Create a Dependent Drop Down List
How to Create Dynamic Named Ranges
How to Use Data Validation
How to Use RANDARRAY