Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Find Out How to Generate Unique Random Numbers in Excel

Excel allows us to generate unique random numbers in Excel using the RAND and RANK functions. This step by step tutorial will assist all levels of Excel users to find out how to generate unique random numbers in Excel.

Figure 1. The final result of the formula

Syntax of the RAND Formula

The generic formula for the RAND function is:

=RAND()

The function returns a random decimal number between 0 and 1 and has no parameters.

Syntax of the RANK Formula

The generic formula for the RANK function is:

=RANK(number, ref, [order])

The parameters of the RANK function are:

  • number – a number which we want to rank
  • ref – a list of numbers for ranking
  • [order] – 1 for ascending or 0 for descending order. This parameter is optional, if it’s omitted, the default order is descending.

 

 

Set up a Random Team Generator

First, we need to get a random number in column B

The formula for RAND in B3 looks like:

=RAND()

To apply the formula, we need to follow these steps:

  • Select cell B3 and click on it
  • Insert the formula: =RAND()
  • Press enter
  • Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

Figure 2. Using the RAND formula

When we get a random number we can rank them in column C.

The formula for RANK in C3 looks like:

=RANK(B3, $B$3:$B$14)

The number parameter is the cell B3. The ref parameter is the range $B$3:$B$14. We must fix the range, as it’s not changing when the formula is copied down the cells.

To apply the formula, we need to follow these steps:

  • Select cell C3 and click on it
  • Insert the formula: =RANK(B3,$B$3:$B$14)
  • Press enter
  • Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

Figure 3. Using the RANK formula

Now we have 12 unique random numbers in column C.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Solution examples
im creating a fantasy hockey model and trying to figure out how to rank if someone has played more than a certain number of games, and if they're certain position how to rank them accordingly
Solved by X. D. in 60 mins
I'm Apply a =RANDBETWEEN formula in one cells want to apply Formula to entire column and run formula when i hit the entire on the particular cell
Solved by M. S. in 20 mins
Help! In a coin toss I have a 1 in 2 (fifty-fifty) chance of getting heads or tails. I need a formula to determine my chance of getting 4 heads in a row or 4 tails in a row out of 10 coin tosses. If I get 4 in a row I win. What formula can I use to determine my chances of winning if I repeat the 10 tosses for hundreds or thousands of times? I get as far as =RANDBETWEEN(0,1) but I have not been able to figure out what formula to use to determine if there are 4 in a row in a group of 10 tosses, times thousands of groups with 10 tosses in each group. Thank-you for any help you can provide.
Solved by V. F. in 51 mins
I have 4 column. when i fill the first 3 column. in 4th column i need a unique id number and i generate unique id number using RANDBETWEEN formula [ =CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(1000,9900) ] but i want ..... when i'm filling the 3 column after that 4th column run this formula and generate unique id automatically
Solved by X. Q. in 20 mins
I cannot enter this formula into Excel. I get an error message asking for an apostrophe?IF($S2=0,"",IF($S2>0,RANK($S2,$S$2:$S$8000),RANK($S2,$S$2:$S$8000-COUNTIF($S$2:$S$8000,0)))
Solved by B. W. in 20 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc