This post will show you all the ways you can use to randomize the sort order of your data in Google Sheets. Show
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.
Download your copy of the example workbook from the link above and follow along! Random Sort Order from the Data MenuGoogle 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.
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.
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.
Random Sort Order with a Helper ColumnThis 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.
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 3 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.
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.
The Sort by option now displays the column headers, instead of the column index.
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-OnAdd-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 FunctionsNext, 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
The SORT function has 3 required arguments.
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 FunctionThe 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.
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 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.
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.
You can use the above formula if you want to sort individual columns. Random Sort Order with the QUERY FunctionLike 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.
To sort the data, you will need a new column containing randomly generated values using the RAND or RANDARRAY function. Use 3 and drag down the fill handle to populate the remaining cells.
If you want to use the RANDARRAY function, use the above formul.
Copy and paste the above formula into an empty cell to sort the data.
0This 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 OrderFor this last and final method, we will wrap the SORT function in a TRANSPOSE. 1The TRANSPOSE function changes the position of row and column values. Rows are switched to columns and columns are switched into rows. 2So 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. ConclusionsWe 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! Apa saja rumus spreadsheet?Berikut daftar rumus Google Sheets yang paling dibutuhkan:. SUM. Pertama adalah rumus SUM yang berguna untuk melakukan penjumlahan. ... . 2. AVERAGE. Selain penjumlahan, rumus umum lainnya yang pasti Anda butuhkan adalah mencari rata-rata dari sebuah data. ... . 3. COUNT. ... . MAX. ... . MIN. ... . TRIM. ... . PROPER. ... . 3. GOOGLETRANSLATE.. Bagaimana mencari rataPada komputer Anda, buka spreadsheet di Google Spreadsheet. Tandai sel yang ingin dihitung.. Tandai rentang B2:B5.. Di kanan bawah, klik Sum.. Pilih Avg.. Di kanan bawah, Anda akan melihat “Avg: 87%”.. Apa yang dapat diterapkan bagi pemilik Google Sheets?Dengan Google Spreadsheet, Anda dapat membuat dan mengedit spreadsheet secara langsung di browser web—tanpa memerlukan software khusus. Beberapa orang dapat bekerja secara bersamaan, Anda dapat melihat saat orang lain membuat perubahan, dan setiap perubahan disimpan secara otomatis.
Mengapa kita harus menggunakan Gsheet?Google Sheets memiliki fungsi sama dengan Microsoft Excel yang mana dapat digunakan untuk mengolah data, mengurutkan data, serta memanipulasi data dalam bentuk tabel. Dalam perusahaan, Google Sheets dapat digunakan untuk mengolah data seperti laporan penjualan, laporan keuangan, laporan piutang, dan sejenisnya.
|