Do you need to randomize your data? This post is going to show you all the ways you can randomly sort a list in Excel.

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.

1. Create a new column to the right of the dataset named Order.

= RAND ( )

2. Add the above formula to the first row of the Order column.
3. Copy and paste the formula to the last row in your data.

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.

4. Select your entire data set. You can select any cell inside your data and press Crtl + A to select all the data.
5. Go to the Data tab.
6. Click on the Filter command in the Sort & Filter section of the ribbon.

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.

7. Left click on the Order column sort and filter toggle.
8. Choose either the Sort Smallest to Largest or Sort Largest to Smallest option.

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!

=SORTBY(B3:D12,RANDARRAY(ROWS(B3:D12)))

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.

1. Select any cell inside your data.
2. Go to the Data tab.
3. Click on the From Table/Range command.

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.

4. Go to the Add Column tab.
5. Click on the Custom Column command.

This is going to open up the Custom Column menu where you can add a formula for the new column.

5. Add a name such as Order in the New column name input.

= List.Random(1)

6. Add the above formula to the Custom column formula editor.
7. Press the OK button.

📝 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.

8. Click on the Filter toggle in the Order column which contains all the List items.
9. Choose the Extract Values option.

This results in an Order column filled with different random numbers and it can now be sorted.

10. Click on the Filter toggle in the Order column.
11. Choose either the Sort Ascending or Sort Descending option.

Now that the data is sorted based on the random column, it’s no longer needed and you can delete it.

12. Right-click on the Order column heading containing the random numbers.
13. Select the Remove option from the menu.

Now the query can be loaded into Excel.

14. Go to the Home tab of the Power Query editor.
15. Click on the Close & Load button.

This is going to open the Import Data menu and you can load the data into a table.

16. Select the Table option.
17. Choose to load the table in an Existing worksheet or a New worksheet.
18. Press the OK button.

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!