Sorting data is usually a key component of any data analysis. It can help you organize your data, spot trends, or find the minimum and maximum values. However, there might be instances where you need to unsort your data and remove any previous ordering! Randomizing the order of a list can be useful if you need to randomly select a sample in your data. Get your copy of the example workbook used in this post and follow along!
Randomize a List with a Helper Column
The most straightforward way to randomize your list is by using a helper column. Excel has many ways to generate random numbers. You can use these to create a new column of random numbers and then sort the entire range based on the random numbers. Because the numbers are random, sorting based on that column will randomize your data! Here is a small example dataset that’s been sorted by the Sales value. How can you randomize the sort order of this list? Follow these steps to randomly sort your data using a helper column. This creates a column of random numbers between 0 and 1. You will be able to see these random numbers refresh if you press the F9 key. Now you will need to apply sort and filter toggles to your data. This will add a small sort and filter toggle to each column heading in your data. These allow you to sort or filter the data by each of the columns. Since you are sorting a column of random numbers, it won’t matter if it’s sorted in ascending or descending order. The other data will be randomized either way! This results in your dataset getting sorted in a randomized way! Notice the Order column isn’t even in order? This is because after it’s sorted, the values recalculate and are therefore randomized again. This means you can sort the column again to obtain another shuffling of your data.
Randomize a List with the SORTBY and RANDARRAY Function
This method will use the same basic idea as before to reorder your data in a random way. But this will use a function to sort the data! The SORTBY function allows you to sort any range based on the values in one or more columns. The cool part is, that these columns don’t even need to be a part of the range you’re sorting. This means you can create a virtual column of random numbers to sort the data. The RANDARRAY function will allow you to do just that! The above formula will randomize your data range. This single formula will produce the entire randomized dataset. Any time the workbook recalculates, the ordering will change too. The ROWS function will determine the number of rows in your data. This is then used in the RANDARRAY function to produce a single column of random values between 0 and 1 with the same number of rows as your data. The SORTBY function then sorts your data in ascending order based on the RANDARRAY results.
Randomize a List with Power Query
Power Query is a very powerful data transformation tool in Excel and it has many sorting options available. You can do something very similar to the previous methods and create a column of random values and sort the data by them. Follow these steps to create a Power Query that will randomly sort your data. If your data is not already inside a table, Power Query will prompt you to create a table. Make sure to check the My table has headers option in the Create Table menu and then press the OK button. This will open the Power Query Editor and you can now add a new column of random values that will be used to sort the data. This is going to open up the Custom Column menu where you can add a formula for the new column. 📝 Note: You need to use the List.Random(1) function instead of Number.Random() because the latter will only produce the same random number across the entire rows. This creates a list in each row. Each list contains a single random number from 0 to 1, but you will need to extract these values before you can use them to sort the data. This results in an Order column filled with different random numbers and it can now be sorted. Now that the data is sorted based on the random column, it’s no longer needed and you can delete it. Now the query can be loaded into Excel. This is going to open the Import Data menu and you can load the data into a table. That’s it! Your randomized data is now loaded into Excel. Any time you want to reshuffle the data, all you need to do is click a button. Go to the Data tab and click on the Refresh All command found in the Queries & Connections section.
Conclusions
In Excel, there is no specific feature to randomly sort your data. Nevertheless, it’s still possible to get the job done. For a one-time randomizing task, adding a helper column with the RAND function is the easiest way. If you want to repeat the task of randomizing your list, then setting up a SORTBY and RANDARRAY formula or using Power Query will be the way to go. These will both keep the source data unchanged while creating a randomized copy of your list. Do you have any need to randomly arrange your data in Excel? Do you have any other methods to get it done? Let me know in the comments below!
