Function Problems

How to Select 100 Random Unique Invoice Numbers in Excel

Olivia Miller Sep 27, 2026 869 views

Question details

The user needs to randomly extract 100 unique invoice numbers from a larger list of 1,000 records without duplicates.

How to Select 100 Random Unique Invoice Numbers in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Auditing, sampling, or quality check processes where a random subset of data is required.
Observed behavior: Extracting a random subset without duplicating existing invoice numbers.

Before you start

Ensure your invoice dataset does not contain any pre-existing duplicates by using the 'Remove Duplicates' feature before generating random selections.

Solution 1Recommended

Use the RAND Function and Sort Feature

This method uses Excel's RAND function in a helper column to assign a random number to each invoice, which can then be sorted to easily pick the top 100.

The RAND() function generates a random decimal number between 0 and 1. Because each random number is highly likely to be unique, sorting by this helper column scrambles the invoice order effectively.

Remove Duplicates (Optional)

Select your invoice list, go to the 'Data' tab, and click 'Remove Duplicates' to ensure all starting numbers are unique.

Create a Helper Column

Insert a new column next to your invoice numbers. In the first row of this new column, type =RAND() and press Enter.

Apply the Formula to All Rows

Double-click the fill handle at the bottom-right corner of the cell to copy the =RAND() formula down to all 1,000 rows.

Sort the Data

Select all your data (both the invoice numbers and the random numbers). Go to the 'Data' tab, click 'Sort', and choose to sort by the helper column.

Extract the Top 100

Copy the first 100 rows of the newly sorted invoice column to your desired location.

Lock the Values

If you need to keep the random numbers from recalculating, copy them and use 'Paste Special' > 'Values' to overwrite the formulas.

Volatile Function Warning: The RAND function is volatile and will recalculate every time you make a change to the spreadsheet. Always paste as values if you want a fixed random sample.

Solution 2

Use Dynamic Array Formulas (Office 365)

Modern spreadsheet versions offer dynamic array formulas to perform random selections without manual sorting.

Effortlessly Extract Random Data Samples in WPS Spreadsheet

WPS Spreadsheet allows you to easily generate random numbers, sort datasets, and extract samples for audits or data analysis in seconds.

1. Open your dataset: Launch WPS Spreadsheet and open your invoice workbook.
2. Add a randomizer column: Type =RAND() in a blank column adjacent to your invoices and drag the fill handle down.
3. Sort and extract: Highlight the data range, click the 'Data' tab, sort by the randomizer column, and select the first 100 rows.

Seamless compatibility with Microsoft Excel (.xlsx) formatsSupports advanced functions like RAND, RANDARRAY, and SORTBYFast data processing for thousands of rows without lagFree and lightweight alternative for everyday office tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why do my random numbers keep changing in Excel?

The =RAND() function is volatile, meaning it recalculates every time a change is made to the worksheet. To stop this, copy the cells with the RAND formula, right-click, and choose 'Paste Special' > 'Values'.

Can I use the RANDBETWEEN function to pick random invoices?

RANDBETWEEN generates random integers and often creates duplicate numbers, which is not ideal for selecting unique records. Using RAND() with a sort helper column ensures non-repeating selections.

How do I randomly select rows if my data has multiple columns?

Apply the =RAND() formula in an empty helper column at the end of your dataset. When sorting, make sure you expand the selection to include all columns so the entire row moves together without scrambling your data fields.

Excel & Data Problems