Assign unique random number using Excel

This tutorial explains how to use assign unique random number using Excel. You can apply this skills to Simple Random Sampling.

You may also want to read:

Generate random letter A to Z

Generate random HKID


Assign unique random number using Excel

There are several ways to assign unique random number, the simplest way to do it is to use Worksheet Rand Function. You can apply this skills to Simple Random Sampling.

Rand Function generates a decimal number from 0 to 1 up to 15 decimal places, therefore the chance of duplication is very little (Excel 2013 only has about 1 million rows).

For example, we have 10 values from 1 to 10.

assign unique random number 01

Select column B1 to B10, type formula


and then press Ctrl + Enter, this will generate a list of random decimal number.

assign unique random number 02

Every time you change a cell, all random values change as well. To freeze the number, copy B1:B10 and paste as value.

Assign 1 to n to n numbers

Although you have assigned a random number to column B, it looks ugly and does not make much sense. You can consider sorting column B and assign 1 to 10 in ascending order so that column A are assigned random number. There is a more elegant way to do the sorting with Rank Function.

Type the below formula in C1 and autofill down


assign unique random number 03

Unless column B value has duplicates (but the chance is extremely small), column C will be unique.

Sampling with replacement vs sampling without replacement

Assume that you have 10 balls (number 1 to 10) in a  box, and you take 5 balls out. Would you put back the ball back into the box every time you take a ball out? If you put the ball back, then all the balls have an equal probability of being taken out. If you don’t put it back, then each ball has a different probability. However if you put the ball back, there is a chance you draw the same ball more than once.

The method introduced in this post has the advantage of sampling with replacement while it has avoided duplicated numbers. In my another post, I have also demonstrated a way to simulate sampling without replacement method to ensure no duplicates, click here to learn more.


Leave a Reply

Your email address will not be published.