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

how to do random allocation in excel

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.

how to do random allocation in excel

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.

how to do random allocation in excel

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.

how to do random allocation in excel

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

how to do random allocation in excel

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

how to do random allocation in excel

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

how to do random allocation in excel

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

  • Click on the File tab in the ribbon.

how to do random allocation in excel

  • 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.

how to do random allocation in excel

  • 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.

how to do random allocation in excel

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

how to do random allocation in excel

  • 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

how to do random allocation in excel

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 .

how to do random allocation in excel

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.

how to do random allocation in excel

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.

how to do random allocation in excel

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.

how to do random allocation in excel

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.

how to do random allocation in excel

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.

how to do random allocation in excel

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.

how to do random allocation in excel

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.

how to do random allocation in excel

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

John MacDougall

Subscribe for awesome Microsoft Excel videos 😃

how to do random allocation in excel

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

how to do random allocation in excel

Related Posts

10 Ways to Convert Number to Text in Excel

10 Ways to Convert Number to Text in Excel

Feb 23, 2024

Do you want to learn how to convert number to text in Excel to appropriately...

8 Ways to Find External Links in Microsoft Excel

8 Ways to Find External Links in Microsoft Excel

Professional and collaborative Microsoft Excel workbooks can accumulate...

9 Ways to Reduce Excel File Size

9 Ways to Reduce Excel File Size

Read this Excel tutorial until the end to learn how to reduce Excel file size...

Get the Latest Microsoft Excel Tips

how to do random allocation in excel

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

How-To Geek

How to generate random numbers in microsoft excel.

Use a simple function, or get more advanced control with a generator tool.

Quick Links

Use the random number generator in excel, use the random number functions in excel.

If you need to generate one or more random numbers for business, education, or another purpose, you can do so right in Microsoft Excel. You can get random numbers  using either a random number generator tool or an Excel function.

Related: How to Generate Random Numbers in Google Sheets

With an add-in from Microsoft called Analysis ToolPak , you can perform many statistical and engineering operations such as finding a moving average . Another feature of the tool is a random number generator.

Add the Analysis ToolPak

To see if you already have the add-in, go to the Data tab and the Analysis section of the ribbon. Look for the Data Analysis button. If you have the button, you can skip down to using the tool.

Go to Data, Data Analysis

If you don't see the button, you can add it easily. Go to File > Options and select "Add-ins" on the left. At the bottom of the window, go to Manage and pick "Excel Add-ins." Click "Go."

Select Add-ins, Manage Excel Add-ins

When the Add-ins window opens, check the box next to Analysis ToolPak and click "OK."

Add the Analysis ToolPak

Use the Random Number Generator

Head to the Data tab and click "Data Analysis" in the Analysis section of the ribbon. When the window appears, select "Random Number Generation" and click "OK."

Select Random Number Generation

Starting at the top of the window, enter the number of columns you want to fill using the Number of Variables box. Then, enter the number of rows in the Number of Random Numbers box.

Add the number of columns and rows

This generator is advanced in that you can select the distribution you want to use from options like Bernoulli, Binomial, Patterned, and Discrete. Once you select the Distribution from the drop-down list, the Parameters section will update with the necessary information for you to complete.

Pick a Distribution

In the Random Seed field, you can enter a starting number (up to 9999) for the generator to use if you like. Then, choose one of the Output Options for where you want your random numbers to display.

Add a Seed and pick the Output

Click "OK" when you're ready and you'll receive your numbers.

Random numbers generated

Another option for generating random numbers in Excel is using a function. There are three functions you can use . Each time you recalculate or reopen the workbook, a new random number will be generated with these functions. They don't offer as many options as the Random Number Generator tool, but they're simpler to use.

Related: How to Find the Function You Need in Microsoft Excel

The RAND Function

function, you can generate a random number greater than or equal to zero and less than one. This provides you with decimal number options. But you can also get numbers going higher than one by tweaking the formula.

For a basic random number, enter the following and press Enter:

RAND function in Excel

For a random number greater than or equal to zero and less than 500, enter the following and hit Enter:

=RAND()*500

RAND function for less than 500

For a random whole number greater than or equal to zero and less than 500, enter the following and press Enter:

=INT(RAND()*500)

Whole number less than 500

The RANDBETWEEN Function

Maybe you want to generate a number that's between two specific numbers. In this case, you'd use the RANDBETWEEN  function .

For a random number between 10 and 100, enter the following and press Enter:

=RANDBETWEEN(10,100)

RANDBETWEEN positive numbers

For a random number between negative 10 and 10, enter the following and hit Enter:

=RANDBETWEEN(-10,10)

RANDBETWEEN with negative number

Related: How to Create Random (Fake) Datasets in Microsoft Excel

The RANDARRAY Function

For Microsoft 365 subscribers, the RANDARRAY function provides a set of random numbers. You can choose the number of rows and columns to fill with numbers. You can also select minimum and maximum values and specify whole numbers or decimals.

The syntax for this function is RANDARRAY(rows, columns, minimum, maximum, whole-decimal) where you enter True for a whole number or False for a decimal number as the last argument. All arguments are optional.

For a random array of numbers that cover three rows and four columns, you'd enter the following and hit Enter:

=RANDARRAY(3,4)

RANDARRAY with rows and columns

For a random array that covers the same number of rows and columns but also has a minimum of 1 and a maximum of 10, enter the following and press Enter:

=RANDARRAY(3,4,1,10)

RANDARRAY with minimum and maximum values

For a random array using these same arguments but returns only whole numbers, you would enter the following and press Enter:

=RANDARRAY(3,4,1,10,TRUE)

RANDARRAY with whole numbers

Microsoft Excel gives you easy options for generating random numbers . Whether you need a discrete number or simply a number between one and 10, Excel has you covered.

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

Randomly assign people to groups

Related functions .

Excel formula: Randomly assign people to groups

To randomly assign people to groups or teams of a specific size, you can use a helper column with a value generated by the RAND function, together with a formula based on the RANK and ROUNDUP functions. In the example shown, the formula in D5 is:

which returns a group number for each name listed in column B, where "randoms" is the named range C5:C16, and "size" is the named range G5.

Generic formula

Explanation .

At the core of this solution is the RAND function, which is used to generate a random number in a helper column (column C in the example).

To assign a full set of random values in one step, select the range C5:C16, and type =RAND() in the formula bar. Then use the shortcut control + enter to enter the formula in all cells at once.

Note: the RAND function will keep generating random values every time a change is made the worksheet, so typically you will want to replace the results in column C with actual values using paste special to prevent changes after random values are assigned.

In column D, a group number is assigned with the following formula:

The RANK function is used to rank the value in C5 against all random values in the list. The result will be a number between 1 and the total number of people (12 in this example).

This result is then divided by "size", which represents the desired group size (3 in the example), which then goes into the ROUNDUP function as number , with num_digits of zero. The ROUNDUP function returns a number rounded up to the next integer. This number represents assigned group number.

CEILING version

The CEILING function can be used instead of ROUNDUP. Like the ROUNDUP function, CEILING also rounds up but instead of rounding to a given number of decimal places, CEILING rounds to a given multiple.

Related formulas

Excel formula: Randomly assign data to groups

  • Randomly assign data to groups

Excel formula: Random text values

  • Random text values

Excel formula: Random number from fixed set of options

  • Random number from fixed set of options

Related functions

Excel RAND function

  • RAND Function

The Excel RAND function returns a random number between 0 and 1. For example, =RAND() will generate a number like 0.422245717. RAND recalculates when a worksheet is opened or changed.

Excel ROUNDUP function

  • ROUNDUP Function

The Excel ROUNDUP function returns a number rounded up to a given number of decimal places. Unlike standard rounding, where only numbers less than 5 are rounded down, ROUNDUP rounds all numbers up .

Excel CEILING function

  • CEILING Function

The Excel CEILING function rounds a given number up to the nearest specified multiple. CEILING works like the MROUND function , but CEILING always rounds up .

Dave Bruns Profile Picture

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.

Excel foundational video course

Help us improve Exceljet

Your email address is private and not shared.

  • 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 453,012 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.

Step 1 Open the Microsoft Excel program.

  • 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.

Step 4 Make sure you have all relevant data entered into your spreadsheet.

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

Step 3 Select the new

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

Step 5 Press ↵ Enter.

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

Step 8 Select the cell below your random sample number.

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

Step 9 Highlight the rest of the random sample cells.

  • 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.

Step 10 Hold down Control and tap V.

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

Step 1 Select the top left cell.

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

Step 2 Hold down ⇧ Shift and select the bottom right cell.

  • 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.

Step 3 Right-click your data.

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

Step 4 Hover your cursor over Sort.

  • 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.

Step 7 Choose your random sample participants.

Community Q&A

Community Answer

  • 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

how to do random allocation in excel

  • 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

Make a Spreadsheet in Excel

About This Article

Jack Lloyd

  • Send fan mail to authors

Is this article up to date?

Am I a Narcissist or an Empath Quiz

Featured Articles

Get Started in Standup Comedy

Trending Articles

How to Take the Perfect Thirst Trap

Watch Articles

Wrap a Round Gift

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

wikiHow Tech Help Pro:

Level up your tech skills and stay ahead of the curve

  • Ablebits blog
  • Random data

Random sort in Excel: shuffle cells, rows and columns

Svetlana Cheusheva

The tutorial will teach you two quick ways to randomize in Excel: perform random sort with formulas and shuffle data by using a special tool.

Microsoft Excel provides a handful of different sorting options including ascending or descending order, by color or icon, as well as custom sort. However, it lacks one important feature - random sort. This functionality would come in handy in situations when you need to randomize data, say, for an unbiased assigning of tasks, allocation of shifts, or picking a lottery winner. This tutorial will teach you a couple of easy ways to do random sort in Excel.

How to randomize a list in Excel with a formula

Although there is no native function to perform random sort in Excel, there is a function to generate random numbers ( Excel RAND function ) and we are going to use it.

Assuming you have a list of names in column A, please follow these steps to randomize your list:

  • Insert a new column next to the list of names you want to randomize. If your dataset consists of a single column, skip this step.
  • In the first cell of the inserted column, enter the RAND formula: =RAND()

Excel RAND formula

Tips & notes:

  • Excel RAND is a volatile function, meaning that new random numbers are generated every time the worksheet is recalculated. So, if you are not happy with how your list has been randomized, keep hitting the sort button until you get the desired result.
  • To prevent the random numbers from recalculating with every change you make to the worksheet, copy the random numbers, and then paste them as values by using the Paste Special feature . Or, simply delete the column with the RAND formula if you don't need it any longer.
  • The same approach can be used to randomize multiple columns . To have it done, place two or more columns side by side so that the columns are contiguous, and then perform the above steps.

How to shuffle data in Excel with Ultimate Suite

If you don't have time to fiddle with formulas, use the Random Generator for Excel tool included with our Ultimate Suite to do a random sort faster.

  • Cells in each row - shuffle cells in each row individually.
  • Cells in each column - randomly sort cells in each column.
  • Entire rows - shuffle rows in the selected range.
  • Entire columns - randomize the order of columns in the range.
  • All cells in the range - randomize all cells in the selected range.
  • Click the Shuffle button.

A list of names is randomized.

If you are curious to try this tool in your Excel, you are welcome to download an evaluation version below. Thank you for reading!

Available downloads

You may also be interested in.

  • How to select random sample in Excel
  • Get random selection without duplicates
  • How to generate random numbers in Excel
  • How to generate random numbers without duplicates
  • How to sort by date in Excel
  • How to sort and filter by color in Excel

Table of contents

Ablebits.com website logo

19 comments

how to do random allocation in excel

Good Evening,

I am needing some help from an expert formula/function guru.

I am attempting to create a randomize list of job assignments for a high school shop class. I can create a list/schedule that continually rotates in a methodical order, but that continually results in the same students working with the same individuals. I have a set number of students that is different for each class and a set number of jobs/assignments (some duplicated but the total number of jobs/assignments matches the number of students for each class). I want to use a formula/function to randomly assign the jobs to each student for each day while still ensuring that each student has the same amount of opportunities to participate within each job.

I hope this makes sense. Thank you in advance for your help.

how to do random allocation in excel

Hello! If I understand your task correctly, our Random Generator for Excel may help you solve it in a few clicks. The values from your list will be randomly inserted into the selected cells. Optionally, you can insert only unique values. 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 .

how to do random allocation in excel

I havev two columns in mu excel...column A list of teachers(1-20), column B class room numbers(1-20). How to shuffle column A with out affecting column B.

Hi! Please check out this article to learn how to alphabetize in Excel: sort columns and rows A-Z or Z-A.

how to do random allocation in excel

Google randomization is a lot easier and user friendly.

how to do random allocation in excel

I have a complicated spreadsheet in which I use iteration=1 to deal with an intentional circular reference. I use the RAND() function, and manual recalculation. I use COUNTIF to determine the sort sequence of the random numbers. I then use OFFSET and MATCH together to extract the data in the new randomized order.

For reasons that are too complicated for this format, I want to retrieve first one column on one manual recalculation and then that column plus a second (without resorting the original list) on the next recalculation - hence the circular equations

What's happening is that the COUNTIF function appears to be working on the random numbers before all of the random numbers have been calculated as the values returned are clearly wrong.

I’m assuming there’s some interaction between iteration, RAND() and the Excel’s calculation sequencing that is causing the problems but I have been unable to work out a solution.

Hello! Unfortunately, without seeing your data it is difficult to give you any advice. I guess the reason is that the random data changes with each recalculation.

That's been my thought too.

I read Excel's description of how it determines the calculation order of cells so I tried linking the calculation of the COUNTIF function to the cell containing the random number so that a recalculation of the COUNTIF is tied to the last iteration's random number. This hasn't solved the problem, though.

Do you know of a way to force the Excel's calculation tree to calculate specific cells in a particular order?

I am willing to send you the spreadsheet if you are interested in digging into the situation more deeply.

Hello! You can control the order of calculations in Excel only inside formulas. The rules of arithmetic are used.

how to do random allocation in excel

I need a computer/randomizer program for a volleyball tournament that shuffles 36 players to play or sit out within 12 games.

1) We have 2 volleyball courts, 4 sides (Red & Green; Yellow & Blue). 2) We have 36 players. 6 teams of 6 players per team. 24 players would play on 4 teams on the 2 courts for each game. 12 players would sit out each game. Each team will consist of 6 different players each game. 3) Each player would have a number and would play on the team and court color each game. 4) There would be a total of 12 games. Each player would play 8 and sit out 4 of the 12 games. Each player will play twice on each court color. No player will play more than 2 games in a row. 5) With 36 players, 1 player would play 8 games with 5 other players each game.

We would also like to have the program sort for: 36 players (6 teams of 6 players per team); 30 players (6 teams of 5 players per team); 24 players (6 teams of 4 players per team); 24 players (4 teams of 6 players per team); 20 players (4 teams of 5 players per team) and 16 players (4 teams of 4 players per team).

I would like the program to be able to run on a laptop computer. Thanks for you assistance or comments.

Hi, This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

how to do random allocation in excel

Brilliant! Your instructions worked perfectly, giving me a way to instantly randomize a list of names as often as I want. No way can anyone stumble upon this solution without in depth knowledge of Excel. Sincere thanks!

how to do random allocation in excel

thanks a lot you saved my job. i had to plan a tambola, and was easily able to generate 45 tickets with the help of the randomise > shuffle cells tool from Utilitites. I have been trying so hard from =Rand() formula, but it never worked multiple combinations. Thanks a lot.

how to do random allocation in excel

I do circuit training routines at home. I have multiple exercises that I perform, and I'm currently looking through a list on a Word document, and just picking as I go. I would like for Excel to choose the exercises randomly for me. I have multiple columns of exercises, once column for each part of the body it works. Is it possible to open the spreadsheet, and Excel will automatically highlight several random cells out of each column? And each time I open it, Excel will choose a different set of random cells to automatically highlight out of each column? I am not well-versed in formulas, and was unable to make the multiple column thing work for me.

Thanks for any advice or help you can give.

Hello Kelly! Read about random selection in this article . We have a ready-made solution for your task. I'd recommend you to have a look at our Ablebits Tools - Randomize. This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for 30 days for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial

how to do random allocation in excel

Hello Thank you. that was creatively. I enjoyed it.

how to do random allocation in excel

the running ticker at the bottom of the site is incredibly distracting and makes it almost unusable. I use it ONLY if I can't find the answer on another site.

You can hide it

how to do random allocation in excel

Thank you so much! I knew there had to be a way to randomize a list in Excel. I never would have come up with this on my own!

Post a comment

  • CELL REFERENCE

how to do random allocation in excel

Post your problem and you'll get expert help in seconds

  • All articles

Randomly Assign People to Groups in Excel

At times we have to randomly assign people to groups. This may seem a mind-numbing task, but it can be made very easy when executed in Excel.

How to randomly assign people to groups in Excel

Here we can use a formula created by the Excel RANK and ROUNDUP functions together. We also use a helper column where RAND function is used to randomly generate values.

To randomly assign people to groups we use the formula

=ROUNDUP(RANK(A1,randoms)/size,0)

The above formula returns a group no. for each entry.

‘ randoms ’ and ‘ size ’ are named range.

Random is a helper column generated by Excel RAND Function.

Explanation of formula

Excel RANK function is an inbuilt function in Excel which returns the rank of a number within a large set of numbers or array. This function is categorized as an inbuilt statistical function in Excel.

Excel ROUNDUP function is another inbuilt function in Excel which returns a number which is rounded up to a definite number of digits. This function rounds away from ‘0’. It is categorized as a Trig or Math function and can be aptly used as a worksheet function in Excel.

Let us take an example; we have to assign random groups to students for the aerobics class. We provide the size of group 4 in G10 as shown. To create groups, we use the formula shown in D5.

=ROUNDUP(RANK(C5,random)/size,0)

how to do random allocation in excel

Here, “ random ” is the name of the range [C5:C20] and “ size ” is the named range [G10] .

We can drag the formula and use the above formula for a hefty amount of data.

how to do random allocation in excel

We can also make use of the RAND function, which would generate random numbers. This function will keep on generating random values every time we change the values in the worksheet.

Alternate Solution

Another approach of assigning random value is by using the CEILING function which is another inbuilt function in Excel.

= CEILING ( RANK (C5,random)/size,1)

This function can be used as an alternative to the ROUNDUP function. The ceiling function rounds up, but instead of rounding to some given number of decimal places, it rounds up to a given multiple.

As per the example is shown in Figure 1, you can you use the above alternate formula to randomly assign people to groups as shown below.

how to do random allocation in excel

Still need some help with Excel formatting or have other questions about Excel? Connect with a live  Excel expert  here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

how to do random allocation in excel

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Did this post not answer your question? Get a solution from connecting with the expert

Amazon.com, Inc

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.

Save time by asking instead! Most questions solved and answered in 10 minutes.

  • Kutools for Excel
  • Kutools for Outlook
  • Kutools for Word
  • EULA End User License Agreement
  • Setup Made Simple Easily Install or Uninstall
  • Get 4 Sotware Package
  • 60-Day Refund 60-Day Risk-Free Guarantee
  • 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.

doc random group 3

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.

doc random group 4

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!

how to do random allocation in excel

U.S. flag

An official website of the United States government

The .gov means it’s official. Federal government websites often end in .gov or .mil. Before sharing sensitive information, make sure you’re on a federal government site.

The site is secure. The https:// ensures that you are connecting to the official website and that any information you provide is encrypted and transmitted securely.

  • Publications
  • Account settings
  • Advanced Search
  • Journal List
  • Clin Orthop Surg
  • v.6(1); 2014 Mar

Logo of clinorsurg

How to Do Random Allocation (Randomization)

Jeehyoung kim.

Department of Orthopedic Surgery, Seoul Sacred Heart General Hospital, Seoul, Korea.

Wonshik Shin

To explain the concept and procedure of random allocation as used in a randomized controlled study.

We explain the general concept of random allocation and demonstrate how to perform the procedure easily and how to report it in a paper.

Randomized controlled trials (RCT) are known as the best method to prove causality in spite of various limitations. Random allocation is a technique that chooses individuals for treatment groups and control groups entirely by chance with no regard to the will of researchers or patients' condition and preference. This allows researchers to control all known and unknown factors that may affect results in treatment groups and control groups.

Allocation concealment is a technique used to prevent selection bias by concealing the allocation sequence from those assigning participants to intervention groups, until the moment of assignment. Allocation concealment prevents researchers from influencing which participants are assigned to a given intervention group. This process must be included in the experiment for the success of any RCT.

Blinding refers to keeping trial participants, health care providers, assessors or data collectors unaware of the assigned intervention, so that they will not be influenced by that knowledge. This process is conducted to minimize possible bias in implementation, dropouts, measurements, etc. Blinding is not always feasible for RCT but should be implemented if possible.

Randomization, allocation concealment and blinding should be well implemented and should be described in the paper.

On the other hand, many researchers are still unfamiliar with how to do randomization, and it has been shown that there are problems in many studies with the accurate performance of the randomization and that some studies are reporting incorrect results. So, we will introduce the recommended way of using statistical methods for a randomized controlled study and show how to report the results properly.

CATEGORIES OF RANDOMIZATION

Simple randomization.

The easiest method is simple randomization. If you assign subjects into two groups A and B, you assign subjects to each group purely randomly for every assignment. Even though this is the most basic way, if the total number of samples is small, sample numbers are likely to be assigned unequally. For this reason, we recommend you to use this method when the total number of samples is more than 100.

Block Randomization

We can create a block to assign sample numbers equally to each group and assign the block.

If we specify two in one block (the so-called block size is two), we can make two possible sequences of AB and BA. When we randomize them, the same sample numbers can be assigned to each group. If the block size is four, we can make six possible sequences; these are AABB, ABAB, ABBA, BAAB, BABA, BBAA, and we randomize them.

However, there is a disadvantage in that the executer can predict the next assignment. We can easily know the fact that B comes after A if the block size is two and if the block size is four; we can predict what every 4th sample is. This is discordant with the principle of randomization. To solve this problem, the allocator must hide the block size from the executer and use randomly mixed block sizes. For example, the block size can be two, four, and six.

Stratified Randomization

Randomization is important because it is almost the only way to assign all the other variables equally except for the factor (A and B) in which we are interested. However, some very important confounding variables can often be assigned unequally to the two groups. This possibility increases when the number of samples is smaller, and we can stratify the variables and assign the two groups equally in this case.

For example, if the smoking status is very important, what will you do? First, we have two methods of randomization that we learned previously. There are two randomly assigned separate sequences for smokers and non-smokers. Smokers are assigned to the smoker's sequences, and non-smokers are assigned to the non-smoker's sequences. Therefore, both smokers and non-smokers groups will be placed equally with the same numbers.

So we can use 'simple randomization with/without stratification' or 'block randomization with/without stratification.' However, if there are multiple stratified variables, it is difficult to place samples in both groups equally with the same numbers. Usually two or fewer stratified variables are recommended.

EXAMPLES OF RANDOMIZATION

Although there are websites or common programs for randomization, let us use an Excel file. Download the attached file in http://cafe.naver.com/easy2know/6427 . It is in a 'Read-only' state, but there is no limit in function; it is in the 'Read-only' state only to prevent accidental modification.

Due to the nature of Excel, if there is a change, it creates a new random number accordingly. If we input any number instead of '2' in the orange-colored cell and click the 'enter key,' it creates new random sequences ( Fig. 1 ). The sequences are the result of simple randomization. The numbers in the right column show the numbers of the total sample. Basically the numbers are up to 1,000, but if you need to, you can extend the numbers with the AutoFill function in Excel.

An external file that holds a picture, illustration, etc.
Object name is cios-6-103-g001.jpg

Simple randomization sheet.

Fig. 2 shows an example of randomization when the block size is four. Also, there are numbers of the total samples in the right column.

An external file that holds a picture, illustration, etc.
Object name is cios-6-103-g002.jpg

An example of randomization when the block size is four.

Fig. 3 shows an example of block randomization when the block size is two and four. Total eight kinds of blocks inside of the red-dotted line are assigned at random. The left column is for allocation and the right column is for the total sample size.

An external file that holds a picture, illustration, etc.
Object name is cios-6-103-g003.jpg

Block randomization when the block size is two and four. Total eight blocks in the red-dotted line are assigned at random. The left column is for allocation and the right column is for the total sample size.

By the way, www.randomization.com can do block randomization for up to four kinds of block sizes and it is very easy to perform as well. Fig. 4 shows the general features and an example.

An external file that holds a picture, illustration, etc.
Object name is cios-6-103-g004.jpg

www.randomization.com can do block randomization more easily. In this figure, the block size is 2, 4, and 6 when the total samples are 88.

THE REALITY OF THE RANDOMIZATION PROCEDURE

How to implement these techniques can vary by each trial. The following is only one of the examples of how these can be implemented in real trial. You may change the details of the example for your experiment. Figures of randomization and allocation concealment can also be adjusted to your needs ( Fig. 5 ).

An external file that holds a picture, illustration, etc.
Object name is cios-6-103-g005.jpg

The reality of the randomization procedure.

Random Allocation

An independent researcher makes random allocation cards using computer-generated random numbers. He keeps the original random allocation sequences in an inaccessible third place and works with a copy. Since the executers can get confused with the original coding of A and B later, the allocator should record exactly what these codes mean to avoid further confusion.

When the purpose of the study is a surgical procedure, instead of using A and B, different names that distinguish exactly between the surgical procedures should be used (for example, 'the anterior approach' and 'the posterior approach'). It is convenient to reproduce the contents of the Excel file to a Word file, and enlarge the text font after replacing A with 'the anterior approach' (page break) and B with 'the posterior approach' (page break). Next, you print it out and put each of the sheets one by one into each envelope ( Fig. 6 ).

An external file that holds a picture, illustration, etc.
Object name is cios-6-103-g006.jpg

MS word can replace A and B with a specific treatment name easily.

Here in Fig. 6 , '^m' is a special character for manual page break. After setting it as shown, you click 'all change' and print it out. Then we can get it printed per sheet. The inside of the envelope should not be visible from the outside, and it has to be printed out for each one and put in an envelope after being folded several times. In some papers, even aluminum foil was used to hide the print to prevent it from being read with a flash of light.

There are serial numbers on the outside of the envelopes. Input date, time, patient ID, results after the procedure, etc. usually will be recorded on the envelope or another sheet inside of the envelope, also.

Drug Preparation

An independent nurse (researcher) prepares syringes with "drug A" and "drug B" and puts them into envelopes according to the allocation orders. These syringes cannot be distinguished because they contain the same colored liquid with the same volume. Or pills or tablets with the same color and shape (placebo) will be put into the envelopes according to the allocation orders.

In the case of surgical treatment, an independent researcher prepares the envelopes, including writing the treatment name on a sheet of paper inside it. In the operation room, another independent nurse (researcher) opens the envelope and informs the doctor to do the treatment that is written on the paper in the envelope.

Another independent nurse injects the drug or the doctor performs the operation according to the order. The patient's ID, date, time and other information are recorded on each envelope. The nurse and the patient would not know what drugs are injected (double blinded). The doctor knows the treatment and the patient does not know it (one blinded). The preparer retrieves the envelopes and checks to see if the operation (and injection) was done as planned.

In the case of broken or lost syringes, the preparer figures out what the number of the envelope it is and replaces the envelope with the same drug according to the allocation.

The envelopes should be opened just before the injection or operation. For example, when a patient comes, an envelope is opened; however, if this does not meet the criteria for the performance of the study, this can be cancelled. Also, if the operator finds out before an operation the tool that is to be inserted, it is impossible to get the operation as planned. For example, even though plate A was assigned to be used, if the patient was indicated to have some other surgery because of infection or severe osteoporosis, you will waste an envelope and it will cause confusion as well as violate the randomization. All these cases should be mentioned as inclusion criteria and exclusion criteria in advance. To avoid this, the envelopes should be opened just before the operation or injection if possible.

However, in cases where the operation tool is so big that two tools cannot be prepared at the same time, or the preparation takes a lot of money (robotic surgery, etc.) or time (liver transplantation, etc.), the envelopes can be opened in advance.

Also, although you open an envelope and choose the procedure that you see, other conditions that affect the outcome can occur. For example, the patient could be admitted to the intensive care unit for medical problems after treatment, or may not get enough rehabilitation treatment for some other reasons.

In this case, it is an important issue whether to consider this as a follow-up loss or exclude this case from the study. We can deal with this issue by focusing on intention-to-treat analysis and per-protocol analysis. We will study this later when we get a chance.

Survey Results

After a period of time, another independent researcher measures the patient's outcome. He does not know the allocation. That is another blinding, so triple blinding is recommended if possible.

Another independent researcher who was not involved in any stage of these procedures will do the statistical analysis (sometimes a statistician). He even does not know the treatment name because the treatment name is hidden, as in A and B.

REPORTING OF RANDOMIZATION METHODS

From 1988 to 2000, 72 of 2,468 papers (2.9%) in the Journal of Born and Joint Surgery were RCTs. 1) It has been suggested that in some of the papers, randomization was not completely done or the result was not properly reported. According to the analysis of RCTs using painkillers from the January issue in 1966 to the June issue in 2006, 23.9% of the papers were inadequate in terms of the randomization. 2) It would be helpful to see a CONSORT checklist and examples. The following were used in the actual papers and extracted from examples in the CONSORT ( http://www.consort-statement.org ).

Sequence Generation

"Independent pharmacists dispensed either active or placebo inhalers according to a computer generated randomization list."

"For allocation of the participants, a computer-generated list of random numbers was used."

Type of Randomization

"Randomization sequence was created using Stata 9.0 (StataCorp, College Station, TX, USA) statistical software and was stratified by center with a 1:1 allocation using random block sizes of 2, 4, and 6."

"Participants were randomly assigned following simple randomization procedures (computerized random numbers) to 1 of 2 treatment groups."

We can apply the above examples to our case as follows: Randomization sequence was created using Excel 2007 (Microsoft, Redmond, WA, USA) with a 1:1 allocation using random block sizes of 2 and 4 by an independent doctor. In this way, sequence generation and type of randomization can be expressed at the same time.

Allocation Concealment Mechanism

"The doxycycline and placebo were in capsule form and identical in appearance. They were pre-packed in bottles and consecutively numbered for each woman according to the randomization schedule. Each woman was assigned an order number and received the capsules in the corresponding pre-packed bottle."

"The allocation sequence was concealed from the researcher (JR) enrolling and assessing participants in sequentially numbered, opaque, sealed and stapled envelopes. Aluminum foil inside the envelope was used to render the envelope impermeable to intense light. To prevent subversion of the allocation sequence, the name and date of birth of the participant was written on the envelope and a video tape made of the sealed envelope with participant details visible. Carbon paper inside the envelope transferred the information onto the allocation card inside the envelope and a second researcher (CC) later viewed video tapes to ensure envelopes were still sealed when participants' names were written on them. Corresponding envelopes were opened only after the enrolled participants completed all baseline assessments and it was time to allocate the intervention."

The second example was described in great detail, and we can guess how important the randomization and concealment were.

Who Generated the Allocation Sequence, Who Enrolled Participants, and Who Assigned Participants to Interventions?

"Determination of whether a patient would be treated by streptomycin and bed-rest (S case) or by bed-rest alone (C case) was made by reference to a statistical series based on random sampling numbers drawn up for each sex at each center by Professor Bradford Hill (this means that the stratification was done by sex and center); the details of the series were unknown to any of the investigators or to the coordinator. After acceptance of a patient by the panel, and before admission to the streptomycin center, the appropriate numbered envelope was opened at the central office; the card inside told, if the patient was to be an S or a C case, and this information was then given to the medical officer of the center."

"Details of the allocated group were given on colored cards contained in sequentially numbered, opaque, sealed envelopes. These were prepared at the NPEU and kept in an agreed location on each ward. Randomization took place at the end of the 2nd stage of labor when the midwife considered a vaginal birth was imminent. To enter a woman into the study, the midwife opened the next consecutively numbered envelope."

"Block randomization was by a computer generated random number list prepared by an investigator with no clinical involvement in the trial. We stratified by admission for an oncology related procedure. After the research nurse had obtained the patient's consent, she telephoned a contact who was independent of the recruitment process for allocation consignment."

If Done, Who Was Blinded after Assignment to Interventions and How

"Whereas patients and physicians allocated to the intervention group were aware of the allocated arm, outcome assessors and data analysts were kept blinded to the allocation."

"Blinding and equipoise were strictly maintained by emphasizing to intervention staff and participants that each diet adheres to healthy principles, and each of them is advocated by certain experts to be superior for long-term weight-loss. Except for the interventionists (dieticians and behavioral psychologists), investigators and staff were kept blind to diet assignment of the participants. The trial adhered to established procedures to maintain separation between staff that take outcome measurements and staff that deliver the intervention. Staffs who obtained outcome measurements were not informed of the diet group assignment. Intervention staffs, dieticians and behavioral psychologists who delivered the intervention did not take outcome measurements. All investigators, staffs, and participants were kept masked to outcome measurements and trial results."

In short, in a paper, we have to report who was kept blinded. In the case of physical therapy or surgery, keeping the surgeon blinded would be difficult or even impossible; however, blinding is possible for the person who measures the outcome. Anyhow, all individuals who were kept blinded must be described in the report.

WEBSITES AND SYSTEMS HELPING THESE PROCEDURES

To help with all the procedures of a fully qualified RCT, the following systems including electronic case report forms (eCRFs) are available for researchers.

iCReaT (clinical research and trial management system) in Korea Centers for Disease Control & Prevention (KCDC; http://icreat.nih.go.kr ): free for pre-educated and qualified researchers; there are regular education programs once a month, and some hospitals (for example, Severance Hospital) have their own educational programs. An English version will be available soon for non-Korean researchers.

MRCC ( https://mrcc.snuh.org ): for Seoul National University Hospital only. It is relatively inexpensive and includes statistical counseling.

Velos ( http://eresearch.ncc.re.kr ): a world-famous system and very expensive; it is available at National Cancer Center in Korea ( http://ncc.re.kr/crcc/ ).

eCRFs are very convenient as well as helpful to improve the quality of research and their advantages are summarized in the table ( Table 1 ).

Comparisons between Paper CRFs and e-CRFs of Web-based Clinical Research Management System

An external file that holds a picture, illustration, etc.
Object name is cios-6-103-i001.jpg

CRF: case report form.

In RCT, random assignment is important and performing it is easy if you know how to do it. Besides the practice of randomization, correct reporting of the randomization process is also important and it should be done very accurately.

No potential conflict of interest relevant to this article was reported.

Europe PMC requires Javascript to function effectively.

Either your web browser doesn't support Javascript or it is currently turned off. In the latter case, please turn on Javascript support in your web browser and reload this page.

Search life-sciences literature ( 43,669,105 articles, preprints and more)

  • Free full text
  • Citations & impact
  • Similar Articles

How to do random allocation (randomization).

Author information, affiliations, orcids linked to this article.

  • Kim J | 0000-0003-3380-1683

Clinics in Orthopedic Surgery , 14 Feb 2014 , 6(1): 103-109 https://doi.org/10.4055/cios.2014.6.1.103   PMID: 24605197  PMCID: PMC3942596

Abstract 

Free full text .

Logo of clinorsurg

How to Do Random Allocation (Randomization)

Jeehyoung kim.

Department of Orthopedic Surgery, Seoul Sacred Heart General Hospital, Seoul, Korea.

Wonshik Shin

To explain the concept and procedure of random allocation as used in a randomized controlled study.

We explain the general concept of random allocation and demonstrate how to perform the procedure easily and how to report it in a paper.

Randomized controlled trials (RCT) are known as the best method to prove causality in spite of various limitations. Random allocation is a technique that chooses individuals for treatment groups and control groups entirely by chance with no regard to the will of researchers or patients' condition and preference. This allows researchers to control all known and unknown factors that may affect results in treatment groups and control groups.

Allocation concealment is a technique used to prevent selection bias by concealing the allocation sequence from those assigning participants to intervention groups, until the moment of assignment. Allocation concealment prevents researchers from influencing which participants are assigned to a given intervention group. This process must be included in the experiment for the success of any RCT.

Blinding refers to keeping trial participants, health care providers, assessors or data collectors unaware of the assigned intervention, so that they will not be influenced by that knowledge. This process is conducted to minimize possible bias in implementation, dropouts, measurements, etc. Blinding is not always feasible for RCT but should be implemented if possible.

Randomization, allocation concealment and blinding should be well implemented and should be described in the paper.

On the other hand, many researchers are still unfamiliar with how to do randomization, and it has been shown that there are problems in many studies with the accurate performance of the randomization and that some studies are reporting incorrect results. So, we will introduce the recommended way of using statistical methods for a randomized controlled study and show how to report the results properly.

  • CATEGORIES OF RANDOMIZATION

Simple Randomization

The easiest method is simple randomization. If you assign subjects into two groups A and B, you assign subjects to each group purely randomly for every assignment. Even though this is the most basic way, if the total number of samples is small, sample numbers are likely to be assigned unequally. For this reason, we recommend you to use this method when the total number of samples is more than 100.

Block Randomization

We can create a block to assign sample numbers equally to each group and assign the block.

If we specify two in one block (the so-called block size is two), we can make two possible sequences of AB and BA. When we randomize them, the same sample numbers can be assigned to each group. If the block size is four, we can make six possible sequences; these are AABB, ABAB, ABBA, BAAB, BABA, BBAA, and we randomize them.

However, there is a disadvantage in that the executer can predict the next assignment. We can easily know the fact that B comes after A if the block size is two and if the block size is four; we can predict what every 4th sample is. This is discordant with the principle of randomization. To solve this problem, the allocator must hide the block size from the executer and use randomly mixed block sizes. For example, the block size can be two, four, and six.

Stratified Randomization

Randomization is important because it is almost the only way to assign all the other variables equally except for the factor (A and B) in which we are interested. However, some very important confounding variables can often be assigned unequally to the two groups. This possibility increases when the number of samples is smaller, and we can stratify the variables and assign the two groups equally in this case.

For example, if the smoking status is very important, what will you do? First, we have two methods of randomization that we learned previously. There are two randomly assigned separate sequences for smokers and non-smokers. Smokers are assigned to the smoker's sequences, and non-smokers are assigned to the non-smoker's sequences. Therefore, both smokers and non-smokers groups will be placed equally with the same numbers.

So we can use 'simple randomization with/without stratification' or 'block randomization with/without stratification.' However, if there are multiple stratified variables, it is difficult to place samples in both groups equally with the same numbers. Usually two or fewer stratified variables are recommended.

  • EXAMPLES OF RANDOMIZATION

Although there are websites or common programs for randomization, let us use an Excel file. Download the attached file in http://cafe.naver.com/easy2know/6427 . It is in a 'Read-only' state, but there is no limit in function; it is in the 'Read-only' state only to prevent accidental modification.

Due to the nature of Excel, if there is a change, it creates a new random number accordingly. If we input any number instead of '2' in the orange-colored cell and click the 'enter key,' it creates new random sequences ( Fig. 1 ). The sequences are the result of simple randomization. The numbers in the right column show the numbers of the total sample. Basically the numbers are up to 1,000, but if you need to, you can extend the numbers with the AutoFill function in Excel.

how to do random allocation in excel

Simple randomization sheet.

Fig. 2 shows an example of randomization when the block size is four. Also, there are numbers of the total samples in the right column.

how to do random allocation in excel

An example of randomization when the block size is four.

Fig. 3 shows an example of block randomization when the block size is two and four. Total eight kinds of blocks inside of the red-dotted line are assigned at random. The left column is for allocation and the right column is for the total sample size.

how to do random allocation in excel

Block randomization when the block size is two and four. Total eight blocks in the red-dotted line are assigned at random. The left column is for allocation and the right column is for the total sample size.

By the way, www.randomization.com can do block randomization for up to four kinds of block sizes and it is very easy to perform as well. Fig. 4 shows the general features and an example.

how to do random allocation in excel

www.randomization.com can do block randomization more easily. In this figure, the block size is 2, 4, and 6 when the total samples are 88.

  • THE REALITY OF THE RANDOMIZATION PROCEDURE

How to implement these techniques can vary by each trial. The following is only one of the examples of how these can be implemented in real trial. You may change the details of the example for your experiment. Figures of randomization and allocation concealment can also be adjusted to your needs ( Fig. 5 ).

how to do random allocation in excel

The reality of the randomization procedure.

Random Allocation

An independent researcher makes random allocation cards using computer-generated random numbers. He keeps the original random allocation sequences in an inaccessible third place and works with a copy. Since the executers can get confused with the original coding of A and B later, the allocator should record exactly what these codes mean to avoid further confusion.

When the purpose of the study is a surgical procedure, instead of using A and B, different names that distinguish exactly between the surgical procedures should be used (for example, 'the anterior approach' and 'the posterior approach'). It is convenient to reproduce the contents of the Excel file to a Word file, and enlarge the text font after replacing A with 'the anterior approach' (page break) and B with 'the posterior approach' (page break). Next, you print it out and put each of the sheets one by one into each envelope ( Fig. 6 ).

how to do random allocation in excel

MS word can replace A and B with a specific treatment name easily.

Here in Fig. 6 , '^m' is a special character for manual page break. After setting it as shown, you click 'all change' and print it out. Then we can get it printed per sheet. The inside of the envelope should not be visible from the outside, and it has to be printed out for each one and put in an envelope after being folded several times. In some papers, even aluminum foil was used to hide the print to prevent it from being read with a flash of light.

There are serial numbers on the outside of the envelopes. Input date, time, patient ID, results after the procedure, etc. usually will be recorded on the envelope or another sheet inside of the envelope, also.

Drug Preparation

An independent nurse (researcher) prepares syringes with "drug A" and "drug B" and puts them into envelopes according to the allocation orders. These syringes cannot be distinguished because they contain the same colored liquid with the same volume. Or pills or tablets with the same color and shape (placebo) will be put into the envelopes according to the allocation orders.

In the case of surgical treatment, an independent researcher prepares the envelopes, including writing the treatment name on a sheet of paper inside it. In the operation room, another independent nurse (researcher) opens the envelope and informs the doctor to do the treatment that is written on the paper in the envelope.

Another independent nurse injects the drug or the doctor performs the operation according to the order. The patient's ID, date, time and other information are recorded on each envelope. The nurse and the patient would not know what drugs are injected (double blinded). The doctor knows the treatment and the patient does not know it (one blinded). The preparer retrieves the envelopes and checks to see if the operation (and injection) was done as planned.

In the case of broken or lost syringes, the preparer figures out what the number of the envelope it is and replaces the envelope with the same drug according to the allocation.

The envelopes should be opened just before the injection or operation. For example, when a patient comes, an envelope is opened; however, if this does not meet the criteria for the performance of the study, this can be cancelled. Also, if the operator finds out before an operation the tool that is to be inserted, it is impossible to get the operation as planned. For example, even though plate A was assigned to be used, if the patient was indicated to have some other surgery because of infection or severe osteoporosis, you will waste an envelope and it will cause confusion as well as violate the randomization. All these cases should be mentioned as inclusion criteria and exclusion criteria in advance. To avoid this, the envelopes should be opened just before the operation or injection if possible.

However, in cases where the operation tool is so big that two tools cannot be prepared at the same time, or the preparation takes a lot of money (robotic surgery, etc.) or time (liver transplantation, etc.), the envelopes can be opened in advance.

Also, although you open an envelope and choose the procedure that you see, other conditions that affect the outcome can occur. For example, the patient could be admitted to the intensive care unit for medical problems after treatment, or may not get enough rehabilitation treatment for some other reasons.

In this case, it is an important issue whether to consider this as a follow-up loss or exclude this case from the study. We can deal with this issue by focusing on intention-to-treat analysis and per-protocol analysis. We will study this later when we get a chance.

Survey Results

After a period of time, another independent researcher measures the patient's outcome. He does not know the allocation. That is another blinding, so triple blinding is recommended if possible.

Another independent researcher who was not involved in any stage of these procedures will do the statistical analysis (sometimes a statistician). He even does not know the treatment name because the treatment name is hidden, as in A and B.

  • REPORTING OF RANDOMIZATION METHODS

From 1988 to 2000, 72 of 2,468 papers (2.9%) in the Journal of Born and Joint Surgery were RCTs. 1) It has been suggested that in some of the papers, randomization was not completely done or the result was not properly reported. According to the analysis of RCTs using painkillers from the January issue in 1966 to the June issue in 2006, 23.9% of the papers were inadequate in terms of the randomization. 2) It would be helpful to see a CONSORT checklist and examples. The following were used in the actual papers and extracted from examples in the CONSORT ( http://www.consort-statement.org ).

Sequence Generation

"Independent pharmacists dispensed either active or placebo inhalers according to a computer generated randomization list."

"For allocation of the participants, a computer-generated list of random numbers was used."

Type of Randomization

"Randomization sequence was created using Stata 9.0 (StataCorp, College Station, TX, USA) statistical software and was stratified by center with a 1:1 allocation using random block sizes of 2, 4, and 6."

"Participants were randomly assigned following simple randomization procedures (computerized random numbers) to 1 of 2 treatment groups."

We can apply the above examples to our case as follows: Randomization sequence was created using Excel 2007 (Microsoft, Redmond, WA, USA) with a 1:1 allocation using random block sizes of 2 and 4 by an independent doctor. In this way, sequence generation and type of randomization can be expressed at the same time.

Allocation Concealment Mechanism

"The doxycycline and placebo were in capsule form and identical in appearance. They were pre-packed in bottles and consecutively numbered for each woman according to the randomization schedule. Each woman was assigned an order number and received the capsules in the corresponding pre-packed bottle."

"The allocation sequence was concealed from the researcher (JR) enrolling and assessing participants in sequentially numbered, opaque, sealed and stapled envelopes. Aluminum foil inside the envelope was used to render the envelope impermeable to intense light. To prevent subversion of the allocation sequence, the name and date of birth of the participant was written on the envelope and a video tape made of the sealed envelope with participant details visible. Carbon paper inside the envelope transferred the information onto the allocation card inside the envelope and a second researcher (CC) later viewed video tapes to ensure envelopes were still sealed when participants' names were written on them. Corresponding envelopes were opened only after the enrolled participants completed all baseline assessments and it was time to allocate the intervention."

The second example was described in great detail, and we can guess how important the randomization and concealment were.

Who Generated the Allocation Sequence, Who Enrolled Participants, and Who Assigned Participants to Interventions?

"Determination of whether a patient would be treated by streptomycin and bed-rest (S case) or by bed-rest alone (C case) was made by reference to a statistical series based on random sampling numbers drawn up for each sex at each center by Professor Bradford Hill (this means that the stratification was done by sex and center); the details of the series were unknown to any of the investigators or to the coordinator. After acceptance of a patient by the panel, and before admission to the streptomycin center, the appropriate numbered envelope was opened at the central office; the card inside told, if the patient was to be an S or a C case, and this information was then given to the medical officer of the center."

"Details of the allocated group were given on colored cards contained in sequentially numbered, opaque, sealed envelopes. These were prepared at the NPEU and kept in an agreed location on each ward. Randomization took place at the end of the 2nd stage of labor when the midwife considered a vaginal birth was imminent. To enter a woman into the study, the midwife opened the next consecutively numbered envelope."

"Block randomization was by a computer generated random number list prepared by an investigator with no clinical involvement in the trial. We stratified by admission for an oncology related procedure. After the research nurse had obtained the patient's consent, she telephoned a contact who was independent of the recruitment process for allocation consignment."

If Done, Who Was Blinded after Assignment to Interventions and How

"Whereas patients and physicians allocated to the intervention group were aware of the allocated arm, outcome assessors and data analysts were kept blinded to the allocation."

"Blinding and equipoise were strictly maintained by emphasizing to intervention staff and participants that each diet adheres to healthy principles, and each of them is advocated by certain experts to be superior for long-term weight-loss. Except for the interventionists (dieticians and behavioral psychologists), investigators and staff were kept blind to diet assignment of the participants. The trial adhered to established procedures to maintain separation between staff that take outcome measurements and staff that deliver the intervention. Staffs who obtained outcome measurements were not informed of the diet group assignment. Intervention staffs, dieticians and behavioral psychologists who delivered the intervention did not take outcome measurements. All investigators, staffs, and participants were kept masked to outcome measurements and trial results."

In short, in a paper, we have to report who was kept blinded. In the case of physical therapy or surgery, keeping the surgeon blinded would be difficult or even impossible; however, blinding is possible for the person who measures the outcome. Anyhow, all individuals who were kept blinded must be described in the report.

  • WEBSITES AND SYSTEMS HELPING THESE PROCEDURES

To help with all the procedures of a fully qualified RCT, the following systems including electronic case report forms (eCRFs) are available for researchers.

iCReaT (clinical research and trial management system) in Korea Centers for Disease Control & Prevention (KCDC; http://icreat.nih.go.kr ): free for pre-educated and qualified researchers; there are regular education programs once a month, and some hospitals (for example, Severance Hospital) have their own educational programs. An English version will be available soon for non-Korean researchers.

MRCC ( https://mrcc.snuh.org ): for Seoul National University Hospital only. It is relatively inexpensive and includes statistical counseling.

Velos ( http://eresearch.ncc.re.kr ): a world-famous system and very expensive; it is available at National Cancer Center in Korea ( http://ncc.re.kr/crcc/ ).

eCRFs are very convenient as well as helpful to improve the quality of research and their advantages are summarized in the table ( Table 1 ).

Comparisons between Paper CRFs and e-CRFs of Web-based Clinical Research Management System

how to do random allocation in excel

CRF: case report form.

In RCT, random assignment is important and performing it is easy if you know how to do it. Besides the practice of randomization, correct reporting of the randomization process is also important and it should be done very accurately.

No potential conflict of interest relevant to this article was reported.

Full text links 

Read article at publisher's site: https://doi.org/10.4055/cios.2014.6.1.103

Citations & impact 

Impact metrics, citations of article over time, alternative metrics.

Altmetric item for https://www.altmetric.com/details/15088230

Smart citations by scite.ai Smart citations by scite.ai include citation statements extracted from the full text of the citing article. The number of the statements may be higher than the number of citations provided by EuropePMC if one paper cites another multiple times or lower if scite has not yet processed some of the citing articles. Explore citation contexts and check if this article has been supported or disputed. https://scite.ai/reports/10.4055/cios.2014.6.1.103

Article citations, participant comprehension and acceptability of enhanced versus text-only electronic informed consent: an innovative qualitative pilot study..

Corneli A , Starling S , Choi Y , Vosylius J , Madre L , Mackinnon A , Tenaerts P

Pilot Feasibility Stud , 10(1):10, 17 Jan 2024

Cited by: 0 articles | PMID: 38233932 | PMCID: PMC10792918

Can an educational intervention in the context of inpatient pulmonary rehabilitation improve asthma self-management at work? A study protocol of a randomized controlled trial.

Salandi J , Hayden MC , Heinrichs K , Limbach M , Schultz K , Schwarzl G , Neumeister W , Loerbroks A

BMC Pulm Med , 24(1):40, 17 Jan 2024

Cited by: 0 articles | PMID: 38233800 | PMCID: PMC10795332

Microbiological evaluation of conjunctival anopthalmic flora after using digital 3D-printed ocular prosthesis compared to conventional one: a randomized clinical trial.

Tahmawy YA , Mohamed FS , Elfeki S , Abd-Ellah ME

BMC Oral Health , 23(1):1012, 18 Dec 2023

Cited by: 0 articles | PMID: 38110937 | PMCID: PMC10729395

Protocol for a Randomized Controlled Trial Testing the Efficacy of a Transdiagnostic Virtual Reality-Based Intervention for the Reduction of Unhealthy Lifestyles and Behaviors in the General Population.

Gardini V , Ruini C , Tossani E , Grandi S , Tomba E

J Clin Med , 12(23):7470, 02 Dec 2023

Cited by: 0 articles | PMID: 38068522 | PMCID: PMC10707206

Preconception Maternal Mentoring for Improved Fetal Growth among Indonesian Women: Results from a Cluster Randomized Controlled Trial.

Hadi H , Nurunniyah S , Gittelsohn J , Alfiana RD , Fatimatasari , Lewis EC , Nurdiati D

Nutrients , 15(21):4579, 28 Oct 2023

Cited by: 0 articles | PMID: 37960232 | PMCID: PMC10649661

Similar Articles 

To arrive at the top five similar articles we use a word-weighted algorithm to compare words from the Title and Abstract of each citation.

The pursuit of balance: An overview of covariate-adaptive randomization techniques in clinical trials.

Lin Y , Zhu M , Su Z

Contemp Clin Trials , 45(pt a):21-25, 02 Aug 2015

Cited by: 42 articles | PMID: 26244705

Mobile access to virtual randomization for investigator-initiated trials.

Deserno TM , Keszei AP

Clin Trials , 14(4):396-405, 28 Apr 2017

Cited by: 1 article | PMID: 28452236

[Introduction of dynamic balancing randomization method and its application].

Liu ZL , Liu BQ , Li LH , Liu JP

Zhong Xi Yi Jie He Xue Bao , 9(3):246-251, 01 Mar 2011

Cited by: 0 articles | PMID: 21419076

Blocked randomization with randomly selected block sizes.

Int J Environ Res Public Health , 8(1):15-20, 23 Dec 2010

Cited by: 210 articles | PMID: 21318011 | PMCID: PMC3037057

Randomization in clinical studies.

Lim CY , In J

Korean J Anesthesiol , 72(3):221-232, 01 Apr 2019

Cited by: 55 articles | PMID: 30929415 | PMCID: PMC6547231

Europe PMC is part of the ELIXIR infrastructure

how to do random allocation in excel

Top Contributors in Excel: HansV MVP  -  Andreas Killer  -  Ashish Mathur  -  Jim_ Gordon  -  Rory Archibald   ✅

February 13, 2024

Top Contributors in Excel:

HansV MVP  -  Andreas Killer  -  Ashish Mathur  -  Jim_ Gordon  -  Rory Archibald   ✅

  • Search the community and support articles
  • Microsoft 365 and Office
  • Search Community member

Ask a new question

How do I remove random cell in middle of Excel Spreadsheet

I have a random box that sits in the middle, top portion of my spreadsheet/s. If I type in any active cell it mirrors what's being typed into that box. Can't seem to click on or highlight this box in question at all, nor does it appear on same spreadsheet/s when viewing them on a different device (e.g. Cell phone).

  • Subscribe to RSS feed

Report abuse

Replies (2) .

Waqas Muhammad MSFT

  • Microsoft Agent |

Good morning!

Thanks for posting in the Microsoft Community. We're happy to help. We're looking into your situation and will update you soon.

Appreciate their patience and understanding and   thank them for their time and cooperation.

Waqas Muhammad

Was this reply helpful? Yes No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

Thanks for your feedback.

Snow Lu MSFT

Could you share us a test file and show us an expected result? Then I will have a try.

Question Info

  • For business
  • Norsk Bokmål
  • Ελληνικά
  • Русский
  • עברית
  • العربية
  • ไทย
  • 한국어
  • 中文(简体)
  • 中文(繁體)
  • 日本語

how to do random allocation in excel

How to find and enable missing Copilot button in Microsoft 365 apps

Copilot is available in the Home ribbon tab of Word, Excel (Preview), PowerPoint, Outlook, and OneNote if you are a Copilot Pro subscriber who also has a subscription to Microsoft 365 Personal or Family.

If you have those subscriptions and are not seeing Copilot in these Microsoft 365 apps, follow these steps:

Important:  Copilot in Outlook only supports work or school accounts, and Microsoft accounts using outlook.com, hotmail.com, live.com, and msn.com email addresses at this time. Any Microsoft account using an account from a third-party email provider, such as a Gmail, Yahoo, or iCloud, can still use Outlook, but won’t have access to the Copilot features in Outlook.

Step 1 - Ensure you're using the correct account

Make sure you're signed into your Microsoft 365 apps with an account that has a Copilot license.

For home users ...this means you should have a Microsoft 365 Personal or Family subscription, and Copilot Pro.

For business users ...this means you should have a Microsoft 365 Business Standard, Business Premium, E3, or E5 subscription, and Microsoft Copilot for Microsoft 365.

Step 2 - Make sure that the current file is saved in that account

If you have multiple file locations available to you - such as your business OneDrive and your personal OneDrive - Copilot will only appear if the file you have open is saved to the location associated to your Copilot subscription.

For example, if you have Copilot through your work account, but not through your personal account, and you open a file from your personal OneDrive, Copilot won't work with that file.

For new, unsaved files, or files on your local hard drive, the current primary account shown at the top of the Microsoft 365 window must be licensed for Copilot.

A picture with a red arrow pointing to the current primary user name which is on the app title bar towards the top right of the window.

Important:  Copilot in Excel requires AutoSave to be turned on, which means the file has to be saved to OneDrive. It currently doesn't work with unsaved files.

Step 3 - Refresh your license

Force a refresh of the license.

Note:  These steps must be done by the account administrator.

 In any Microsoft 365 app, go to File > Account .

Locating the Update License button in Microsoft Word on Windows.

Close and restart all Microsoft 365 apps you might have open so that the license refresh can take effect.

  In any Microsoft 365 app, select the app name at the top of your screen.

Locating the Update License button in Microsoft Word on MacOS.

Close any open documents and restart all Microsoft 365 apps you may have open so the license refresh can take effect.

Ensure your apps are updated to their latest versions via your device's app store.

Close your app completely, and then re-launch it.

Refresh the page

Select the Refresh icon in your browser to see if the Copilot features appear.

The Refresh icon in Microsoft Edge.

You may have third-party cookies blocked in your browser, which currently causes Copilot to be unable to validate your license. If Copilot still isn't working for you try enabling third-party cookies. For more information see:  I am getting license validation errors with Copilot.

Microsoft Copilot help & learning

Facebook

Need more help?

Want more options.

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

how to do random allocation in excel

Microsoft 365 subscription benefits

how to do random allocation in excel

Microsoft 365 training

how to do random allocation in excel

Microsoft security

how to do random allocation in excel

Accessibility center

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

how to do random allocation in excel

Ask the Microsoft Community

how to do random allocation in excel

Microsoft Tech Community

how to do random allocation in excel

Windows Insiders

Microsoft 365 Insiders

Find solutions to common problems or get help from a support agent.

how to do random allocation in excel

Online support

Was this information helpful?

Thank you for your feedback.

IMAGES

  1. Excel Task Random Allocation: 1 Free & Effective Template

    how to do random allocation in excel

  2. To Do List

    how to do random allocation in excel

  3. Excel Task Random Allocation: 1 Free & Effective Template

    how to do random allocation in excel

  4. Excel Random Selection: How to Get Random Sample From a Dataset

    how to do random allocation in excel

  5. Job Allaction Excel : Budget Spreadsheet Definition How To Use And How To Create / In this case

    how to do random allocation in excel

  6. Resource Planning Excel Sheet ~ Excel Templates

    how to do random allocation in excel

VIDEO

  1. RANDOM ALLOCATION IN CADRES

  2. Excel tip to get a random list

  3. How to fill random cells with values

  4. MS Excel: Difference between Random and Randbetween Function #randomnumbers #exceltips

  5. Microsoft Excel

  6. February 3, 2024

COMMENTS

  1. How to Randomize a List in Microsoft Excel

    Use RAND to Randomize List Items in Excel. 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: =RAND() In the cell where you typed the function, you will see a random number.

  2. 5 Ways to Generate Random Numbers in Excel

    = RAND ( ) * ( Y - X ) + X In general, you can create a random number between X and Y by using the above formula. = RAND ( ) * 9 + 1 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.

  3. Randomly assign data to groups

    To randomly people (or anything) to groups you can use the RANDBETWEEN function with the CHOOSE function. In the example shown, the formula in F3 is: = CHOOSE ( RANDBETWEEN (1,3),"A","B","C") When copied down the column, this formula will generate a random group (A, B, or C) for each person in the list.

  4. How to Make Random Groups in Excel & Google Sheets

    First, we use the RAND Function to generate random decimal values between 0 and 1 for each person: =RAND() Now that we have random numbers for each person we have a way to split them into groups. RANK Function Next we will use the RANK Function to identify where each random value "ranks" in the list of random values. =RANK(A2,$A$2:$A$11)

  5. How to Generate Random Numbers in Microsoft Excel

    Go to File > Options and select "Add-ins" on the left. At the bottom of the window, go to Manage and pick "Excel Add-ins." Click "Go." When the Add-ins window opens, check the box next to Analysis ToolPak and click "OK." Use the Random Number Generator Head to the Data tab and click "Data Analysis" in the Analysis section of the ribbon.

  6. How to Generate Random Numbers in Excel

    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 () Hold the Control key and Press Enter. Select all the cell (where you have the result of the RAND function) and convert it to values. In the adjacent column, use the following ...

  7. Excel RANDARRAY function

    Basic RANDARRAY formula How to randomize in Excel - formula examples Generate random numbers between two numbers Generate random date between two dates Create random workdays in Excel Generate random numbers without duplicates Random sort in Excel Get a random sample Select random rows Random assignment in Excel Randomly assign data to groups

  8. Excel random selection: how to get random sample from dataset

    There's no built-in function to randomly pick cells in Excel, but you can use one of the functions to generate random numbers as a workaround. These probably cannot be called simple intuitive formulas, but they do work. How to select a random value from a list

  9. Randomly assign people to groups

    To randomly assign people to groups or teams of a specific size, you can use a helper column with a value generated by the RAND function, together with a formula based on the RANK and ROUNDUP functions. In the example shown, the formula in D5 is: = ROUNDUP ( RANK (C5, randoms) / size,0)

  10. RAND and RANDBETWEEN functions to generate random numbers in Excel

    For instance, if you want to get random numbers between 0 and 10, including 10, the right formula is =RAND ()*11. 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: RAND ()* ( B - A )+ A.

  11. How to generate random numbers in Excel with no repeats

    Where n is the number of random values you want to get.. For example, to create a list of 5 random numbers, use 5 for n: =SORTBY(SEQUENCE(5), RANDARRAY(5)) Enter the formula in the topmost cell, press the Enter key, and the results will spill automatically over the specified number of cells.. As you can see in the screenshot below, this formula actually sorts numbers from 1 to 5 in random order.

  12. RAND function

    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. Example

  13. How to Create a Random Sample in Excel: Tools & Tips

    1 Open the Microsoft Excel program. You can also open an existing Microsoft Excel document if you have one that correlates to your random sample needs. 2 Select Blank workbook. If you aren't opening a new document, skip this step. 3 Enter your data. To do this, click on a cell into which you wish to input data, then type in your data.

  14. Random sort in Excel: shuffle cells, rows and columns

    Insert a new column next to the list of names you want to randomize. If your dataset consists of a single column, skip this step. In the first cell of the inserted column, enter the RAND formula: =RAND () Copy the formula down the column. The fastest way to do this is by double-clicking the fill handle:

  15. Excel formula: Randomly assign people to groups

    =ROUNDUP (RANK (C5,random)/size,0) Figure 1: Illustration of ROUNDUP and RANK formula Here, "random" is the name of the range [C5:C20] and "size" is the named range[G10]. We can drag the formula and use the above formula for a hefty amount of data. Figure 2: Randomly assign groups

  16. Randomly Assign Names To Groups In Excel

    To do this, we first assign each person a random number u... We assign each of the names in our table to a random group, based on the group size that we select.

  17. Quickly generate random groups for list of data in Excel

    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 ")

  18. Randomization in Microsoft Excel Sheet

    In this video tutorial, I will show all of you how to do randomization of samples by Random Allocation method while doing a clinical study/ clinical trial. I...

  19. Random allocation using Excel

    How to randomly allocate 100 subjects into two groups of size 50 using excel.

  20. Using Excel for Random Assignment

    Use Microsoft Excel to randomly assign participants to groups.

  21. How to Randomly Assign Participants to Equal Sized Groups in Excel

    Download the featured file here: https://www.bluepecantraining.com/how-to-randomly-assign-participants-to-equal-sized-groups-in-excel-group-generator-no-repe...

  22. How to Do Random Allocation (Randomization)

    Go to: Abstract Purpose To explain the concept and procedure of random allocation as used in a randomized controlled study. Methods We explain the general concept of random allocation and demonstrate how to perform the procedure easily and how to report it in a paper.

  23. How to do random allocation (randomization).

    We can apply the above examples to our case as follows: Randomization sequence was created using Excel 2007 (Microsoft, Redmond, WA, USA) with a 1:1 allocation using random block sizes of 2 and 4 by an independent doctor. In this way, sequence generation and type of randomization can be expressed at the same time.

  24. How do I remove random cell in middle of Excel Spreadsheet

    I have a random box that sits in the middle, top portion of my spreadsheet/s. If I type in any active cell it mirrors what's being typed into that box. Can't seem to click on or highlight this box in question at all, nor does it appear on same spreadsheet/s when viewing them on a different device (e.g. Cell phone).

  25. How to find and enable missing Copilot button in Microsoft 365 apps

    Important: Copilot in Outlook only supports work or school accounts, and Microsoft accounts using outlook.com, hotmail.com, live.com, and msn.com email addresses at this time. Any Microsoft account using an account from a third-party email provider, such as a Gmail, Yahoo, or iCloud, can still use Outlook, but won't have access to the Copilot features in Outlook.