random assignment generator excel

Randomly assign data to groups

Related functions .

To randomly assign rows of data to arbitrary groups, you can use the RANDBETWEEN function with the CHOOSE function . In the example shown, the formula in F5 is:

As the formula is copied down the column, it will return a random group ("A", "B", or "C") at each new row.

Note: this approach will create groups of different sizes. If you need to assign random groups with a fixed size, see  this formula .

Generic formula

Explanation .

In this example, the goal is to return a random group ("A", "B", or "C") at each new row. The simplest way to do this is to use the RANDBETWEEN function with the CHOOSE function. In the current version of Excel, it is also possible to generate all random groups in one step with the RANDARRAY function. Both approaches are explained below.

The CHOOSE function

The CHOOSE function returns a value from a list of values using an index number. The index number is provided as the first argument, and the values to be selected follow. For example, if we have a list of three colors ("red", "blue", and "green"), we can configure CHOOSE to return each color in turn with the following formulas:

Notice that CHOOSE uses the index number to select the "nth" value from the list of values. The values can be customized in any way you like and the only requirement is that the index number be valid for the number of values provided. Of course, in this example, we don't want to hardcode an index number into CHOOSE, we want a random index number. For this, we can use the RANDBETWEEN function.

The RANDBETWEEN function

The RANDBETWEEN function generates a random number between two integers, provided as the bottom and the top . For example, to generate a random number between 1 and 10, you can use RANDBETWEEN like this:

When Excel's calculation engine updates a worksheet, RANDBETWEEN will generate a random number between 1 and 10.

CHOOSE with RANDBETWEEN

The behavior of RANDBETWEEN will work perfectly for this problem. We have three possible groups ("A","B","C") so we need a random number between 1 and 3, which we can get like this:

The final step is to embed RANDBETWEEN into the CHOOSE function as the index number like this:

This is the formula that appears in cell F5 in the example shown. When the formula is copied down the column, RANDBETWEEN returns a random number between 1 and 3. This number is delivered directly to the CHOOSE function as the index number, and CHOOSE returns the corresponding color as a final result. You can use this approach whenever you need to assign random text values to each row in a data set. Just be sure to adjust the second argument in RANDBETWEEN, top , to match the number of values provided.

Stopping automatic recalculation

Be aware that RANDBETWEEN is a volatile function and will recalculate whenever there is any change to a workbook, or even when a workbook is opened. To force a recalculation, you can press the F9 key. Once you have a set of random assignments, you may want to stop the formula from returning new results. The classic way to do this is to use Paste Special:

• Select all cells that contain the CHOOSE and RANDBETWEEN formula.
• Copy to the clipboard with Control + C.
• Open the Paste Special window with the shortcut Control + Alt + V.
• Select "Values" and click OK:

After you press OK, all formulas will be replaced with static values.

Dynamic array formula

In the current version of Excel (Excel 2021 or later) you can use a single dynamic array formula to generate all random values at once. One option is to use the RANDARRAY function with CHOOSE like this:

The core idea of this formula is the same as the original formula above. However, instead of RANDBETWEEN, we use RANDARRAY, which can generate an array of random numbers in one step. To figure out how many random numbers to generate, we use the ROWS function on a range corresponding to the first column of the data. This saves us the step of telling RANDARRAY how many rows we need. In this case, ROWS returns 100, because there are 100 rows in the range B5:B104. Simplifying, we now have:

Next, RANDARRAY generates an array of 100 random numbers between 1 and 3. The result is returned to CHOOSE as the index_num argument, and CHOOSE uses the random numbers to return an array that contains 100 random groups. This array lands in cell F5 and spills into the range F5:F104.

INDEX alternative

It is also possible to use the INDEX function instead of CHOOSE in a formula like this:

Like CHOOSE, INDEX retrieves a value based on an index number. INDEX however accepts the values all at once in the first argument, called array . In the formula above, the values "A", "B", and "C" are provided as an array constant to INDEX as the array , and RANDBETWEEN is used as before to generate a random number between 1 and 3.  The RANDARRAY version of the formula with INDEX looks like this:

One advantage of INDEX is that the array constant can be replaced with a range on the worksheet. In other words, you can enter group names into a range and provide that range to INDEX. The CHOOSE function will not accept a range of values; it requires that values be provided separately.

Note: the formulas on this page will create completely random groups. One result is that the total number of rows assigned to each group will vary. If you need to assign random groups with a fixed size (i.e. randomly assign people to teams of 6), see the example on  this page .

Related formulas

• Randomly assign people to groups

• Random number between two numbers

• Random date between two dates

• Random text values

• Random value from list or table

• Random number from fixed set of options

Related functions

• RANDBETWEEN Function

The Excel RANDBETWEEN function returns a random integer between two given numbers. RANDBETWEEN recalculates each time a worksheet is opened or changed.

• CHOOSE Function

The Excel CHOOSE function returns a value from a list using a given position or index. For example, =CHOOSE(2,"red","blue","green") returns "blue", since blue is the 2nd value listed after the index number. The values provided to CHOOSE can include references.

• RANDARRAY Function

The Excel RANDARRAY function generates an array of random numbers between two values. The size or the array is is specified by rows and columns arguments. The generated values can be either decimals or whole numbers.

• ROWS Function

The Excel ROWS function returns the count of rows in a given reference. For example, ROWS(A1:A3) returns 3, since the range A1:A3 contains 3 rows.

Related videos

• How to pick names out of a hat with Excel

• How to randomly assign people to teams

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Related Information

Get training, quick, clean, and to the point training.

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

Help us improve Exceljet

Your email address is private and not shared.

Excel Tutorial: How To Randomly Assign Participants To Equal Groups In Excel

Introduction.

When conducting research studies, it is crucial to randomly assign participants to equal groups to ensure fairness and accuracy in the results. This helps to minimize bias and ensure that the groups are comparable and representative of the larger population. One effective way to achieve this is by using Excel , a widely used software for data analysis and management. Excel offers various functions and tools that can simplify the process of randomly assigning participants to equal groups, saving time and ensuring precision in the research process.

Key Takeaways

• Randomly assigning participants to equal groups is crucial in research studies to ensure fairness and accuracy in the results
• Excel offers various functions and tools that can simplify the process of randomly assigning participants to equal groups
• The RAND function in Excel can be used to generate random numbers for participant assignment
• Sorting and grouping participants based on random numbers is essential for ensuring equal group distribution
• A fair and unbiased method for participant assignment is important for the validity of research studies

Understanding the RAND Function in Excel

When it comes to randomly assigning participants to equal groups in Excel, the RAND function is an essential tool. This function allows you to generate random numbers, which can then be used to distribute participants evenly across different groups.

The RAND function in Excel is used to generate a random number between 0 and 1. Each time the worksheet is recalculated, a new random number is generated. This makes it a useful tool for scenarios where randomization is required, such as in assigning participants to groups.

To use the RAND function, you simply need to enter it into a cell in your Excel worksheet. When you press Enter, a random number between 0 and 1 will be generated. You can then use this number to assign participants to different groups based on the range of random numbers generated.

Using the RAND function for participant assignment

• First, generate a list of random numbers using the RAND function, one for each participant.
• Next, sort the list of random numbers in ascending order.
• Finally, divide the sorted list into equal groups, ensuring that each participant is assigned to a group based on their corresponding random number.

Generating Random Numbers for Participant Assignment

When conducting research or organizing a group for a project, it's important to assign participants to groups randomly to avoid bias. Excel provides a simple way to generate random numbers for participant assignment using the RAND function.

• A. Discuss the steps for using the RAND function to generate random numbers for each participant

The RAND function in Excel is used to generate a random number between 0 and 1. To use the RAND function for participant assignment, follow these steps:

• Step 1: Open a new Excel spreadsheet and create a column for participant names.
• Step 2: In the adjacent column, use the formula =RAND() to generate a random number for each participant.
• Step 3: Copy the formula down to generate a random number for each participant.
• Step 4: Sort the random numbers from smallest to largest using the Excel sorting function.
• B. Provide tips for avoiding bias when using the RAND function for participant assignment

While the RAND function in Excel is a convenient way to generate random numbers, it's important to be mindful of potential biases that can arise. Here are some tips to avoid bias when using the RAND function for participant assignment:

• Avoid manual selection: Resist the urge to manually assign participants based on the generated random numbers, as this can introduce bias.
• Use a large sample size: When generating random numbers for participant assignment, aim to have a large sample size to minimize the impact of outliers.
• Double-check your results: After using the RAND function to assign participants, double-check the distribution of participants in each group to ensure it's truly random.

Sorting and Grouping Participants Based on Random Numbers

When conducting research or organizing events, it's often necessary to randomly assign participants to equal groups. Excel can be a helpful tool in this process, allowing you to generate random numbers and then sort and group participants based on those numbers.

Explain how to sort the random numbers generated for participants

• Generate Random Numbers: To begin, you can use the RAND() function in Excel to generate a random number for each participant. Simply enter =RAND() in a cell and drag the fill handle down to apply the formula to each participant.
• Sort Random Numbers: Once the random numbers have been generated, you can then sort them in ascending order by selecting the range of random numbers and using the Sort & Filter feature under the Data tab. This will arrange the participants in a random order based on the generated numbers.

Discuss how to group the participants based on the sorted random numbers

• Create Groups: After the participants have been sorted based on their random numbers, you can create equal groups by manually assigning them to different categories (e.g., Group 1, Group 2, etc.) based on the sorted order.
• Automate Grouping: Alternatively, you can use the IF function in Excel to automate the grouping process. For instance, you can use a formula like =IF(A2<=0.33, "Group 1", IF(A2<=0.66, "Group 2", "Group 3")) to assign participants to different groups based on their sorted random numbers.

Checking for Equal Group Distribution

When conducting research studies or experiments, it is crucial to ensure that participants are randomly assigned to equal groups in order to maintain the validity and reliability of the results. Here are some methods for checking and ensuring equal group distribution:

Descriptive Statistics:

Chi-square test:, visual inspection:.

Ensuring equal group distribution is essential for maintaining the internal validity of a study. When participants are randomly assigned to groups, it helps to minimize the impact of confounding variables and ensures that any observed differences in outcomes are due to the manipulation of the independent variable and not other extraneous factors.

Furthermore, equal group distribution enhances the generalizability of the findings, as it allows for more accurate conclusions to be drawn about the overall population from which the participants were drawn.

Potential Issues and Solutions

When using Excel for participant assignment, there are a few potential issues that may arise:

• Data Input Errors: Human error in typing or copying data can lead to incorrect participant assignment.
• Unequal Group Sizes: Excel's randomization feature may not always produce perfectly equal group sizes, leading to imbalanced data analysis.
• Duplicate Assignments: There is a possibility of participants being randomly assigned to multiple groups, leading to biased results.

To address these potential issues, consider the following solutions and workarounds:

Data Input Errors

To minimize data input errors, use data validation and drop-down lists in Excel to ensure accurate participant information entry. Double-checking the input data for accuracy can also help prevent errors in participant assignment.

Unequal Group Sizes

If Excel's randomization feature produces unequal group sizes, consider using the RAND function in combination with the RANK.EQ function to rank and allocate participants into groups, ensuring equal distribution.

Duplicate Assignments

To avoid duplicate assignments, use the COUNTIF function in Excel to check for duplicate participant assignments before finalizing the groups. Additionally, consider using a VBA macro to automatically remove any duplicate assignments from the dataset.

Randomly assigning participants to equal groups in Excel is a crucial step in research studies to ensure fairness and statistical validity . To do this, you can follow these steps: select a column for participant names, use the RAND function to generate random numbers, and then sort the participants based on the generated numbers. It is important to use a fair and unbiased method for participant assignment to maintain the integrity of your research study and ensure accurate results.

By following these steps and emphasizing the importance of fairness in participant assignment, you can conduct research studies with confidence and credibility .

Immediate Download

MAC & PC Compatible

Free Email Support

Related aticles

The Benefits of Excel Dashboards for Data Analysts

Unlock the Power of Real-Time Data Visualization with Excel Dashboards

Unlocking the Potential of Excel's Data Dashboard

Unleashing the Benefits of a Dashboard with Maximum Impact in Excel

Exploring Data Easily and Securely: Essential Features for Excel Dashboards

Unlock the Benefits of Real-Time Dashboard Updates in Excel

Unleashing the Power of Excel Dashboards

Understanding the Benefits and Challenges of Excel Dashboard Design and Development

Leverage Your Data with Excel Dashboards

Crafting the Perfect Dashboard for Excel

An Introduction to Excel Dashboards

How to Create an Effective Excel Dashboard

• Choosing a selection results in a full page refresh.

• Kutools for Excel
• Kutools for Outlook
• Kutools for Word
• Setup Made Simple
• End User License Agreement
• Get 4 Software Bundle
• 60-Day Refund
• Tips & Tricks for Excel (3000+)
• Tips & Tricks for Outlook (1200+)
• Tips & Tricks for Word (300+)
• Excel Functions (498)
• Excel Formulas (350)
• Excel Charts
• Outlook Tutorials
• About Us Our Team

Feature Tutorials

• Search Search more
• Retrieve License Lost License?
• Report a Bug Bug Report
• Forum Post in Forum

Quickly generate random groups for list of data in Excel

Sometimes, you may want to randomly assign data to groups as screenshot 1 shown, or generate groups for a list of names as below screenshot 2 shown, but how can handle these jobs quickly? Actually, in Excel, you can use formulas to solve them easily.

Randomly assign data to groups

Generate random groups in a specified data size, download sample file.

If you want to randomly assign data to a specified number of groups, each group is allowed with different numbers of data, you can use the CHOOSE and RANDBETWEEN functions.

Select a blank cell next to the list you want to assign to random groups, copy or type this formula

=CHOOSE(RANDBETWEEN(1,3),"Group A","Group B","Group C ")

In the formula, (1, 3) indicates to group data into 3 groups, Group A, Group B and Group C are the texts will be displayed in formula cells which used to distinguish different groups.

Then the list of data has been randomly assigned to groups, and each group may have different numbers of data.

The calculated results will not be fixed, they will be recalculated if there is any change to the workbook.

If you want to generate random groups for a list of data, and each group has a specified data size, you can use the ROUNDUP and RANK functions.

1. Firstly, you need a helper column to list some random data next to your data. Supposing in cell E2, type this formula

Then drag fill handle down to fill this formula to cells you use.

2. In the next column, supposing in cell F2, copy or type this formula

=ROUNDUP(RANK(E2,$E$2:$E$13)/4,0)

E2:E13 is the range that contains formula =RAND(), 4 is the number of data that you want each group contains.

Click to download sample file

Other Popular Articles

Remove first or last n characters from a cell or string in Excel

Extract part of text string from cell in Excel?

Two Easy Ways to convert or import Word document contents to Excel worksheet

Calculate the absolute difference between two values/times in Excel

More articles

Excel Productivity Tools

The best office productivity tools, kutools for excel solves most of your problems, and increases your productivity by 80%.

• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range ...
• Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average ... Prevent Duplicate Cells; Compare Ranges ...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets , Check Boxes and more...
• Favorite and Quickly Insert Formulas , Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
• Extract Text , Add Text, Remove by Position, Remove Space ; Create and Print Paging Subtotals; Convert Between Cells Content and Comments ...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets ; Merge Tables based on key columns; Split Data into Multiple Sheets ; Batch Convert xls, xlsx and PDF ...
• Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name ...

Office Tab - brings tabbed interface to Office, and make your work much easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint , Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

• Ablebits blog
• Random data

How to generate random numbers in Excel: RAND and RANDBETWEEN functions

The tutorial explains the specificities of the Excel random number generator algorithm and demonstrates how to use RAND and RANDBETWEEN functions to generate random numbers, dates, passwords and other text strings in Excel.

Before we delve into different techniques of generating random numbers in Excel, let's define what they actually are. In plain English, random data is a series of numbers, letters or other symbols that lacks any pattern.

Randomness has a variety of different applications in cryptography, statistics, lottery, gambling, and many other fields. And because it has always been in demand, various methods of creating random numbers have existed since ancient times, such as flipping coins, rolling dice, shuffling playing cards, and so on. Of course, we won't rely on such "exotic" techniques in this tutorial and focus on what Excel random number generator has to offer.

Excel random number generator - the basics

Let's take a closer look at the Excel random generator algorithm so that you know what you can expect from it, and what you cannot.

Like most computer programs, Excel random number generator produces pseudo-random numbers by using some mathematical formulas. What it means for you is that, in theory, random numbers generated by Excel are predictable, provided that someone knows all the details of the generator's algorithm. This is the reason why it has never been documented and will hardly ever be. Well, what do we know about the random number generator in Excel?

• The Excel RAND and RANDBETWEEN functions generate pseudo-random numbers from the Uniform distribution , aka rectangular distribution, where there is equal probability for all values that a random variable can take on. A good example of the uniform distribution is tossing a single die. The outcome of the toss is six possible values (1, 2, 3, 4, 5, 6) and each of these values is equally likely to occur. For a more scientific explanation, please check out wolfram.com .
• There is no way to seed either Excel RAND or RANDBETWEEN function, which are rumored to be initialized from the computer's system time. Technically, a seed is the starting point for generating a sequence of random numbers. And every time an Excel random function is called, a new seed is used that returns a unique random sequence. In other words, when using the random number generator in Excel, you cannot get a repeatable sequence with the RAND or RANDBETWEEN function, nor with VBA, nor by any other means.
• In early Excel versions, prior to Excel 2003, the random generation algorithm had a relatively small period (less than 1 million nonrecurring random number sequence) and it failed several standard tests of randomness on lengthy random sequences. So, if someone still works with an old Excel version, you'd better not use the RAND function with large simulation models.

If you are looking for true random data, you can probably use a third-party random number generator such as www.random.org whose randomness comes from atmospheric noise. They offer free services to generate random numbers, games and lotteries, color codes, random names, passwords, alphanumeric strings, and other random data.

Excel RAND function - generate random real numbers

The RAND function in Excel is one of the two functions specially designed for generating random numbers. It returns a random decimal number (real number) between 0 and 1.

RAND() is a volatile function, meaning that a new random number is generated every time the worksheet is calculated. And this happens every time you perform any action on a worksheet, for example update a formula (not necessarily the RAND formula, just any other formula on a sheet), edit a cell or enter new data.

The RAND function is available in all versions of Excel 365 - 2000.

And now, let's take a step further and write a few RAND formulas to generate random numbers according to your conditions.

Formula 1. Specify the upper bound value of the range

To generate random numbers between zero and any N value, you multiple the RAND function by N:

For example, to create a sequence of random numbers greater than or equal to 0 but less than 50, use the following formula:

Formula 2. Generate random numbers between two numbers

To create a random number between any two numbers that you specify, use the following RAND formula:

Where A is the lower bound value (the smallest number) and B is the upper bound value (the largest number).

For example, to generate random numbers between 10 and 50, you can use the following formula:

=RAND()*(50-10)+10

Formula 3. Generating random integers in Excel

To make the Excel RAND function produce random integers, take either of the above mentioned formulas and wrap it in the INT function.

To create random integers between 0 and 50:

=INT(RAND()*50)

To generate random integers between 10 and 50:

Excel RANDBETWEEN function - generate random integers in a specified range

RANDBETWEEN is another function provided by Excel for generating random numbers. It returns random integers in the range that you specify:

Obviously, b ottom is the lowest number and top is the highest number in the range of random numbers you want to get.

Like RAND, Excel's RANDBETWEEN is a volatile function and it returns a new random integer every time your spreadsheet recalculates.

For instance, to generate random integers between 10 and 50 (including 10 and 50), use the following RANDBETWEEN formula:

The RANDBETWEEN function in Excel can create both positive and negative numbers. For example, to get a list of random integers from -10 to 10, enter the following formula in your worksheet:

=RANDBETWEEN(-10, 10)

The RANDBETWEEN function is available in Excel 365 - Excel 2007. In earlier versions, you can use the RAND formula demonstrated in Example 3 above.

Further on in this tutorial, you will find a few more formula examples demonstrating how to use the RANDBETWEEN function to generate random values other than integers.

Tip. In Excel 365 and Excel 2021, you can use the dynamic array RANDARRAY function to returns an array of random numbers between any two numbers that you specify.

Create random numbers with specified decimal places

Though the RANDBEETWEEN function in Excel was designed to return random integers, you can force it to return random decimal numbers with as many decimal places as you want.

For instance, to get a list of numbers with one decimal place, you multiply the bottom and top values by 10, and then divide the returned value by 10:

The following RANDBETWEEN formula returns random decimal numbers between 1 and 50:

In a similar manner, to generate random numbers between 1 and 50 with 2 decimal places, you multiply the RANDBETWEEN function's arguments by 100, and then divide the result by 100 as well:

How to generate random dates in Excel

To return a list of random dates between the given two dates, use the RANDBETWEEN function in combination with DATEVALUE :

For example, to get the list of dates between 1-Jun-2015 and 30-June-2015 inclusive, enter the following formula in your worksheet:

=RANDBETWEEN(DATEVALUE("1-Jun-2015"),DATEVALUE("30-Jun-2015"))

Alternatively, you can use the DATE function instead of DATEVALUE:

=RANDBETWEEN(DATE(2015,6,1),DATEVALUE(2015,6,30))

How to insert random times in Excel

To return random times according to your criteria, more specific random formulas are required, as demonstrated below.

Formula 1. Generate random times in the specified range

To insert random times between any two times that you specify, use either the TIME or TIMEVALUE function in conjunction with Excel RAND:

For example, to insert a random time between 6:00 AM and 5:30 PM, you can use either of the following formulas:

=TIME(6,0,0) + RAND() * (TIME(17,30,0) - TIME(6,0,0))

Formula 2. Generating random dates and times

To create a list of random dates and times , use a combinations of RANDBETWEEN and DATEVALUE functions:

Supposing you want to insert random dates between June 1, 2015 and June 30, 2015 with a time between 7:30 AM and 6:00 PM, the following formula will work a treat:

=RANDBETWEEN(DATEVALUE("1-Jun-2015"), DATEVALUE("30-Jun-2015")) + RANDBETWEEN(TIMEVALUE("7:30 AM") * 10000, TIMEVALUE("6:00 PM") * 10000) / 10000

You can also supply dates and times using the DATE and TIME functions, respectively:

Generating random letters in Excel

To return a random letter, a combination of three different functions is required:

=CHAR(RANDBETWEEN(CODE("A"),CODE("Z")))

Where A is the first character and the Z is the last character in the range of letters you want to include (in alphabetical order).

In the above formula:

• CODE returns numeric ANSI codes for the specified letters.
• RANDBETWEEN takes the numbers returned by the CODE functions as the bottom and top values of the range.
• CHAR converts random ANSI codes returned by RANDBETWEEN to the corresponding letters.

If someone remembers the ANSI Character Codes Chart by heart, nothing prevents you from supplying the codes directly to the RANDBETWEEN function.

For example, to get random UPPERCASE letters between A (ANSI code 65) and Z (ANSI code 90), you write:

=CHAR(RANDBETWEEN(65, 90))

To generate lowercase letters from a (ANSI code 97) to z (ANSI code 122), you use the following formula:

=CHAR(RANDBETWEEN(97, 122))

To insert a random special character, such as ! " # $ % & ' ( ) * + , - . /, use the RANDBETWEEN function with the bottom parameter set to 33 (ANSI code for "!') and the top parameter set to 47 (ANSI code for "/").

Generating text strings and passwords in Excel

To create a random text string in Excel, you just have to concatenate several CHAR / RANDBEETWEEN functions.

For example, to generate a list of passwords consisting of 4 characters, you can use a formula similar to this:

=RANDBETWEEN(0,9) & CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(97, 122)) & CHAR(RANDBETWEEN(33,47))

To make the formula more compact, I supplied the ANSI codes directly in the formula. The four functions return the following random values:

• RANDBETWEEN(0,9) - returns random numbers between 0 and 9.
• CHAR(RANDBETWEEN(65,90)) - returns random UPPERCASE letters between A and Z .
• CHAR(RANDBETWEEN(97, 122)) - returns random lowercase letters between a and z .
• CHAR(RANDBETWEEN(33,47)) - returns random special characters.

A word of caution! If you use a similar formula to create random passwords, they won't be strong. Of course, there's nothing saying you can't generate longer text strings by chaining more CHAR / RANDBETWEEN functions. However, it's impossible to randomize the order or characters, i.e. the 1 st function always returns a number, the 2 nd function returns an uppercase letter and so on.

If you are looking for an advanced random password generator in Excel capable of producing text strings of any length and pattern, you may want to check out the capabilities of Advanced Random Generator for test strings .

How to prevent RAND and RANDBETWEEN from recalculating

If you want to get a permanent set of random numbers, dates or text strings that won't change every time the sheet is recalculated, use one of the following techniques:

• To stop the RAND or RANDBETWEEN functions from recalculating in one cell , select that cell, switch to the formula bar and press F9 to replace the formula with its value.
• To prevent an Excel random function from recalculating, use the Paste Special > Values feature. Select all the cells with the random formula, press Ctrl + C to copy them, then right click the selected range and click Paste Special > Values .

How to generate unique random numbers in Excel

Neither of Excel's random functions can produce unique random values. If you want to create a list of random numbers without duplicates , perform these steps:

• Use the RAND or RANDBETWEEN function to generate a list of random numbers. Create more values than you actually need because some will be duplicates to be deleted later.
• Convert formulas to values as explained above.
• Remove duplicate values by using either Excel's built-in tool or our advanced Duplicate Remover for Excel .

Advanced Random Number Generator for Excel

Now that you know how to use random functions in Excel, let me demonstrate you a faster, easier and formula-free way to create a list of random numbers, dates or text strings in your worksheets.

AbleBits Random Generator for Excel was designed as a more powerful and user-friendly alternative to Excel's RAND and RANDBETWEEN functions. It works with all versions of Microsoft Excel 2019, 2016, 2013, 2010, 2007 and 2003 equally well and addresses most of the quality and usability issues of the standard random functions.

AbleBits Random Number Generator algorithm

Before showing our Random Generator in action, let me provide a few key notes on its algorithm so that you know exactly what we are offering.

• AbleBits Random Number Generator for Excel is based on the Mersenne Twister algorithm , which is considered an industry standard for high-quality pseudo randomization.
• We use version MT19937 that produces a normally distributed sequence of 32-bit integers with a very long period of 2^ 19937 - 1, which is more than sufficient for all imaginable scenarios.
• Random numbers generated using this method are of a very high quality. The Random Number Generator has successfully passed multiple tests for statistical randomness, including the well-known NIST Statistical Test Suiteand Diehard tests and some of TestU01 Crush randomness tests.

Unlike Excel random functions, our Random Number Generator creates permanent random values that do not change when a spreadsheet recalculates.

As already noted, this advanced Random Number Generator for Excel offers a formula free (and consequently error-free :) way to create various random values such as:

• Random integers or decimal numbers, including unique numbers
• Random dates (workdays, weekends, or both, and optionally unique dates)
• Random text strings, including passwords of a given length and pattern, or by mask
• Random Boolean values of TRUE and FALSE
• Random selection from custom lists

Generate random numbers in Excel

With AbleBits Random Number Generator, creating a list of random numbers is as easy as clicking the Generate button.

Generating unique random integers

Generating random real numbers (decimals)

Create random dates in Excel

For dates, our Random Number Generator provides the following options:

• Generate random dates for a specific time period - you enter the bottom date in the From box and the top date in the To box.
• Include weekdays, weekends, or both.
• Generate unique dates.

Generate random text strings and passwords

A unique option provided by AbleBits Random Number Generator is creating random text strings by mask . This is a very useful feature for generating globally unique identifiers (GUID), zip codes, SKUs, and so on.

If you are interested to give our Random Generator a try, you are most welcome to download it below as part of our Ultimate Suite for Excel.

Available downloads

You may also be interested in.

• Random sort in Excel: shuffle cells, rows and columns
• How to select random sample in Excel
• How randomly select in Excel without duplicates
• How to round numbers in Excel - ROUND, ROUNDUP, ROUNDDOWN and other functions
• How to calculate CAGR (compound annual growth rate) in Excel - formula examples

Table of contents

59 comments

How can i use indian language (Gujarati) letters to generate random characters?

Hi! To generate random characters, you need to know the codes of these characters in the ASCII table. Generate these codes randomly as described in the article above. Convert these codes to characters using the CHAR function. For example,

=CHAR(RANDBETWEEN(64,90))

I recommend reading this guide: How to convert column number to letter in Exce l

I have 4 numbers that need randomized. 25-50-75-100. I need 7500 of the 25 3750 of the 50 1875 of the 75 and 1875 of the 100.

Is there a formula that can randomize specific values with specific quantities?

Hi! Looks like this is not possible with the standard Excel options. You can write all these 250 numbers on the worksheet, select that range of cells, and use tool Random Generator - Shuffle Cells . It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free .

The closest I could get was with assigned percentages. 50%-25%-12.5%-12.5%. (F column)

I used =SUM($F$2:F2) to get a Cumulative percent of 50%-75%-88%-100% (G column)

and then used this formula to get my result. It's not perfect but close enough for me to change a few to get my exact specific quantities.

=INDEX(E$2:E$5,COUNTIF(G$2:G$5,"<="&RAND())+1) (E column has the values of 25-50-75-100)

How can I apply the random formula to meet the following conditions. 1. a series of cells must equal a certain sum 2. They must be whole numbers. Exampe: I want a column of blank cells to randomly add up to 20 every time I click generate? Thanks

I did random number between 3-18. I want to know that can I do the number which I want to start to random. How can I control these number unchange?

How can i have a random numbers those satisfies row and column totals in a contingency table.

Hi! Sorry, it's not quite clear what you are trying to achieve.

205 227 152 153 199 150 174 237 231 ? ? ? how to find next random number by using previous random number between 150 to 250

Hi! If you tell me what the pattern is in these numbers, I will try to help write the formula.

Hi We want to calculate random 8 values between 1-50, can you guide how to calculate?

Hi! Have you read this article carefully? Use RANDBETWEEN function.

i need this type of code

tsm 1923 tsm 9207

Hi! What do you want to calculate exactly? Your question is not entirely clear, please specify.

I have menus designed 2 per page that I want to print out with 35 different room numbers in sequence. This is out of my knowledge base and would love some help.

I need a solution for an eprime script. In my experiment I called images to appear randomly, but I dont want that a determined imaged will be selected more than one time. How can I randomize or pseudorandomize this task?

Thank you in advance.

Hello! Please check out the following article on our blog, it’ll be sure to help you with your task: How to generate random numbers in Excel with no repeats

Expel 132 123 213 231 321 312 If I register a three digit number it is automatic I need to change how I do this

Hello! I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result. It’ll help me understand it better and find a solution for you. Thank you.

I want to generate a series of A’s and B’s but never more than 2 of A or B is it possible?

Hello! I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the expected result. It’ll help me understand it better and find a solution for you. Thank you.

Hola, quisiera saber cuál es la diferencia entre aleatorio y pseudoaleatorio?

Hello Johan!

Random numbers are a sequence of numbers selected in a random way. Pseudo-random numbers are used instead of random ones. They are generated by a computer program using a predefined complex algorithm. For an ordinary user they look like random, but still they can be predicted if you know that complex algorithm. Pseudo-random numbers are used in all computer programs.

What is the max capacity of RAND() in Excel - i.e. what number of i.i.d. random numbers one can safely simulate without running into autocorrelation of them (breaking statistical tests on i.i.d.)? Thanks

could you please any one help how to random using below string

SS YS TD AR KP HH ET GD JJ KA WC

Is there a way to use the RAND function with exceptions? I want to generate random numbers between 93 and 118 but they can not include 103 or 114. (Also each number can only appear a maximum of twice per row). Is there a better function to use?

Is it possible if A=24 then B=random value less than 12 and C=random value less than 12 A=a+b

1 180 2 180 3 360 4 180 5 180 6 360 7 180 8 180 9 360 10 540 11 180 12 180 13 180 14 720 15 540 16 360 17 180 18 180 19 360 20 540 21 360 22 180 23 360 24 180 25 720 26 900 27 1080 28 3600 29 9000 30 5400 31 2700 32 1800 33 1260 34 540 35 900 36 720 37 540 38 180 39 540 40 360 41 180 42 360 43 540 44 180 45 360 46 540 47 720 48 180 49 180 50 180

40860 want in cell two to select rendomly no so that there sum may be 18000 and 16200 like wise is this possible by any formula

how to generate random number (real) in a specified range that will have a Ppk value of at least 1.3

Nincals: Is something like this what you want? =RANDBETWEEN(1.3*10,5*10)/10 The lowest value is 1.3, the highest value is 5 and the numbers will be decimal numbers. This idea is discussed in the above article. You can format the cells as numbers to show one decimal place.

What is the best formula when rolling (many) dice: a) The TRUNC(RAND) function or b) The INT(RAND) function or c) are they both suitable?

Nice information about randam password generator.

wow i like this app

Hello can you please help me to find the next number in Random Number Generator when the last numbers was 351 and 610 then what is the next number? please help me.

Hi there, i want to generate random number between 2 number in decimal point form (between 0.1 and 0.6), but always got value of 1. Is there any possible way to make the random number generate between these 2 values?

I have a list of Latitude and Longitude values in excel, is there a way to build a script that will take the column of data and randomize the last 2 digits past the decimal? This would in effect jitter the points by a few meters, and keep points from all falling on top of each other on a map. Anyone know how to accomplish this?

I have a small VBA code setup to generate 4 random integers (using INT (RAND()*(10-1)+1) ) between 1 and 10 every time I push a button on the worksheet. What I noticed is that every time I open the spreadsheet it cycles through the same order of numbers. Meaning the first time I click the button I'll get the same 4 random numbers, the second time I push the button I'll get the same set of numbers I got the last time I opened the sheet and presssed it twice.

Hi, Would you please help me find the correct formula to generate a random number between 1500 and 4000 which ends in a zero (either round up or down, doesnt matter). Kind regards

Hi, Martin,

try the following formula: =ROUND(RANDBETWEEN(1500,4000),-1)

You can find more about ROUND function here .

NOT TRUE; There is no way to seed either Excel RAND or RANDBETWEEN function, which are rumored to be initialized from the computer's system time. Technically, a seed is the starting point for generating a sequence of random numbers. And every time an Excel random function is called, a new seed is used that returns a unique random sequence. In other words, when using the random number generator in Excel, you cannot get a repeatable sequence with the RAND or RANDBETWEEN function, nor with VBA, nor by any other means.

Use Rnd(-N) to generate a reproducible number. Feed the previous results as a negative number to the succeeding Rnd calls, x1=RND(-Seed); x2=RND(-x1) etc.. to produce a repeatable sequence from a seed number.

Thank you for your comment.

Right, in Excel VBA you can initialize a random sequence using Rnd(-N), but as noted in the tutorial, it cannot be done with the regular worksheet functions RAND or RANDBETWEEN. Or course, you can create your own user-defined function that will use RND to generate reproducible numbers.

Hi, is there a possible way to combine the randbetween function with current day. In other words use a random number with current day to create an unique number using only the year and the day only. Thanks!

use this formula, i hope it will help you

=text(today(),"DD ")&randbetween(100,9999)

Trying to randomize the 55- 2 digit combinations for 0-9. For example: 0,0 0,1 and so on through 9,9. 0,1 and 1,0 are considered the same combo. Gotta be easier than 55 slips of paper?? Can you help?

Very nice article. Note that from Excel 2010 onwards, Excel RAND() function uses the Mersenne Twister algorithm.

3 cells wide 100 down that have my own numbers that don't change.

3 cells wide beside it and 100 down as well but random numbers between 1 and 20 "no decimals"

3 Cells wide 100 down as a match column that highlights any number of single pairs or tripples

Can you paste or send to my email please

I need for my work so please help me :(

I need random incremental decimal values in a range.

range 22.1234 to 79.1234

and from row 1 to row 300.

how to do it........?

how to generate the 100 random numbers equally distributed between 23 and 87 using excel

i am working on a norm.inv(rand(),Mean,Std dev) using these mean and deviation values to generate my random number. however, the deviation may bring the value across 0, negative, which is not possible for my needs. is it possible to put a bounds (randbetween) while still asking it to use the mean and dev. to create the number?

Hi Svetlana, I have a requirement in excel when I simulate random variables in excel particular cell, if the cell value is within a specified range, I want to copy that value to a new cell each time it simulates, I want to retain the previous value to calculate the mean, Kindly help with the requirement of copying the simulated value to new cell I am running the simulation 10k times

When I say '5 rows', I mean as many times as posible for each number in the ninety numbers.

Urgently pls.

I want to generate a lotto chart of 1 - 90 in 5 unrepeated rows involving all numbers such that every winning result of 5 numbers must comform with at least one possible and correct row.

Do you know how to change the root of the generator? has excell a simple method to do that? thanks.

Hello, Yasser,

If you mean the randomization algorithm, it cannot be changed. That's why we created Random Generator for Excel - to use a different reliable algorithm.

Is the specific method of generating RAND() in Excel known, or is it a black box? In other words, how random is RAND()?

The RAND function generates pseudo-random numbers from the Uniform distribution, aka rectangular distribution, where there is equal probability for all values that a random variable can take on.

I have several days extending for over 4 years I need to generate one randon time for each date for the next 4 years

I have a date field and a time field in my excel table For each day there are multiple times 20-1-2009 6:00 20-1-2009 7:00 20-1-2009 8:00

so on till 20-1-2009 24:00 from each day I need to generate one randon time from all times I have.

Can you help

Post a comment

RAND function

This article describes the formula syntax and usage of the RAND  function in Microsoft Excel.

Description

RAND returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.

The RAND function syntax has no arguments.

To generate a random real number between a and b, use:

=RAND()*(b-a)+a

If you want to use RAND to generate a random number but don't want the numbers to change every time the cell is calculated, you can enter =RAND() in the formula bar, and then press F9 to change the formula to a random number. The formula will calculate and leave you with just a value.

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2 , and then press Enter . You can adjust the column widths to see all the data, if needed.

Need more help?

You can always ask an expert in the Excel Tech Community  or get support in  Communities .

Mersenne Twister algorithm

RANDBETWEEN function

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

5 Ways to Generate Random Numbers in Excel

Not every user will have a need for random numbers in Excel. Most people work with fixed numbers and formulas and may have no need for random numbers to appear in their reports.

However, a random number generator does have a huge use when working with different scenarios on a set of data or when performing various statistical analyses.

A financial model may use a stochastic simulation that is dependent on probabilities. The model may need to be run thousands of times, but with the random number generator providing the parameters of each simulation.

Whatever your need for random numbers, Excel has several ways to generate them.

In this post, I’ll show you all the methods you can use to insert random numbers into your workbooks.

Generate Random Numbers with the RAND function

The first way I will show you is the easiest way to generate random values in Excel.

There is a very simple RAND function that requires no parameters and will generate a random number between 0 and 1.

Syntax for the RAND Function

This function has no required or optional arguments. The function is always entered with an empty set of parenthesis.

This function will generate a decimal random number between 0 and 1, but not including 0 or 1.

Repeated values are possible but unlikely since the RAND function produces numbers from a continuous range of numbers.

The values that are returned will follow a uniform distribution. This means that any number between 0 and 1 is equally likely to be returned.

Generate Random Numbers Between Any Two Numbers

A decimal number between 0 and 1 may not be too useful if you need numbers between 1 and 10.

But you can use a simple formula involving the RAND function to generate random numbers between any two numbers.

In general, you can create a random number between X and Y by using the above formula.

For example, to generate numbers between 1 and 10 you can use the above formula.

This multiplies the random number generated by 9 and then adds 1 to it. This will produce decimal numbers between 1 and 10.

Generate Random Integer Numbers Between Any Two Numbers

Another possible need you may encounter is to generate random whole numbers between two given numbers. This can also be done using a simple formula.

In general, you can use the above formula to generate random integer numbers between two values X and Y.

For example, the above formula will create random integer numbers between 1 and 10.

This is the same formula as before, but using the ROUND function to round to zero decimal places.

You can copy this formula down the column on the spreadsheet, and if you keep pressing F9 to re-calculate, you will see various combinations of numbers from 1 to 10.

Since the set of possible numbers is discrete, the random numbers generated may well be duplicated in the list, depending on what the minimum and maximum are of the range.

This also works for producing negative numbers. Suppose you need to generate random integer numbers between -3 and 4, then the above formula will be what you need.

Multiplying the RAND function by 7 will produce random numbers between 0 and 7. Add -3 to the result and round to zero decimal places, and this will give the range of random numbers of -3 to 4.

Generate Random Numbers using the RANDBETWEEN Function

Excel has a useful function for generating random numbers within a range of an upper and lower number.

This is easier to use than using the RAND function as it includes extra operators to arrive at your specific range.

Syntax for the RANDBETWEEN Function

• bottom is the lower range for the values to return.
• top is the upper range for the values to return.

Both of these arguments are required.

This function will produce random integer numbers between the bottom and top values. This function will also return the upper and lower limits as possible values as it’s not strictly between in this function.

Example with the RANDBETWEEN Function

For example, if you wanted random numbers between -3 and 4, as in the previous example, you can use the above formula.

Note that the RANDBETWEEN function can only produce integer numbers. There is no way of making the function produce decimal numbers. However, it is considerably less complicated than using the RAND function with operators to achieve the same result.

Generate Random Numbers with the RANDARRAY Function

Usually, it’s the case that you don’t want just a single random value but an entire set of random values.

The RANDARRAY function is the perfect solution for this.

It will populate a range of cells with an array of random numbers, which can be very powerful.

This function is only available on the Microsoft 365 version of Excel.

Syntax for the RANDARRAY Function

• Rows is the number of rows to return.
• Columns  is the number of columns to return.
• Min  is the minimum value for the random numbers.
• Max is the maximum value for the random numbers.
• Whole_Number is TRUE to return whole numbers, and FALSE to return decimal numbers.

All the arguments are optional for this function.

If no parameters are included, you will get a single random number with decimal places, in the same way as the RAND function.

Example with the RANDARRAY Function

To generate an array of 4 rows and 3 columns of whole random numbers between 6 and 14 you can use the above formula.

This will produce an array of values. Notice the blue border around the numbers? These are all produced from a single formula!

Note that the top left-hand corner of the array is always anchored on the cell that the formula is in. Pressing F9 to recalculate the spreadsheet will change all the numbers in the array.

If you do not put a minimum or maximum value, the default of 0 to 1 will be used.

The minimum value must be less than the maximum value otherwise there will be a #VALUE! error.

The array will automatically resize if you change either the rows or columns parameters in the RANDARRAY formula. This is why they’re known as dynamic arrays.

Warning : If there is already data in one of the cells in the output range that you have entered, you will get a #SPILL! error.  No data will be overwritten.

Generate Random Numbers with the Analysis Tools Add-In

There is another method that can be used to insert random numbers without using a formula.

You can use an add-in to create random numbers. Excel comes with an Analysis Tool Pak add-in, but you will need to install it before you can use it.

Install the Analysis Toolpak

Here are the steps to install the Analysis Tool Pak add-in.

• Click on the File tab in the ribbon.

• In the lower left-hand pane of the window, scroll down and click on Options . You can also use the keyboard shortcut Alt , F , T from the spreadsheet window to open the Options window.

• In the left-hand pane of the pop-up window, click on Add-Ins.
• At the bottom of the main window displayed, select Excel Add-ins from the dropdown and click on the Go button.

• This will display a pop-up window containing all available add-ins for Excel. Check the box for Analysis ToolPak and then click OK .

• On the Excel ribbon, on the Data tab, there is now an extra group called Analysis with one button called Data Analysis .

Generate Random Numbers with the Analysis Toolpak

Click on the Data Analysis button in the Analysis group.

This will display a pop-up window. Scroll down and select the Random Number Generation option and then click OK .

A new pop-up window will appear where you can enter your parameters to generate the random numbers.

There are several settings that can be customized.

• Number of Variables This is the number of columns of random numbers that you want in your output table. If left blank, then all columns will be filled in the output range that you specify.
• Number of Random Numbers This is the number of rows of random numbers that you want to generate. If left blank, the output range that you specify will be filled.
• Distribution  You can select several distribution methods from the drop-down such as uniform or normal distribution. Different options will become available in the Parameters section depending on your selection here.
• Parameters  Enter the values to characterize the distribution selected.
• Random Seed This is optional and will be the starting point for the algorithm to produce the random numbers. If you use the same seed again, it will produce the same random numbers. If left blank, it will take the seed value from the timer event.
• Output Range Enter the upper left cell of where the table is to be constructed in the spreadsheet. If you have left the Variables parameter blank, then you will need to specify an entire range. Note that existing data in that range will be overwritten.
• New Worksheet Ply This option will insert a new worksheet within the workbook and will paste the results at Cell A1. Enter a sheet name in the adjacent box, otherwise, a default name will be used.
• New Workbook This will create a new workbook and paste the results into cell A1 in the first sheet.

Press the OK button and Excel will insert the random number according to the selected options.

Notice that unlike the formula methods previously shown, these numbers are hardcoded and will not change when you refresh calculations in the workbook.

Generate Random Numbers with VBA

VBA (Visual Basic for Applications) is the programming language that sits behind the front end of Excel, and this can also be used to generate random numbers.

However, it is more complicated than simply entering a formula into a cell in Excel, and you do need some programming knowledge to use it.

To open the VBA editor, use the Alt + F11 keyboard shortcut.

In the left-hand pane of the window (Project Explorer), you will see the workbooks that are open (including add-ins) and the sheets available.

On the menu at the top of the window, click on Insert and then click on Module . This will add a module window to the current spreadsheet. Paste or add the following code to the module.

Press F5 to run this, and a message pop-up will appear in Excel with a random number displayed. Press OK and you will return to the code window.

Run the code again and a different random number will be displayed. The random number will be between 0 and 1, but will not include the values of 0 or 1.

You can also give the Rnd function a parameter, which is a seed for the starting point of the algorithm to produce the random numbers.

If the seed value is set to a negative number or zero, then the same random number will be displayed each time.

Using VBA functions, you can emulate all the functionality of the front-end methods that have been covered in this article.

For example, if you wanted to generate whole random numbers between 3 and 10, then you would use the following above code.

This code multiplies the random number, by 7, and then adds 3 to it, and then rounds to zero decimal places.

Suppose that you then wanted to display your random numbers in the grid. You can do this with the following code.

This code uses a For Next loop to iterate 5 times through the random number calculation and enter the results in a column of cells starting at cell A1.

Remember that any data already there will be overwritten, and there is no warning or undo feature available. Save any previous work beforehand!

There is also a VBA function called Randomize . You can use this before the Rnd function to reset the seed value to the timer event, or to any parameter given.

Generate Random Numbers without Duplicates or Repeats

You may well have a situation where you want to generate a range of random numbers, but you do not want to see any duplicate values appearing.

You may want to select 3 random numbers between the numbers from 1 to 10, but where each of the 3 selected numbers is unique.

You could generate random numbers with the RANDBETWEEN function and then use the Excel function Remove Duplicates from the ribbon, but this still may not give you all the numbers required.

There are several possible solutions available.

Solution with RANK.EQ and COUNTIF Functions

If you don’t have access to the RANDARRAY function in Excel, then you can use a combination of RANK.EQ and COUNTIF to get unique random numbers.

You can create your random numbers using RANDBETWEEN and then use a formula in the next column to rank them thereby giving you a randomly sorted sequence from 1 to 10.

In cell B2, enter the above formula . Copy this formula down so that there are 10 rows of random numbers going down to cell B11.

You will notice that some numbers may be duplicated and some are not shown at all.

You can then use the RANK.EQ function to rank them so as to create a sequence from 1 to 10 but that is sorted randomly.

In cell C2, enter the above formula.

Note that there are absolute references used (the $ signs) so that formula references stay fixed as you copy the formula down.

Copy this formula down to cell C11, and this will display all the numbers between 1 and 10, but in random order.

To explain this formula in more depth, it uses two functions RANK.EQ and COUNTIF .

• Number is the number that we want to find the rank of in the array.
• Ref is the array where we want to search for the number.
• Order is optional and allows you to find the rank in either ascending or descending order. If its omitted then ascending order is used.

The RANK.EQ function returns the rank of a number within an array of numbers.

• Range is the range that is being searched for instances of the criteria.
• Criteria is the value to match within the range.

The COUNTIF function counts the number of cells based on a given criterion. In this case, it is counting how many times a given random number has appeared in the list.

For each random number the RANK.EQ function will determine its ranking position relative to the other random numbers. But if the random numbers contain duplicates, then they will create a tied ranking.

The COUNTIF function will compensate for any ties in the ranking and will add one to the rank for each time the random number has previously appeared.

This creates a unique ranking where ties don’t get the same rank.

Since this rank is based on a set of random numbers the result is the same as randomizing a list of numbers from 1 to 10.

Now, if you only want 5 non-repeating numbers, you only need to take the first 5 from the ranking list.

Solution with VBA

You could also use VBA to generate a string of random numbers from 1 to 10 without duplicates.

This code iterates through values from 1 to 5, generating a random number between 1 and 10 each time.

It tests the random number to check if it has already been generated. This is done by concatenating successful numbers into a string and then searching that string to see if the number has already been used.

If it has been found, then it uses the label Repeat to go back and re-generate a new number. This is again tested that it has not already been used. If it is a new number, then it is added to the sheet.

Solution with Dynamic Arrays

If you have dynamic arrays in Excel, then there is a single formula method to avoid repeating values.

Suppose you want to return 5 numbers from the sequence 1 to 10. You want each number selected to be unique.

This can be done using a combination of the SEQUENCE , SORTBY , RANDARRAY , and INDEX functions.

The above formula creates a sequence of numbers from 1 to 10.

It then sorts them in a random order using the SORTBY function and sorting on a column of random numbers generated by the RANDARRAY function. The effect is to sort the sequence in random order.

Now if you want to get 5 random and unique numbers you only need to take the first 5 numbers from the randomly sorted sequence.

This is exactly what the INDEX function does! This part of the formula will return the first 5 numbers from the randomly sorted sequence.

There are several ways to generate random numbers in Excel.

Whether you need whole numbers, decimals, or a range of random numbers with an upper and lower limit, the facility is available. Excel is extremely versatile on this topic.

However, bear in mind that these numbers are pseudo-random numbers generated by an algorithm.

Although the random number generator passes all the tests of randomness, they are not true random numbers.

To be a truly random number, it would have to be driven by a random event happening outside the computer environment.

For most purposes of constructing general simulations and statistical analysis, the Excel random number generator is considered fit for the purpose.

Have you used any of these methods for generating random numbers in Excel? Do you know any other methods? Let me know in the comments below!

About the Author

John MacDougall

Subscribe for awesome Microsoft Excel videos 😃

I’m John , and my goal is to help you Excel!

You’ll find a ton of awesome tips , tricks , tutorials , and templates here to help you save time and effort in your work.

• Pivot Table Tips and Tricks You Need to Know
• Everything You Need to Know About Excel Tables
• The Complete Guide to Power Query
• Introduction To Power Query M Code
• The Complete List of Keyboard Shortcuts in Microsoft Excel
• The Complete List of VBA Keyboard Shortcuts in Microsoft Excel

Related Posts

10 Ways to Count the Number of Occurrences in Microsoft Excel

Apr 11, 2024

Wondering how to count the number of occurrences in Excel? You've reached the...

10 Ways to Make a Negative Number Positive in Microsoft Excel

This Microsoft Excel tutorial helps you to learn how to make a negative number...

How to Find and Replace in Microsoft Excel

Mar 4, 2024

This effortless Microsoft Excel tutorial shall explain how to find and replace...

Get the Latest Microsoft Excel Tips

Follow us to stay up to date with the latest in Microsoft Excel!

• PRO Courses Guides New Tech Help Pro Expert Videos About wikiHow Pro Upgrade Sign In
• EXPLORE Tech Help Pro About Us Random Article Quizzes Request a New Article Community Dashboard This Or That Game Popular Categories Arts and Entertainment Artwork Books Movies Computers and Electronics Computers Phone Skills Technology Hacks Health Men's Health Mental Health Women's Health Relationships Dating Love Relationship Issues Hobbies and Crafts Crafts Drawing Games Education & Communication Communication Skills Personal Development Studying Personal Care and Style Fashion Hair Care Personal Hygiene Youth Personal Care School Stuff Dating All Categories Arts and Entertainment Finance and Business Home and Garden Relationship Quizzes Cars & Other Vehicles Food and Entertaining Personal Care and Style Sports and Fitness Computers and Electronics Health Pets and Animals Travel Education & Communication Hobbies and Crafts Philosophy and Religion Work World Family Life Holidays and Traditions Relationships Youth
• Browse Articles
• Learn Something New
• Quizzes Hot
• This Or That Game New
• Train Your Brain
• Explore More
• Support wikiHow
• About wikiHow
• Log in / Sign up
• Education and Communications
• Mathematics
• Probability and Statistics

How to Create a Random Sample in Excel

Last Updated: January 23, 2024

Assembling the Data

Creating a random sample, sorting the sample.

This article was co-authored by wikiHow staff writer, Jack Lloyd . Jack Lloyd is a Technology Writer and Editor for wikiHow. He has over two years of experience writing and editing technology-related articles. He is technology enthusiast and an English teacher. This article has been viewed 454,796 times. Learn more...

This wikiHow teaches you how to generate a random selection from pre-existing data in Microsoft Excel. Random selections are useful for creating fair, non-biased samples of your data collection.

• Depending on the type of data you have, this process will vary. However, you should start all data in the "A" column.
• For example: you might place your users' names in the "A" column and their responses to a survey (e.g., "yes" or "no") in the "B" column.

• After doing this, any data that was in the "A" column will be relisted as being in the "B" column and so on.

• If Excel attempts to automatically format your "RAND" command, delete the formatting and re-type the command.

• For a Mac, you'll hold down ⌘ Command instead of Control .
• You can also right-click the "RAND" cell and then select Copy .

• Clicking the "A1" cell and highlighting from there can cause a sorting error.

• For example, if your data in columns "B" and "C" extends all the way down to cell 100, you would hold down ⇧ Shift and click "A100" to select all "A" cells from A2 to A100.

• Again, Mac users will need to hold down ⌘ Command instead of Control .

• This includes the random sample numbers to the left of your data as well.

• For example, if your data takes up two columns of 50 cells each, you would select "C50" while holding down ⇧ Shift .
• You can also click and drag your cursor from the top left corner to the bottom right corner of your data (or vice versa) to highlight it.

• If you're using a Mac, you can click using two fingers (or hold down Ctrl and click) to bring up the context menu.

• Select the first, last, or middle half of the data . If your number of data points is too large to warrant this, you can also settle on a lower fraction (for example, the first eighth of the data).
• Select all odd- or even-numbered data . For example, in a set of 10 data points, you would either pick numbers 1, 3, 5, 7, and 9, or 2, 4, 6, 8, and 10.
• Select a number of random data points . This method works best for large sets of data where picking half of the information is too ambitious.

Community Q&A

• If you don't have Microsoft Excel , there are other free programs online (such as Google Sheets or Outlook's Excel app) that may allow you to create a random sample. Thanks Helpful 0 Not Helpful 0
• Microsoft makes an Excel app for iPhone and Android platforms so you can create spreadsheets on-the-go. Thanks Helpful 0 Not Helpful 0

• Failing to use a random sample when looking for results (e.g., sending out a survey after updating a service) may cause your answers to be biased--and, therefore, inaccurate. Thanks Helpful 0 Not Helpful 0

You Might Also Like

About This Article

• Send fan mail to authors

Is this article up to date?

Featured Articles

Trending Articles

Watch Articles

• Terms of Use
• Privacy Policy
• Do Not Sell or Share My Info
• Not Selling Info

Keep up with the latest tech with wikiHow's free Tech Help Newsletter

How-To Geek

How to randomize a list in microsoft excel.

Make your list items appear in random order!

Quick Links

How excel's list randomization works, use rand to randomize list items in excel.

If you want to rearrange items in a list in random order, you can use Microsoft Excel's

function. The function generates random numbers , which allow you to then randomize your list items. Here's how to do that in your spreadsheet.

Related: How Computers Generate Random Numbers

In Microsoft Excel, you don't have a feature to randomize your list items in a click. Instead, you have to rely on the app's

function that generates a series of random numbers . You then use these random numbers as the base for randomizing your list.

Related: How to Generate Random Numbers in Microsoft Excel

Basically, you add the

function's column next to your list, so that you have a random number next to each item on your list. Then, you sort these random numbers in ascending or descending order, which causes your list items to rearrange, too.

Once your items are randomized, you may delete the

function column as you don't need it anymore.

To start the randomization process, first, open your spreadsheet with Microsoft Excel.

In your spreadsheet, select the row next to the first item on your list.

In the selected row, enter the following function and press Enter:

In the cell where you typed the function, you will see a random number. To automatically fill random numbers next to each item on your list, then in this cell's bottom-right corner, double-click the small dot.

You now have a random number next to each item on your list.

To begin randomizing your list items, in Excel's ribbon at the top , click the "Home" tab.

In the "Home" tab, click Sort & Filter > Sort Smallest to Largest. You may choose "Sort Largest to Smallest" as well if you want. It doesn't matter what option you choose as your list items are going to be randomized in either case.

Each time you want to rearrange your items in random order, select the "Sort Smallest to Largest" or "Sort Largest to Smallest" option.

Your random numbers will be sorted , and when this happens, Excel will put your list items in random order, too.

You now have your list in completely random order. Now that you don't need the generated random numbers anymore, get rid of them by right-clicking the column's letter at the top and choosing "Clear Contents."

And that's all there is to applying Excel's magic to come up with random list order.

If you use Google Sheets, you can generate random numbers in Sheets , too.

Related: How to Generate Random Numbers in Google Sheets

How to Generate Random Numbers in Excel

There may be cases when you need to generate random numbers in Excel.

For example, to select random winners from a list or to get a random list of numbers for data analysis or to create random groups of students in class.

In this tutorial, you will learn how to generate random numbers in Excel (with and without repetitions).

Generate Random Numbers in Excel

There are two worksheet functions that are meant to generate random numbers in Excel: RAND and RANDBETWEEN.

• RANDBETWEEN function would give you the random numbers, but there is a high possibility of repeats in the result.
• RAND function is more likely to give you a result without repetitions. However, it only gives random numbers between 0 and 1. It can be used with RANK to generate unique random numbers in Excel (as shown later in this tutorial).

Generate Random Numbers using RANDBETWEEN function in Excel

Excel RANDBETWEEN function generates a set of integer random numbers between the two specified numbers.

RANDBETWEEN function takes two arguments – the Bottom value and the top value. It will give you an integer number between the two specified numbers only.

For example, suppose I want to generate 10 random numbers between 1 and 100.

Here are the steps to generate random numbers using RANDBETWEEN:

• Select the cell in which you want to get the random numbers.
• In the active cell, enter =RANDBETWEEN(1,100).
• Hold the Control key and Press Enter.

This will instantly give me 10 random numbers in the selected cells.

While RANDBETWEEN makes it easy to get integers between the specified numbers, there is a high chance of repetition in the result.

For example, when I use the RANDBETWEEN function to get 10 random numbers and use the formula =RANDBETWEEN(1,10), it gives me a couple of duplicates.

If you’re OK with duplicates, RANDBETWEEN is the easiest way to generate random numbers in Excel.

Note that RANDBETWEEN is a volatile function and recalculates every time there is a change in the worksheet. To avoid getting the random numbers recalculate, again and again, convert the result of the formula to values .

Generate Unique Random Numbers using RAND and RANK function in Excel

I tested the RAND function multiple times and didn’t find duplicate values. But as a caution, I recommend you check for duplicate values when you use this function.

Suppose I want to generate 10 random numbers in Excel (without repeats).

Here are the steps to generate random numbers in Excel without repetition:

• Select the cells in which you want to get the random numbers.
• In the active cell, enter =RAND()
• Select all the cell (where you have the result of the RAND function) and convert it to values .

Now you can use the values in column B as the random numbers.

Note: RAND is a volatile formula and would recalculate every time there is any change in the worksheet. Make sure you have converted all the RAND function results to values.

Caution: While I checked and didn’t find repetitions in the result of the RAND function, I still recommend you check once you have generated these numbers. You can use Conditional Formatting to highlight duplicates or use the Remove Duplicate option to get rid of it.

You May Also Like the Following Excel Tutorials:

• Automatically Sort Data in Alphabetical Order using Formula .
• Random Group Generator Template
• How to Shuffle a List of Items/Names in Excel?
• How to Do Factorial (!) in Excel (FACT function)

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

3 thoughts on “How to Generate Random Numbers in Excel”

The link or the Ebooks do not send to my email

Just a trick, RAND and RANDBETWEEN are volatile functions, so each time there’s a change they will be updated. To avoid this, you can create a Pivot Table referencing the cells with the random values (functions) and then reference the Pivot Table values. This way the random values won’t change until the Pivot Table is updated.

Leave a Comment Cancel reply

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

Privacy Policy  | Sitemap

Twitter | Facebook | YouTube | Pinterest | Linkedin

FREE EXCEL E-BOOK

How to Generate Random Data in Excel (9 Easy Methods)

In Microsoft Excel, we may need to generate random data when working with different scenarios and performing various analyses. In this article, I am going to explain how to generate random data in Excel with some quick methods.

How to Generate Random Data in Excel: 9 Suitable Methods

Excel has some built-in methods for generating random data. The following methods are the easiest ways to generate random data.

1. Apply RANDBETWEEN Function to Generate Random Data in Excel

With the help of the RANDBETWEEN function , you can generate some random numbers within an upper and lower number.

• Select Cells for random data. I have selected cell ( B4 ) to cell ( B12 ).
• Apply the formula-
• The RANDBETWEEN function returns a random integer number between the specific numbers.

• Press CTRL+Enter .
• Here we got our random integer numbers between 1 to 100 .

Sometimes duplicate values will appear. If you don’t have problems with duplicates then check the following steps.

• Select cells for random numbers.
• The RANDBETWEEN function returns integers between the given numbers.

You will find some duplicate values within the given range.

2. Use RAND Function to Generate Random Data in Excel

With the RAND function generating random data is quite easy. Decimal numbers can be generated with the RAND function.

2.1 Generate Data Between 0 and 1

The main advantage of using the RAND function is it has no optional arguments. Following I am going to describe how to generate data between 0 and 1 .

• Select cells for the desired output.
• Apply the Formula-
• The RAND function will return a random number between 0 and 1 .

• Thus you will get random numbers between 0 and 1. It’s that easy.

2.2 Generate Data Between Any Two Numbers

Now, Let’s generate random data between any two numbers.

• Select cells .
• The RAND function returns random numbers within the given range.

• Click CTRL+Enter .

• This way you will get your desired random data between 1 to 10 .

2.3 Produce Integer Data Between Any Two Numbers

The RAND function produces decimal data automatically. Sometimes it becomes difficult to calculate. For this, I will show you how you can produce integer data between any two numbers with the RAND function.

• Select cells from B4 to B13 .
• The RAND function returns a random number within the range.
• The ROUND function rounds a number.
• Press CTRL+Enter to get the round values.

As you can see, we got our random rounded numbers within the range.

This same method can be applied to negative numbers too. Suppose you need to generate random integer data between -5 to 10 .

• Select cells to generate random numbers.
• The ROUND function rounds up to the nearest integer.
• The RAND function produces a random number between the upper and lower values.

• Press CTRL+Enter to get the result in all cells.

• Thus you can get your expected result with negative integers.

3. Combine RAND and RANK Functions to Get Unique Random Data

Generally applying the RAND function we will not get duplicate values. But to be on the safe side, we may need to check for duplicates. With the combination of the RAND and RANK functions, you will get your unique random data. Check out the following.

• Select cells to get random data.
• The RAND function returns a random number between 0 and 1 .

• Select the data that we got by applying the RAND function.
• Press: CTRL+C to copy.

• Click the right button of the mouse and Paste as values.

• Here, we converted our data into values. So that it won’t change.

• Select a new column to get unique data.
• The RANK.EQ function returns the rank of a number against a list of other numeric values.

• Click CTRL+Enter to get unique data from the previous column.

Thus we will get our unique random values by using the RANK function.

4. Perform RANDARRAY Function to Generate Random Data in Excel

If you want to create a full set of random data in Excel then the RANDARRAY Function is the perfect solution for you. But It will work if you have Excel 365 or Excel 2021.

• Select some rows and columns to get a full set of random data.
• Apply the following formula-
• The RANDARRAY function returns an array of random numbers between 0 and 1 .

This way you will get your random data quickly in Excel 365.

5. Combine VLOOKUP and RANDBETWEEN Functions to Generate Random Data in Excel

In the previous methods, we have generated random numbers. In Excel, you can get randomized text also. With the combination of the VLOOKUP function and the RANDBETWEEN function, I am sharing with you a quick approach to getting random text in Excel.

Suppose we have a dataset of a fruit shop containing Product ID and Product name .

• Select the dataset and click the right button on the mouse.
• From the options box, select “ Define Name ”.

• A new window will appear named “ New Name ”.
• In the “ Name ” section type Fruits .

• Select a column where random fruit names will be shown.
• The RANDBETWEEN function distributes data within the given upper and lower values.
• The VLOOKUP function searches for a certain value and returns the value from a different column in the same row.

• Now, drag down the Fill Handle to get random fruit names in the column.

This is the quickest way to generate random text in Excel.

6. Apply the Data Analysis Toolpak to Generate Random Data in Excel

We have used different functions to get random data in Excel. But Excel has a built-in ToolPak to generate random data. In the following, I am going to explain generating random data by applying a “ Data Analysis ” ToolPak.

• Choose the “ Data ” ribbon and go to “ Data Analysis ”.

• A new window will appear-” Data Analysis ”.
• Select “ Random Number Generation ” from the analysis tools.

• From the new window “ Random Number Generation ” input “ Number of Variables ” and “ Number of Random Numbers ” as per your demand.
• Number of Variables indicates the number of columns that you want to add in.
• Number of Random Numbers means the number of data in each column.
• In the drop-down list select “ Uniform ”.
• Choose P arameters . Here I have chosen between 1 and 20 .
• Click on “ Output Range ” and select a cell from the workbook.

• As you can see, without using any type of function we got our random data with the “ Data Analysis ” ToolPak.

7. Run a VBA Code to Generate Random Data in Excel

VBA stands for Visual Basic for Applications. In this method, I am going to run a VBA code to generate random data.

• From the keyboard Press: ALT+F11 to open up the VBA Editor .
• Go to Insert > Module .

• In the Module window, write down the code-

• Now run the code .

• You will get a random number generated in the Excel sheet.

The random number that we got from the macro code is a decimal value. In many cases, we may need round values generated. For that follow the following steps.

• Select “ Module ” from the “ Insert ” ribbon.
• Apply the following code in the module window.

• Click Run to run the code.
• Here you will see we have successfully generated a round number between 5 to 15 .

Think that you want to display your random data in the grid.

• In the Module window apply the code-

• Run the macro .
• Thus we will get our desired random data with VBA code simply.

8. Merge RANK.EQ and COUNTIF Functions to Generate Random Data without Duplicates

You can use the RANK.EQ and COUNTIF function to generate random data without duplicates if you don’t have access to the RANDARRAY function in Excel.

• Select some cells from a column. Here I have selected cells ( B4 ) to cells ( B13 ).
• The RANDBETWEEN function calculates a random number between two numbers.

• Press CTRL+Enter to generate random data between 1 to 20 .

Here we got our random data within the given limit. But if you check you will find some duplicate numbers too in the list. To get data without these duplicates first, we have to convert these data into values and then apply some functions.

• Select those data in the cells.
• Click the right button of the mouse and from the paste option select V alues .

• So, we got all our data converted into values.

• Now it’s time to get the unique values only.
• Select a new column where new unique data without duplicates will be shown.
• The EQ function calculates and returns the statistical rank of a given value.
• The COUNTIF function counts the number of cells in a given range.

Here you will see we generated random data without duplicates.

9. Create a Dynamic Array to Generate Random Data in Excel

In order to avoid repeating values, you can use dynamic arrays in Excel. This is supported only for Microsoft 365 .

• Select cells ( B4 ) to cells ( B13 ).
• The INDEX function returns the value at a given location in an array.
• The SORTBY function sorts the data between an array.
• The SEQUENCE function generates a list of sequential numbers.
• The RANDARRAY function returns random numbers between 0 and 1.

• Press CTRL+Enter on the keyboard.

By creating this dynamic array method we can generate random data within a very short time.

Things to Remember

• When applying the “ Data Analysis ” ToolPak method, you might not find it in the toolbar. You have to install it. Follow the instructions to install the ToolPak.

File > Options > Select “Analysis Toolpak” from “Add-ins” window > OK > Put Tick in the “Analysis Toolpak” > OK.

• The RANDARRAY and the SORTBY functions are only available in Excel 365. If you are not a user of this version, you cannot apply these functions.
• After getting the random data don’t forget to convert them into values. Otherwise, the data will keep changing with every click.
• The RANDBETWEEN function generates duplicate data.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Hope you enjoyed the article. If you find any problems don’t forget to share in the comment section below. Have a great day!

<< Go Back to Random Number in Excel | Randomize in Excel   |  Learn Excel

What is ExcelDemy?

Tags: Random Number in Excel

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

• User Reviews
• List of Services
• Service Pricing

• Create Basic Excel Pivot Tables
• Excel Formulas and Functions
• Excel Charts and SmartArt Graphics
• Advanced Excel Training
• Data Analysis Excel for Beginners

Advanced Excel Exercises with Solutions PDF