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.

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

`= RAND ( )`

- Add the above formula to the first row of the
**Order**column. - 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.

- Select your entire data set. You can select any cell inside your data and press Crtl + A to select all the data.
- Go to the
**Data**tab. - 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.

**Left click**on the**Order**column sort and filter toggle.- 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.

- Select any cell inside your data.
- Go to the
**Data**tab. - 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.

- Go to the
**Add Column**tab. - 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.

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

`= List.Random(1)`

- Add the above formula to the
**Custom column formula**editor. - 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.

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

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

- Click on the
**Filter**toggle in the**Order**column. - 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.

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

Now the query can be loaded into Excel.

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

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

- Select the
**Table**option. - Choose to load the table in an
**Existing worksheet**or a**New worksheet**. - 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!

## 0 Comments