How to Randomize Excel Lists Without Any Duplicates

How-to basics · Updated November 20, 2026

To shuffle a list in Microsoft Excel without repeating or losing entries, put the list in one column and use =SORTBY(A2:A20,RANDARRAY(ROWS(A2:A20))) in an empty cell. This returns the same items in a random order, provided the original list contains distinct entries. If the source has repeated values and you want each value only once, use UNIQUE before shuffling. The steps below also show how to keep a chosen result from changing when Excel recalculates.

There are two different goals people often mean by randomizing without duplicates: shuffling every entry in a list, or drawing a smaller sample without picking the same entry twice. A shuffle is useful for mixing a roster, task list, or question bank. For a sample, shuffle the full list first and take the required number of entries from the top. In both cases, check whether your original data itself contains duplicates before choosing a method.

What You’ll Need

  • Microsoft Excel for Microsoft 365 on Windows, or a recent Excel version with dynamic-array functions. The formula method uses SORTBY, RANDARRAY, and optionally UNIQUE, which are available in Microsoft 365 and Excel 2021 or later.
  • A source list with one item per cell, preferably with a header in row 1 and the entries starting in row 2. The examples use A2:A20; replace that range with the cells in your workbook.
  • An empty area for the randomized results. Dynamic-array formulas spill into cells below the formula, so keep that output area clear.
  • A saved copy of the workbook if the list is important. Random formulas recalculate, and pasting values over a formula changes the live result into a fixed one.

Step-by-Step

  1. Prepare and inspect the source list. Put each entry in its own cell in a single column, such as A2:A20, with no merged cells. Check for accidental blank rows, leading or trailing spaces, and repeated entries. If two entries look the same but differ because one contains an extra space or a different spelling, Excel may treat them as different values. Decide whether those should remain separate or be cleaned up. Keep a header such as Item in A1, but do not include the header in the formula range.
  2. Choose what counts as a duplicate. If you want to shuffle every source row, use the list as it is. That preserves repeated values: for example, if Apple appears twice in the source, it will appear twice in the shuffled output. If you want every distinct value only once, use the version with UNIQUE in step 5 instead. Be clear about whether a repeated name is truly an unwanted duplicate or represents two separate records, such as two people with the same name. For separate records, keep a unique ID or another identifying column alongside the names.
  3. Choose a blank output cell. Click an empty cell outside the source list, for example C2. Leave the cells below it empty for at least as many rows as there are source entries. A dynamic-array formula returns multiple results, and Excel displays them in a spill range. Do not place the formula inside the source range or inside an Excel Table column; table formulas do not spill as a multi-cell dynamic array in the same way. If Excel reports a spill error, clear anything blocking the output range.
  4. Enter the shuffle formula for a list that already has no duplicates. With distinct entries in A2:A20 and the formula entered in C2, use =SORTBY(A2:A20,RANDARRAY(ROWS(A2:A20))). Press Enter. Excel creates a random number for each entry and sorts the entries by those numbers. The result is a permutation: every source entry appears once, although the order changes. For a five-item example in A2:A6, one possible result is Pear, Mango, Apple, Grape, Orange. Your actual order will usually differ because the random values are generated again as Excel recalculates.
  5. Use UNIQUE first if the source includes repeated values you want removed. With possible repeats in A2:A20 and the formula in C2, enter =LET(items,UNIQUE(A2:A20),SORTBY(items,RANDARRAY(ROWS(items)))). The formula first creates a list of distinct values, then shuffles that shorter list. For example, if the source contains Apple, Pear, Apple, and Mango, the output contains Apple, Pear, and Mango once each in a random order. The formula includes blank cells as a distinct value if blanks are in the source range, so remove unwanted blank rows first. UNIQUE returns values Excel recognizes as distinct; it does not fix inconsistent spelling or extra spaces for you.
  6. Use the first entries for a random sample without repeats. If you need, for example, five items from a longer list, generate the complete shuffled output with the formula above and take the first five results. A sample taken from a shuffled list cannot select the same source row twice. If you used UNIQUE, the sample also contains no repeated values. Make sure the source has at least as many eligible entries as the number you need. If you need a formula that returns only the sample, newer Excel versions support TAKE, but taking the first rows of the full spill is an easy option that also lets you inspect the whole shuffle.
  7. Freeze the order if you need to keep it. The formulas are volatile: they can produce a new order when Excel recalculates, including after edits or when you press F9. To retain a draw, select the complete spilled result, copy it, and paste it into a separate blank range using Home > Paste > Values. Confirm that the pasted cells show the entries and no longer change when the workbook recalculates. Keep the original formula elsewhere if you want to generate another shuffle later. On Excel for Mac or Excel for the web, the same formula approach works in supported versions; ribbon layout and keyboard shortcuts can differ, so use the Paste Values command in the Home tab or paste menu.
  8. Check that the output is complete. Compare the number of returned items with the number of eligible source entries. For a regular shuffle, the counts should match. For a unique-value shuffle, the count should match the number of distinct nonblank values. If you sampled only part of the result, check that the sample size is what you intended and that no source entries were excluded by an incorrect range.

Common Mistakes

Expecting a shuffle to remove duplicates. SORTBY changes order; it does not remove repeated source values. If your input contains the same item three times, a full shuffle still contains it three times. Use UNIQUE when each distinct value should occur once, or remove duplicates from the source first with Data > Remove Duplicates if you want to change the source data itself. Remove Duplicates changes the selected data, so consider working on a copy.

Using the wrong range. Including a header in the formula can shuffle it among the entries. Leaving out the last row means that item never appears. Check the first and last cell references before accepting the output, especially if your list has grown since you last used it.

Thinking the random order is permanent. RANDARRAY recalculates, so a result that changes later is expected rather than evidence that the formula failed. Copy the output and paste values when a draw must be recorded or shared as a fixed result.

Putting something in the spill area. A value, space, merged cell, or another formula below the formula cell can prevent the results from appearing. Excel may show a #SPILL! error. Clear the obstructing cells or move the formula to an area with enough room.

Removing duplicates that are actually separate records. Two rows with the same display name may represent different people or transactions. Applying UNIQUE to names alone could collapse those records into one. If each row must remain separate, shuffle a unique record ID or use the helper-column method in Tips to sort whole rows together.

Tips

If you prefer to see the random key used for each row, use a helper column and sort the complete rows by that column. With items in A2:A20 and the first random key entered in B2, use =RAND() in B2 and fill it down through B20. Each cell returns a random decimal between 0 and 1. Then select both columns, including all the data rows, and choose Data > Sort. Sort by the random-number column, smallest to largest. For instance, a row with a key of 0.12 will appear before a row with 0.83. The exact values and order vary, but sorting the full range keeps each item attached to its own key. Do not sort only column B, or the keys will no longer match the original items.

The helper-column approach is also useful when you want to randomize several related columns. Select the entire rectangular data range, not just the item names, and sort by the helper column. If the range has headers, confirm that Excel recognizes them as headers in the Sort dialog. After sorting, you can delete the helper column if you no longer need it. The formula-based dynamic-array method is more convenient for a separate shuffled output; the helper method changes the order of the selected source rows.

To generate a fresh shuffle, press F9 on Windows to recalculate formulas. On many Mac keyboards, use fn+F9 if the function keys are assigned to hardware controls; the exact key behavior depends on keyboard settings. In Excel for the web, formulas recalculate according to the workbook’s calculation behavior, but shortcut support can vary by browser and keyboard. If pressing a key does not refresh the result, make a harmless edit and undo it, or use the available recalculation command for your Excel version. Avoid recalculating after you have made a draw final unless you have pasted values.

For a fairer and more auditable selection, keep a copy of the original list, note the date and the number of items eligible, and paste the chosen result as values. A spreadsheet random function is convenient for everyday shuffling, but it is not a cryptographic random-number generator. For casual classroom, planning, or game use, it is often sufficient; for regulated or high-stakes selection, follow the organization’s approved process.

Troubleshooting

The formula returns #NAME?. Check that the function names are spelled correctly and that you are using a version with the needed functions. SORTBY, RANDARRAY, UNIQUE, and LET are available in Microsoft 365 and Excel 2021 or later. If you are using an older version, use the helper-column method with RAND() and sort the full data range instead.

The formula returns #SPILL!. Select the formula cell and look for Excel’s spill-range warning. Clear the cells in the intended output area, unmerge cells there, or move the formula to a larger blank area. Also confirm that the formula is not being entered inside an Excel Table where the spill output cannot expand normally.

The order does not change. Confirm that calculation is set to Automatic under Formulas > Calculation Options. If calculation is set to Manual, choose Automatic or recalculate with F9. If you already pasted values, the order is fixed by design; return to the original formula or enter it again to create a new shuffle.

You still see repeated values. Check whether the repeats are in the source. The basic shuffle preserves them. If you used UNIQUE and still see entries that look duplicated, inspect for spaces, different punctuation, or spelling differences. Clean the source carefully, and remember that removing duplicates from names alone may discard distinct records.

Some items seem missing. Compare the formula’s range with the actual list and inspect for rows outside the selected range. If the output is a sample, entries below the sample cutoff are still in the full shuffled result; they were not selected for the smaller draw. Clear filters or hidden-row assumptions when checking counts, because a direct range formula may include rows that are hidden.

The helper sort separates names from their details. Undo immediately with Ctrl+Z on Windows, or Command+Z on Mac, if possible. Then select the entire data range and sort it again by the helper column. Sorting one column by itself rearranges only that column and can mismatch records.

Frequently Asked Questions

Does SORTBY guarantee no duplicates?
No. It rearranges the supplied range without adding or removing entries. It has no repeated values in the output only when the source itself has no repeated values. Use UNIQUE before sorting if you want one result per distinct value.

Can I pick a random number of entries without repeats?
Yes. Shuffle the eligible list and use the first required number of results. Do not take more entries than the number of eligible source rows, or, when using UNIQUE, more than the number of distinct eligible values.

Why does my randomized list keep changing?
RANDARRAY recalculates, so Excel can generate a new order after a workbook calculation or edit. Copy the results and use Home > Paste > Values in a separate range to preserve one particular draw.

Can I randomize rows while keeping multiple columns together?
Yes. Use a helper column containing RAND(), then sort the whole table or data range by that helper column. This keeps each row’s fields together. Do not sort just the random-number column.

Can I use this in Excel for the web or on a Mac?
The dynamic-array formulas work in Excel for the web and current Mac versions when the functions are available. Some ribbon labels, menus, and keyboard shortcuts vary, but the formula and the need to paste values to fix a result are the same.

Does UNIQUE ignore blank cells?
No, a blank can appear as one distinct result if it is included in the source range. Remove unwanted blank rows or adjust the source range before shuffling so the randomized output contains only eligible entries.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.