How to Randomize List in Excel?

#1 Insert Random Numbers in the List by Using RAND Function

We do not know whether you know that we can insert random numbers in the list from 0 to less than 1 in Excel using the RAND functionUsing The RAND FunctionThe RAND function in Excel, also known as the random function, generates a random value greater than 0 but less than 1, with an even distribution among those numbers when used on multiple cells. read more. Then, using the numbers generated by the RAND function, we can sort the list of numbers randomly.

The RAND function does not have any parameters to satisfy. Therefore, we need to insert the formula and close the bracket. The RAND function automatically assumes the parameter is greater than 0 and less than 1.

In the above example, we have just entered formulas.

We have a list of products which we want to sort randomly for some confidentiality.

If we try to sort the data based on the product name, it will sort either from A to Z or from Z to A. If we try to sort the data based on gross sales, it will sort from lowest to highest or from highest to lowest.

But we want neither of them to happen. We want to sort randomly.

#2 Pick a Lottery Winner with Randomize List in Excel

If you have seen the lottery industry, they will randomly pick the winner without bias. We can also select a winner using Excel and the RAND function.

  • Apply the RAND function immediately after the “Gross Sales” column. Then, name the new column “Helper.” Remember, the RAND function is volatile and keeps changing the values after every action in Excel. So, remove the formula by using the Paste Special. After pasting values, select the entire data range and go to the “Data” tab. Under the “Data” tab, select the “Sort” option. Now, under the “Sort by,” select the newly inserted column, “Helper.” In the same window, under “Order,” select the option you want. Because we are sorting manually, we can choose any available options. We have chosen the “Smallest to Largest.” Click on the “OK” button. It will sort the data randomly.

We have a few names and lottery ticket numbers. We need to pick a lottery winner from this lot.

The criteria to pick a winner is to generate random numbers from 1 to 100 against each name, and whoever gets the maximum score will be declared the winner.

Since the RAND function is volatile, we will remove the formula and retain only values.

Now, we have the score ready against each name. The criteria are whoever gets the maximum score will be declared the winner. We will sort the numbers from largest to smallest.

  • Step 1: Select the data, and under the “Data” tab, selects the “Sort” option.

  • Step 2: Under the “Sort by,” select the column “Score.”

  • Step 3: Now comes the important part of the process. Under “Order,” we need to select the criteria as “Largest to Smallest” so that the largest value from the list comes first.

  • Step 4: Click on the “OK” button. It will sort the data from largest to smallest of the scoring column.

Now, we have a winner here. The winner is Ramela, who got the highest score when we applied the RAND function.

Things to Remember

  • Both the RAND and RANDBETWEENRANDBETWEENRANDBETWEEN excel formula determine random numbers between two extreme variables (bottom and top numbers). The user needs to fill in the bottom and top numbers in the syntax =RANDBETWEEN (bottom, top) to acquire the random integer.read more are volatile functions. It will slow down the workbook considerably.Both the functions recalculate values when there is any change that occurs in the worksheets.Using the RAND function, we can generate random numbers greater than 0 but less than 1.Do not keep volatile functions in any of the workbooks.

This article is a guide to the Randomize List in Excel. Here, we discuss inserting a random number using the RAND function and picking a lottery winner, along with practical examples and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Column Sort in ExcelRandom Numbers in ExcelRemove Space in ExcelRandbetween in Excel