Why do we create spreadsheets? Obvious, to organize the mess out, right? But there come few situations where rather than organized rows of data, you need a sheet with randomized order of cell values.
It can be reshuffling the students in your class or reshuffling your staff for office work or it can be shuffling the names of the participants to pick them in an unbiased manner.
So to ease your Excel life a little, we have come up with an easy guide to sort your data in Google sheets using Excel formulas and Google Apps script.
While using Google sheets, you get multiple rows of data and you need to sort the list in a random series.
To understand better, consider these examples; Suppose your Google sheet has names or email addresses of the people who participated in the giveaway and you need to pick any three in an unbiased manner to reward them with the prize. Or your sheet consists of the name of your team members and you have to reshuffle them before you start assigning them tasks randomly.
There are multiple ways to sort rows randomly in Google spreadsheets, and here we are listing 2 of the easiest methods: You can either opt to create a menu-based function that allows you to randomize data on a single click or you can use the built-in SORT function of Google spreadsheets
Tip: Create a Google Sheet to sort rows randomly before trying it on your data in the sheets.
Use your PC to open the Google sheet that has all your data, create a new sheet and paste the below-mentioned formula in A1 cell of the empty sheet.
| =SORT(Customers!A2:D50, RANDARRAY (ROWS(Customers!A2:A50), 1), FALSE) |
The very first defense of the SORT function signifies the range of data that needs to sort in A1 notion. The second-order signifies the creation of a virtual column of the same dimension filled with random numbers and the third argument specifies the sort order in ascending manner.
Also, you may want to replace CUSTOMERS in the formula with the exact name in your sheet. There can be a case that name in the sheet may contain spaces, in this case, enclose your sheet name in single quotes like ‘Employee List’ ! A2 : D50.
Quick Tip: Prefer starting with Row 2 since the first row contains the headers (Titles).
The advantage you get from this approach is that it doesn’t alter the source of data as the randomized order data appears in the new sheet that we created earlier.
If you’re someone who does not really want to go manual and add formulas by yourself, this method is for you. You can take the Apps script route and not have to manually add formulas each time you carry forward sorting of rows randomly in spreadsheets.
Go to the tools menu and choose Script editor as you open your Google sheet. Copy-paste the below-mentioned code in the editor and hit the “Save” button. Once done, reload the Google sheet and you should see a new menu as shown above.
| /** @OnlyCurrentDoc */ // Sort data in random order const sortRowsInRandomOrder = () => { // Get the current sheet that contains the list of data const sheet = SpreadsheetApp.getActiveSheet(); // Get the first non-empty column const column = sheet.getLastColumn() + 1; // Add the RAND() formula to all rows in the new column sheet .getRange(1, column) .setFormula(“=RAND()”) .autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES); // Sort the entire range of data using the random values // Do not include the first row of data (header) for sort sheet.getDataRange().offset(1, 0).sort({ column }); // Remove the temporary column from Google sheet sheet.deleteColumn(column); // Flush the changes SpreadsheetApp.flush(); }; // Add the menu to Google Sheets const onOpen = () => { SpreadsheetApp.getUi() .createMenu(“Randomize Rows”) .addItem(“Start”, “sortRowsInRandomOrder”) .addToUi(); }; |
Go to the randomize Rows and select start to create a new column. This automatically fills RAND( ) formula in the new column for the complete range of cells, sorts the sheet range by this data, and then removes the temporary column.
To shuffle the rows in random order, you can click on the same menu multiple times.
We hope this guide helped you sort rows randomly in Google Spreadsheets without any hassle.
Leave a Reply