How to Randomize a List in Excel With SORTBY

To randomize a list in Excel, sort it by random numbers. In Excel 2021 or Microsoft 365, one formula does it: =SORTBY(A2:A11,RANDARRAY(ROWS(A2:A11))). In older versions, a helper column of RAND values and the Sort button do the same job. This guide covers both, plus how to freeze the order and pick random winners or teams without repeats.

How to randomize a list with SORTBY and RANDARRAY#

With names in A2:A11, click an empty cell such as C2 and type:

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

Press Enter and the ten names spill into C2:C11 in a random order. Spilling means one formula fills several cells by itself. Work through it from the inside out:

1. ROWS(A2:A11) counts the rows in the list: 10.
2. RANDARRAY(10) makes 10 random decimals between 0 and 1, one per row.
3. SORTBY(A2:A11, …) sorts the names by those random numbers, smallest first.

Because the numbers are random, so is the order. Every name appears exactly once, with no repeats and none missing. SORTBY and RANDARRAY need Excel 2021, Excel 2024 or Microsoft 365. In older versions the formula shows #NAME?; use the helper-column method further down. For how SORTBY works in general, see the guide to SORT and SORTBY.

Why the order keeps changing, and how to freeze it#

RAND and RANDARRAY are volatile functions: they make new random numbers every time Excel recalculates. Typing in any cell, opening the file or pressing F9 (Mac: ⌘=) gives a new shuffle. That's handy for a fresh draw, but not for a list you need to keep. To freeze it:

1. Select the shuffled cells, for example C2:C11, and copy them (Ctrl+C; Mac: ⌘C).
2. Open Paste Special (Windows: Ctrl+Alt+V; Mac: Control+⌘+V).
3. Choose Values and click OK.

The formula is replaced by plain names, so the order stays put. If you paste over the formula's own cells, the spill result becomes values in place.

How to shuffle a whole table#

SORTBY sorts every column you give it, so you can shuffle whole rows and keep each row together. With names, departments and emails in A2:C11:

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

The result spills three columns wide, and each person's department and email stay with their name. This is the safe way to shuffle: if you sort one column on its own, the rows get mixed up.

How to pick random names without repeats#

To choose a few random items, shuffle the list and take the top ones. Since each name appears once in the shuffle, no one can be picked twice. In Microsoft 365, TAKE keeps the first rows:

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

This returns three random names. In Excel 2021, which has no TAKE, use INDEX with SEQUENCE:

=INDEX(SORTBY(A2:A11,RANDARRAY(ROWS(A2:A11))),SEQUENCE(3))

SEQUENCE(3) makes the list 1, 2, 3, so INDEX returns the first three rows of the shuffle. The SEQUENCE function guide explains it. For a single random pick, =INDEX(A2:A11,RANDBETWEEN(1,ROWS(A2:A11))) works in every version.

How to randomize a list in older Excel with RAND#

Before dynamic arrays, the method was a helper column and a normal sort. It still works in every version:

1. In B2, type =RAND() and fill it down to B11 (Windows: Ctrl+D; Mac: ⌘D). Each row gets a random decimal.
2. Click any cell in the list.
3. Go to Data > Sort, choose to sort by column B, smallest to largest, and click OK.

The names in column A are now in a random order. RAND recalculates during the sort, so the helper numbers won't look sorted afterwards, but the shuffle has already happened. Sort again for a new order, and delete column B when you're done. This is the same method the exercise on this page uses, with the random numbers already frozen.

How to split a shuffled list into random teams#

Once the list is in random order, you can deal people into teams like cards. With the shuffled names in C2:C11, type this in D2:

=MOD(SEQUENCE(ROWS(C2:C11))-1,3)+1

It spills the team numbers 1, 2, 3, 1, 2, 3, 1, 2, 3, 1 down column D. MOD returns the remainder after dividing by 3, so the numbers repeat in a cycle. Because the names are already shuffled, each person lands in a random team, and the teams differ in size by one at most: here 4, 3 and 3. Change the 3 to the number of teams you need. Freeze the names first, or the teams will change on the next recalculation.

Common problems when randomizing a list#

Most issues come from spilling and from blank cells:

• #SPILL! error. Something is in the way of the result. Clear the cells below and to the right of the formula. The guide to dynamic arrays in Excel explains spill ranges.
• Blank rows in the shuffle. If the range includes empty cells, they get shuffled in as zeros or blanks. Remove them first with FILTER: =SORTBY(FILTER(A2:A20,A2:A20<>""),RANDARRAY(COUNTA(A2:A20))).
• #VALUE! from SORTBY. The list and the random numbers must have the same number of rows. Using ROWS on the same range, as above, keeps them in step.
• The list changes when you didn't want it to. Paste as values, as shown above.

SORT or SORTBY for a random order?#

SORTBY is the right choice, because it sorts by an array that isn't part of the list. SORT can only sort by a column inside the range, so you'd first have to add the random numbers as a column. The article on SORT vs SORTBY compares them in detail, and the SORT function guide covers SORT's arguments. When you need a fixed order that isn't A to Z, such as High, Medium, Low, see how to sort by a custom order with a formula.

Practise it#

Shuffling is one line once SORTBY feels natural. Practise SORTBY, XMATCH and spill references in a live spreadsheet, then practise the SORT function to sort tables by any column, ascending or descending. Every formula is checked the moment you press Enter. The exercise on this page is a good warm-up: sort six names by frozen random numbers.