Pinterest Pixel

Generate Random Numbers: 2 Quick and Practical Ways in Excel

John Michaloudis
Let's say you are in charge of a task to create a system where you have to randomly select customers for a special promotion in your small business.
You want to ensure fairness and equal opportunity, giving every customer a chance to participate.

But how do you go about selecting customers randomly?
Generate Random Numbers: 2 Quick and Practical Ways in Excel | MyExcelOnline Generate Random Numbers: 2 Quick and Practical Ways in Excel | MyExcelOnline

Excel can make the selection process for the promotion unbiased and efficient. In this article, we will explore the methods that will allow you to Generate Random Numbers easily:

Let’s look at these methods thoroughly!

Download the Excel Workbook below to follow along and understand How to Use Substrings in Microsoft Excel 

download excel workbookRandom-Numbers.xlsx

 

RAND Function: Generating Random Numbers Between 0 and 1

We want to randomly select customers from your customer database.

Open an Excel worksheet and create a column labeled Order of Winners in column A, Customer Names in column B, and Random Number in column C. In cell C2, enter the formula =RAND(). This formula generates a random number between 0 and 1.
Generate Random Numbers: 2 Quick and Practical Ways in Excel

Press ENTER. Drag the formula down to the rest of the rows.
Generate Random Numbers: 2 Quick and Practical Ways in Excel

Generate Random Numbers: 2 Quick and Practical Ways in Excel

Select the cells with the random numbers and Right-click to Copy.
Generate Random Numbers: 2 Quick and Practical Ways in Excel

Select cells A2 to A31 and Right-click. Go to Paste Special, then Values.

Note: This step is important because RAND and RANDBETWEEN are volatile formulas and would recalculate every time there is any change in the worksheet.
Generate Random Numbers: 2 Quick and Practical Ways in Excel
Generate Random Numbers: 2 Quick and Practical Ways in Excel

Delete C2 to C31 since RAND will keep generating random numbers. Select A2 to B31 and Right-click. Go to Sort and you can choose between Sort Largest to Smallest or Sort Smallest to Largest.
Generate Random Numbers: 2 Quick and Practical Ways in Excel

Select the desired number of customers from the sorted list based on the random numbers assigned to them. For example, if you want to select 10 customers, choose the top 10 customers from the sorted list.
Generate Random Numbers: 2 Quick and Practical Ways in Excel

Note: Given the small range (1 to 30) that we are working with, it is possible that duplicate numbers may be generated. However, we can rely on Excel to handle the sorting process and resolve any duplicate values appropriately.

RANDBETWEEN Function: Generating Random Whole Numbers in a Custom Range

What if we want to know how to generate random whole numbers within a chosen range in Excel?

Open an Excel worksheet and create a column labeled Order of Winners in column A, Customer Names in column B, and Random Number in column C. In cell C2, enter the formula =RANDBETWEEN(1,30). This formula generates a random number between 1 and 30.
Generate Random Numbers: 2 Quick and Practical Ways in Excel

Drag down the formula in cell C2 and paste it into cells C3 to C31 to generate random whole numbers for each customer.
Generate Random Numbers: 2 Quick and Practical Ways in Excel

Select C2 to C31 and Right-click. Select Copy.
Generate Random Numbers: 2 Quick and Practical Ways in Excel

Select cells A2 to A31 and Right-click. Go to Paste Special, then Values.
Generate Random Numbers: 2 Quick and Practical Ways in Excel

Delete C2 to C31 since RANDBETWEEN will keep generating random numbers.
Generate Random Numbers: 2 Quick and Practical Ways in Excel

Select A2 to B31 and Right-click. Go to Sort and you can choose between Sort Largest to Smallest or Sort Smallest to Largest.
Generate Random Numbers: 2 Quick and Practical Ways in Excel

Select the desired number of customers from the sorted list based on the random numbers assigned to them. For example, if you want to select 10 customers, choose the top 10 customers from the sorted list.
Generate Random Numbers: 2 Quick and Practical Ways in Excel

There you have it! Excel’s RAND and RANDBETWEEN functions are indispensable tools for small businesses aiming to randomly select customers for special promotions. By understanding how to leverage these functions effectively, you can implement a fair and unbiased customer selection process, boosting engagement and maximizing the success of your promotions. With these methods, you can be assured that you’re promotion will be fair and square. Now, go ahead and apply this to your tasks!

If you want to generate a list of random numbers in an array, click here.

If you like this Excel tip, please share it
Founder & Chief Inspirational Officer at

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.

See also  Automatically Refresh a Pivot Table

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...