This post will show you all the ways you can use to randomize the sort order of your data in Google Sheets.

Sorting data helps to organize or arrange a dataset in a specific order. In Google Sheets, you can sort text and numeric values in ascending or descending order.

Sorting is a quick and easy way to bring certain aspects of your data to view.

However, you might want to undo any sorting that has been performed on your dataset. For this, you can randomize the order to remove any sorting that has been performed.

There are many different ways you can randomly sort data in Google Sheets. This post will take you through six different ways you can sort your data randomly.

1. Random sort with the Data menu.
2. Random sort with a helper column.
3. Random sort with an Add-On.
4. Random sort with the SORT function.
5. Random sort with the QUERY function.
6. Randomize the order of data columns.

Download your copy of the example workbook from the link above and follow along!

Random Sort Order from the Data Menu

Google Sheets have a nifty feature that helps you to sort data randomly known as Randomise range.

You can access the Randomise range feature from the right-click menu after selecting the cells you want to sort randomly or from the Data menu tab.

In this data is a list of students and their professors. The professors are to be assigned to the students as supervisors in random order.

Follow these steps to use the Randomize range feature.

1. Select all the cells which you want to randomly arrange.
2. Click on Data in the menu.
3. Click on the Randomise range option.

Now the cells in the selected range are sorted randomly, while any unselected cells haven’t been re-ordered.

Follow these steps to access Randomise range from the right-click menu.

1. Select the range to randomize.
2. Right-click inside the selected cell range.
3. Select View more cell actions from the options.
4. Select the Randomise range option from the submenu.

Randomize range is a quick and easy tool for sorting items randomly.

You can randomly sort data in multiple columns as well. In this case, the entire record for a given row is sorted.

The Randomize range command will perform a sorting action on all selected columns, and it will only rearrange the position of each record.

1. Here is the original data.
2. Here is the data randomly sorted. If you look closely at both results, you will see that each student still has the same professor only their position in the list has been altered.

Random Sort Order with a Helper Column

This method will require an added third column in the dataset.

This column is called the helper column because it will help with the random sorting of all the columns in the dataset, without the restriction of the Randomise range feature.

The values in the helper column will be created using the RAND function. The RAND function generates random numbers between 0 and 1, and the generated numbers change every time the spreadsheet refreshes.

This characteristic is what makes the RAND function perfect for creating the values in the helper column.

= RAND ()

The RAND function takes no argument, which is why you get the preview at the top as soon as you start to type in the function.

Create a new column called Helper and you can give it a different background color to signify that it’s not a part of the original data.

In the cell beneath the header, type the =RAND() function syntax to create a random number and drag down the fill handle at the bottom right-hand corner of the cell.

You will notice that the number in the first cell has changed as the other empty cells are populated. There are times when this will be an inconvenience, but for now, it’s exactly what we need.

By using the helper column to sort the data, the other columns get sorted into a random order. This is because the values in the helper column change every time the spreadsheet refreshes, forcing the other columns to sort randomly irrespective of the sort order.

There are many ways you can generate random numbers in Google Sheets. You will get the same result of random sorting irrespective of which method is used to generate your random numbers.

Follow these steps to randomly sort the rows.

1. Select all the columns with the values to be randomly sorted, including the helper column. But avoid selecting the column headers.

2. Go to the Data menu.
3. Select the Sort sheet option.
4. Select either Sort sheet by column C (A to Z) or Sort sheet by column C (Z to A). A to Z means in ascending order, while Z to A means in descending order.

If you use the ascending sort command Sort sheet by column C (A to Z), the column headers will be placed on the last row even without including them in your selection.

This occurs as a result of two things.

The Sort sheet command sorts everything in the entire sheet, selected or unselected. The selection doesn’t have any effect if you use the Sort sheet command. That’s why the column headers are included in this sort.

Because the sheet is sorted in ascending order the text column headers, are regarded as the largest values in the column. That is why they are at the bottom of the list.

Sorting the sheet in descending order will keep the column headers at the top irrespective of how many times you carry out the sort.

When using the Sort command, you will see that Column C was automatically identified as the sort column. Column C happens to be the helper column.

Will the spreadsheet automatically identify the column you want to use to sort your data? No.

The reason why the command picked up on Column C is that a cell in Column C was selected.

When a cell in Column A is selected, the sort command picks up Column A as the column to sort by.

If you don’t want to sort the whole sheet, use the Sort range command.

With the Sort range command, cell selection is important. You definitely want to avoid selecting column headers.

The two selected columns are now randomly sorted with the Helper column.

The Advanced range sorting options from the Sort range command provide you with more range sorting options.

You have to select the range you want to sort. The selected range will appear at the top of the dialogue box as indicated in the image.

The other features of the Advanced range sorting options are detailed below.

1. Data has header rows – with this option, you don’t have to worry about selecting column headers. If your selection includes column headers, you can check the Data has header row box to indicate this. When you check the box, the first row in the range is automatically selected as the column header and will remain unsorted.

The Sort by option now displays the column headers, instead of the column index.

2. Sort by is where you select the column with which you want to sort the data, and also the order whether descending or ascending.
3. Add another sort column will allow you to sort your data based on multiple columns. In this example, you only need to sort based on the helper column.

Click on Sort when you’re done setting up your preferences.

You can also access the Sort range command by right-clicking on the selected ranges that you want to sort. When you’re done sorting your data, make sure you delete the Helper column.

One thing you should note when using Sort ranges is you cannot use this to sort non-adjacent ranges.

The Sort range option is greyed out, and it can’t be used. Although the Sort sheet command will still work, this won’t serve the purpose of sorting only the specific ranges.

To sort non-adjacent ranges, you will need more sophisticated sorting techniques, which we will be discussed after the next method.

Random Sort Order with an Add-On

Add-ons are custom-built applications or extensions that give your spreadsheet added functionality.

They can help you manage and automate most repetitive tasks.

You can access Add-ons from the Extensions menu. Click on Add-ons and then Get add-ons.

Add-ons are housed in the Google Workspace Marketplace, so clicking on Get add-ons takes you there directly.

Use the search bar to look for Ablebits Power Tools in the Google Workspace Marketplace.

Click on Install to add the add-on to your spreadsheet.

When you click on Install, you may be asked to provide permission with your google account.

As soon as you grant permission with your google account, you will get the above notification that it has been installed. Click on DONE.

In your spreadsheet, access the Power Tools add-on in the Extensions tab.

When you click on the Start option, the Power Tools menu will open on the right of the spreadsheet. The Power Tools add-on can perform a host of simple but repetitive operations with just a click.

To randomly sort a range, you will use the Randomize option. As usual, you have to select the range you want to sort randomly.

After you click on Randomize, click on Shuffle.

Use the Cells in each columns option to randomly shuffle the range.

It will take a few seconds after which your data will be sorted randomly.

With the Power tools add-on, you have a one-click solution to solving your random range sorting problem. But you still can’t shuffle non-adjacent cells.

Random Sort Order with the SORT and RANDARRAY Functions

Next, we look at how you can use a combination of functions to randomly sort data of any arrangement.

The first function is the SORT function. This function is specifically designed for sorting data in ascending and descending order.

By combining it with the RANDARRAY function, you can use it to sort data randomly.

SORT Function

= SORT ( range, sort_column, is_ascending, [sort_column2, …], [is_ascending, …])

The SORT function has 3 required arguments.

• range is the dataset that you want to sort.
• sort_column is the column you want to sort the range by. You can use an index number for the sort column. Assume your range is A2:E10 and you want to sort by Column C, you will enter 3 in the sort_column argument.
• Is_ascending will signify the sort order type. It’s a Boolean argument where 1 or TRUE will result in an ascending order and 2 or FALSE will result in descending order.

If you want to use a range outside of your dataset to sort the data, you must make sure they both have the same number of rows. Suppose you want to sort A2:A10 by another range, the sort_column must also be of the same length.

These are the compulsory arguments in the SORT function. The optional arguments can be useful for sorting data based on multiple columns.

So sort_column2 and is_ascending2 will define an additional sort column and order.

RANDARRAY Function

The other function needed is RANDARRAY.

It returns an array of random numbers between 0 to 1. The size of the array can be defined using the rows and columns argument in the function.

= RANDARRAY ( [rows], [columns] )

• rows is the number of rows to return.
• columns is the number of columns to return.

RANDARRY is very similar to the RAND function, but with an added functionality to produce random numbers within a specified set of rows and columns.

Assume you want to generate random numbers for 10 rows and 4 columns. The RANDARRAY syntax will be =RANDARRAY(10, 4). RANDARRAY helps you to easily generate random numbers across any size range.

If you omit the rows and columns argument, the function will work just like the RAND function. It will return a randomly generated number into a single cell.

= SORT ( A2:B6, RANDARRAY ( 5, 1 ), 1 )

Select an empty cell and copy and paste the above formula to sort the sample data randomly.

The syntax will ensure that the data is sorted based on the array of random numbers generated by the RANDARRAY function.

= SORT ( B2:B6, RANDARRAY ( 5, 1 ), 1 )

= SORT ( A2:A6, RANDARRAY ( 5, 1 ), 1 )

You can use the above formula if you want to sort individual columns.

Random Sort Order with the QUERY Function

Like the SORT function, QUERY can also be used to sort items in ascending or descending order.

By introducing a column made up of randomly generated numbers, we can sort our columns in random order.

= QUERY ( data, query, [headers] )

• data is the range containing the data you want to query. In this case, these will be the cells you want to sort.
• query is the computation you want to carry out on data entered using an SQL syntax or statement.
• Headers is an optional input that can be used to specify the number of rows containing column headers in data. If left blank, the query will the column headers.

To sort the data, you will need a new column containing randomly generated values using the RAND or RANDARRAY function.

Use =RAND() and drag down the fill handle to populate the remaining cells.

=RANDARRAY(5,1)

If you want to use the RANDARRAY function, use the above formul.

= QUERY ( A1:C6, "select * order by C", 1 )

Copy and paste the above formula into an empty cell to sort the data.

= QUERY ( A1:C6, "select A order by C", 1 )

= QUERY ( A1:C6, "select B order by C", 1 )

This syntax will sort all the columns in the data. To sort individual columns, use the above formulas.

When you’re done sorting the data, ensure to delete the Random Number column.

Random Sort of Column Order

For this last and final method, we will wrap the SORT function in a TRANSPOSE.

=TRANSPOSE(array_or_range)

The TRANSPOSE function changes the position of row and column values. Rows are switched to columns and columns are switched into rows.

= TRANSPOSE ( SORT ( TRANSPOSE ( A1:E6 ), RANDARRAY ( 5, 1 ), true ) )

So far it has been the row items that have been randomized. But by combining the TRANSPOSE, SORT, and RANDARRAY functions in this way, you’re able to sort the column order randomly.

Conclusions

We have covered various methods by which you can sort your data randomly.

Built-in menu features like the Randomise range will suffice for your random sorting tasks most of the time. You may call on the Sort feature, or use the Add-on if you need a bit more sorting functionality.

The functions will provide the most dynamic and efficient random sorting capabilities. You can use these to avoid using any additional help columns.

Have you used any of these methods? Do you have any tricks for randomly sorting data? Let me know down in the comments section!