How to Randomize a List in Excel [Beginner's Guide 2023] (2023)

Randomizing Lists in Excel is useful when you want to do a lot of experiments or demos with your app. However, if you are just getting started with Excel, you may not know how to randomize a list in Excel.Understanding how to use Excel databases and formulas is important if you're working with data or considering a career in data. Data analysis with Excel often also requires randomizing a list.

As someone with years of Excel experience, I randomized many lists while doing different kinds of tasks. In this tutorial, I'll introduce different methods you can use to randomize lists in Excel.

So, read this article till the end to learn how to randomize a list in Excel.

You may also want to read how to do this.Lock and unlock cells in Excel?

Why is randomization important in an Excel experiment?

In an experiment, randomization refers to the random assignment of values. If you're working in Excel, you may want to create a random list to test a hypothesis.

The results of an experiment will be good thanks to randomization that eliminates bias. To ensure that the test results are as accurate as possible, care is taken to ensure that the groups formed to perform the test are as similar as possible.

In addition, it helps control for undetected factors that may cause results to be different than expected.

The randomly selected sample is intended to be representative of the population, and since there was no interference from the investigators, the outcome was decided quite sensibly.

By randomizing experiments, the most precise cause-and-effect relationships between variables can be obtained.

So in Excel, random lists can remove bias and you can test your hypothesis on random values. You can also use the random list feature to distribute tasks more efficiently.

It ensures that all genders, castes and races are represented in the random selection and that the groupings are not too diverse.

The researchers use a randomized approach to control for the values ​​of the explanatory variables. Thus, by looking at it, we can infer a causal relationship between the explanatory and response variables.

Related content you must readInstall Macro in Excel?

What are the functions to randomize a list in Excel?

There is more than one function you can use when you want to randomize a list in Excel. Some of the functions are RAND, RANDBETWEEN, RANDARRAY, etc. I will discuss some of them here so that you can understand the arguments well.

These are the functions used to randomize a list:

1. ALTERNATIVE

RAND generates a randomly generated real number greater than or equal to 0 and less than 1 that is uniformly distributed. A new random real number is returned each time the spreadsheet is calculated.

Starting with Excel 2010, the Mersenne-Twister algorithm is used to generate random numbers. There are no arguments in the RAND function syntax.

Forbidden=RAND()in the formula bar and press F9 to change the formula to a random number if you want to generate a random number with RAND but don't want the numbers to change each time the cell is calculated. You only get a value after the formula has calculated it.

If you want to increase the limit of numbers generated, use=RAND()*X,where X is the upper bound you want to set.

Use =INT(RAND()*X) to make sure that the generated numbers are integers, which you can know as integers.

Follow our guide to learn how.Copy values ​​to Excel without formulas?

2. BORDER BETWEEN

RAND BETWEEN returns a random integer value within the range of integers you specify. A new random integer is returned each time the spreadsheet is calculated.

While RAND generates a random set of real numbers between 0 and 1, unless the upper bound is changed, RANDBETWEEN can only return random integers. However, you can specify upper and lower bounds as input arguments.

=BETWEEN(lower, upper) iThis is the syntax for this function when you type it in the formula bar. Instead of Bottom and Top, use integer values, where the second argument, Top, is the largest number and the first argument, Bottom, is the smallest number.

Any calculation that uses the RAND function is updated with a new random number when the worksheet is recalculated manually (press F9), by pasting a formula or data into a separate cell, or both.

3. DISPOSAL

An array of random numbers is the output of the RANDARRAY function. You can set the minimum and maximum values, the number of rows and columns to fill, and whether to return integer or decimal values.

It is a very flexible and powerful function.

=RANDARRAY([row],[column],[min],[max],[set])this is what the syntax looks like. All arguments are optional.

Here are the arguments explained:

• SubstituteLinewith the number of rows to return.
• SubstituteTo sharewith the number of columns you want to return.
• SubstituteMinimumwith the minimum number you want to return.
• Substitutemaximumwith the maximum number you want to return.
• SubstitutePhrasewith logical value. it has to be orright or wrong. True returns numbers as integers, and False returns numbers as real numbers.

RANDARRAY returns a single value between 0 and 1 if you do not specify a row or column argument. The default values ​​for RANDARRAY are 0 and 1, respectively, if no minimum or maximum value parameter is specified.

Otherwise, RANDARRAY will produce a #VALUE! Mistake. The minimum number argument must be less than the maximum number. RANDARRY defaults to FALSE or a decimal value if you do not specify an integer parameter.

The RAND function does not produce an array, so RAND would have to be copied over the entire range. RANDARRAY is different in that it creates an array.

look the easy wayFix Microsoft Excel Freezing or Slowing Down.

How to randomize a list in Excel?

If you understood all the features described in the previous section, you probably already know how to randomize a list in Excel.

Just to confirm, I'll show an example with each function. Applying a function to a formula and then duplicating that formula in other cells in a column is the bulk of the work required. Again, a deep understanding of the function is required to know what it is doing.

Here are methods to randomize a list in Excel:

1. Use of RAND

You can use the RAND function to generate random real numbers between 0 and 1. However, you can modify the formula to change the type of number or the upper and lower bounds.

Let's try to generate 10 random integers from 1 to 50 using the RAND function.

Follow these steps to generate 10 random integers from 1 to 50 using the RAND function:

• Add a new column next to the list of names you want to randomize. Skip this step if your data set only contains a single column.
• Enter the RAND formula in the first cell of the newly added column: =INT(RAND()*50)
• Duplicate the formula in the column. You can double-click the fill handle to quickly complete this action. For this example, fill in the next 9 entries in the column.

You would get decimal values ​​if you just use the RAND function without the INT function. The option to get decimal values ​​is not available for the following function.

Also in relation to this manual.

2. RANDOM use

The RANDOM function does not generate real numbers. Let's use this function to generate 10 random integers between 10 and 50.

Follow these steps to generate 10 random integers from 10 to 50 using the RANDBETWEEN function:

• Add a new column next to the list of names you want to randomize. Skip this step if your data set only contains a single column.
• Enter the RAND formula in the first cell of the newly added column: =RANDBETWEEN(10,60)
• Duplicate the formula in the column. You can double-click the fill handle to quickly complete this action. For this example, fill in the next 9 entries in the column.

RANDBETWEEN is useful when working with integer values ​​within a fixed range. Continue with the formula below if you want to add more control.

look the easy wayRandomize photos in a folder?

3. Use of RANDARRAY

The RANDARRAY function is much more powerful than the previous two functions.

You can specify what type of number you want to generate and how many to produce with a fixed number of rows and columns, with the maximum and minimum of the numbers generated by this function being adjustable.

Using this function, let's try to find 24 random integers from 1 to 100 spread over 6 rows and 4 columns.

Follow these steps to use the RANDARRAY function to generate 24 random integers from 1 to 100 spread over 6 rows and 4 columns:

• Add a new column next to the list of names you want to randomize. Skip this step if your data set only contains a single column.
• Enter the RANDARRAY formula in the first cell of the newly added column: =RANDARRAY(6,4,1,100,TRUE)

You can combine this function with others to do even more amazing things in Excel. This feature is only available in Excel 2019.

Also check out our separate postPS5 friends list not showing up.

frequent questions

How to sort a random list in Excel?

Values ​​in a related range are used to sort a range using the SORT BY function. The SORT function always sorts in ascending order. Look up the names of the functions for more information.

How to index a random list in Excel?

You can index a random list in Excel using the INDEX function. The INDEX function returns a value or a reference to a value in a table or range. Look up the name of the function for more information.

How to sort an already generated random list in Excel?

CliqueSort and Filter > Sort Lowest to HighestunderHeimEyelash. If you prefer, you can selectSort from largest to smallest.

Diploma

You can use the RAND, RANDBETWEEN, and RANDARRAY functions in Microsoft Excel to rearrange items in a list in random order. The functions generate random numbers that you can use to randomly select items from your list.

Use RANDARRAY if you have the function in your version of Excel. It gives you a lot of flexibility in terms of which list to create.

If you have any other questions, comment below!