Function Problems

How to Generate Unique Random Integers Without Duplicates in Excel

Maira Mehtab Sep 22, 2026 875 views

Question details

The user needs to create a randomized list of non-repeating integers using Excel formulas.

Product: Excel
Device & OS: not provided
Scenario: Generating random numbers for sampling, lottery draws, or random testing sequences where duplicate values are not allowed.
Observed behavior: A dynamic array formula is needed to output random integers where every number in the generated list is entirely unique.

Before you start

Ensure you are using a modern version of Excel or WPS Spreadsheet that supports dynamic array functions, as formulas utilizing RANDARRAY, UNIQUE, and SEQUENCE will not work in older versions.

Solution 1Recommended

Use RANDARRAY and UNIQUE for a Specific Range

Combine the RANDARRAY function with UNIQUE to generate random numbers and automatically filter out any duplicate values.

This method involves generating an array of random numbers between a specific minimum and maximum value, sorting them, and keeping only the unique outputs.

Select a destination cell

Click on the empty cell where you want the list of unique random integers to begin.

Enter the formula

Type the formula =UNIQUE(SORT(RANDARRAY(15,1,1,100,TRUE))) to request 15 unique random integers between 1 and 100.

Evaluate and recalculate

Press Enter. Because the UNIQUE function removes duplicates from the initially generated 15 numbers, the final list may be shorter than 15. Press F9 on your keyboard to recalculate the sheet until the required number of unique values is returned.

Handling Missing Values: Since RANDARRAY may naturally generate duplicates that UNIQUE then removes, it is best to ask RANDARRAY to generate a larger initial pool (e.g., 30 numbers) and extract the top 15 using the INDEX or CHOOSEROWS function if you want to avoid manually pressing F9.

Solution 2

Use SORTBY and SEQUENCE to Guarantee a Set Count

Generate a strict set of unique sequential numbers and randomly sort them, guaranteeing exactly the requested number of unique values without manual recalculation.

Advanced Spreadsheet Functions

Generate Random Data Easily with WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic array functions like RANDARRAY, SEQUENCE, SORT, and UNIQUE. You can easily apply these powerful formulas to quickly generate random, non-repeating integers for your sampling or data modeling needs.

1. Open a new spreadsheet: Launch WPS Office and create a new blank WPS Spreadsheet.
2. Select a cell: Click on any empty cell where you want your random integers to appear.
3. Input the array formula: Type =SORTBY(SEQUENCE(10),RANDARRAY(10)) to generate 10 guaranteed unique random numbers.
4. Apply and review: Press Enter and watch the dynamic array spill cleanly down the column.

Fully compatible with Microsoft Excel dynamic array formulas.Built-in support for array results to spill perfectly across adjacent cells.Lightweight, fast installation with an intuitive, familiar interface.Completely free to use for daily data processing tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Can I set a custom starting number for the SEQUENCE method?

Yes. You can modify the SEQUENCE function to start at a different number. For example, =SORTBY(SEQUENCE(10,,50), RANDARRAY(10)) will give you 10 unique random integers starting from 50.

Why do my random numbers keep changing every time I type something?

Functions like RANDARRAY are 'volatile', meaning they automatically recalculate every time any change is made to the worksheet. To make the random numbers permanent, highlight the generated list, copy it (Ctrl+C), right-click the same location, and choose 'Paste as Values'.

Why am I getting a #NAME? error when using RANDARRAY?

The #NAME? error typically indicates that your version of the software does not support dynamic array functions. Ensure you are using a recent version like Microsoft 365, Excel 2021, or the latest version of WPS Office.

Excel & Data Problems