30 Advanced Excel Tips for 2026

Excel Champs / Excel Formulas List / Randomize a List (Shuffle Data using Random Sort) in Excel
By Puneet Gogia (Microsoft MVP)

Randomize a List (Shuffle Data using Random Sort) in Excel

30+ Advanced Excel Tips for 2026

No spam. Only Excel Stuff. God Promise. 😊

To shuffle a list, add a helper column with =RAND() beside your data, fill it down, then sort by that column (Data → Sort, or Alt → A → S → S). RAND produces a decimal between 0 and 1 for each row, so sorting on it puts the rows in a random order — ascending or descending makes no difference. Delete the helper column afterwards.

In Excel 365 there’s a one-formula version that needs no helper column or sorting at all: =SORTBY(A2:A11,RANDARRAY(ROWS(A2:A11))) returns the shuffled list in a new range.

Both RAND and RANDARRAY are volatile, so the order reshuffles on every recalculation — with the sort method the shuffle is already baked in, but with SORTBY you’ll want to copy the result and paste as values (Ctrl+Alt+V → V) once you’re happy.

To shuffle columns rather than rows, put the random numbers in a row above the data, select the range, then use Sort → Options → Sort left to right.

To randomize a list of values in Excel means to sort the data in a random order. For this, you need to use Excel’s RAND function, and along with that, you need to use the sort feature that you use to sort data. RAND gives random values that help to shuffle that data randomly.

In the following example, we have a list of names, and now we need to sort this list in a random order.

list-of-names

Steps to Random Sort a List in Excel (Randomize a List)

  1. First, enter =RAND() in cell B1. This will give you a random number between 0 and 1.
  2. Next, you need to drag the formula down to the last name that you have in the list.
    random-sort-a-list
  3. From here, you need to open the sort option from Data ➜ Sort. Or you can use the keyboard shortcut (Alt ➜ A ➜ S ➜ S).
    open-the-sort-option
  4. In “Sort by”, select the name of the column where you have random values and select the order you want (this doesn’t make any difference because we are looking for random sorting).
  5. In the end, click OK.

And once you click OK, you have a randomized list of names (sorted by random order).

rand-function

Now there’s one thing that we need to note down here. The RAND function is a volatile function, and it changes every time Excel recalculates. Either you can change those random values into actual values (copy them and use Paste Special ➜ Values), or if you want to re-sort the names later, you can leave them the way they are and sort again.

Random Sort Columns

You can also sort columns in a random order using the same method. In the following example, we have the same list of names in multiple columns, and now we need to sort the columns in a random order.

random-sort-columns

First, you need to create a row of random numbers (with RAND) below the names, the way we have done in the previous method.

create-random-number-column

After that, you need to select the data range from both rows.

select-data-ranges

Now the next thing that we do is to open the sort option. In the Sort dialog box, click on the “Options” button, select the “Sort left to right” option, and then click OK.

open-the-sort-option

Now, in the Sort dialog box, select the row where you have the random numbers (here, “Row 1”) in “Sort by”, and in the end, click OK.

select-the-row-one

And the moment you click OK, you’ll get the columns sorted in random order.

get-the-sorted-columns

So, this is how you can randomize a list of values in Excel using a random sort method.

Randomize a List with SORTBY and RANDARRAY

In Excel for Microsoft 365 and Excel 2021 and later, you can shuffle a list with a single formula, without a helper column. RANDARRAY creates one random number for each name, and SORTBY sorts the names by those numbers:

=SORTBY(A2:A11,RANDARRAY(ROWS(A2:A11)))

The result spills into the cells below, and your original list stays as it is. Like RAND, RANDARRAY recalculates, so the order changes every time you edit the sheet or press F9. Copy the result and paste it as values to keep an order.

Pick Random Names from a List

Once the list is shuffled, the first few names are a random sample. In Excel for Microsoft 365, wrap the formula in TAKE to get only the first 3 names, with no name picked twice:

=TAKE(SORTBY(A2:A11,RANDARRAY(ROWS(A2:A11))),3)

In older versions, sort the list with the RAND column as shown above and use the first 3 names.

Frequently Asked Questions

How do I shuffle rows with more than one column?

Add the RAND column next to your data, select all the columns together, and sort by the RAND column. Each row moves as a whole, so the values stay together. With SORTBY, refer to all the columns, like =SORTBY(A2:C11,RANDARRAY(ROWS(A2:C11))).

Why do the random numbers change after sorting?

RAND recalculates as soon as the sort is done, so the numbers you see aren’t the ones used to sort. That’s fine: the list is already in a random order. Just don’t sort again unless you want a new order.

Can I randomize a list without a helper column?

Yes, in Excel for Microsoft 365 and Excel 2021 and later, with the SORTBY and RANDARRAY formula shown above. In older versions, you need the RAND helper column.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel

Leave a Comment