Randomize List in Excel

How to Randomize List in Excel?

Randomizing a list in excel means selecting a random value from the data, to randomize a whole list in excel there are two excel formulas one is the =RAND() function which gives any of the random values to the cell and then we can sort the list or we can use the =RANDBETWEEN() function which gives random values to the cell from the number range a user provides.

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

I don’t know whether you are aware or not; we can insert random numbers in the list from 0 to less than 1 in excel by using the RAND function. Using the numbers generated by the RAND function, we can sort the list of numbers randomly.

RAND function does not have any kind of parameters to satisfy. We just need to enter the formula and close the bracket. RAND function automatically assumes the parameter is greater than 0 and less than 1.

Randomize List example 1

In the above example, I have just entered formulas.

I have a list of some products, and I want to sort the data randomly for some confidentiality.

Randomize List example 1-1

If I try to sort the data based on the Product name, it will sort either from A to Z or from Z to A. If I try to sort the data based on Gross Sales, it will sort either from Lowest to Highest or from Highest to Lowest.

But I want neither of them to happen; I Want to sort randomly.

Below are the steps to insert random numbers in excel –

  1. Apply RAND function in the immediate after the Gross SalesGross SalesGross Sales, also called Top-Line Sales of a Company, refers to the total sales amount earned over a given period, excluding returns, allowances, rebates, & any other discount. read more column. Name the new column as Helper.

    Randomize List step 1

  2. Remember, RAND is a volatile function and keeps changing the values after every action in excel. So remove the formula by using Paste Special.Using Paste Special.Paste special in Excel allows you to paste partial aspects of the data copied. There are several ways to paste special in Excel, including right-clicking on the target cell and selecting paste special, or using a shortcut such as CTRL+ALT+V or ALT+E+S.read more

  3. After paste values, select the entire data range and go to the DATA tab.

  4. Under the data tab, select the SORT option.

    Randomize List step 2

  5. Now, under Sort by select the newly inserted column, i.e., Helper

    Randomize List step 3

  6. In the same window, under Order, select the option you want. Because we are sorting manually, we can select any one of the available options. I have selected the Smallest to Largest.

    Randomize List step 4

  7. Click on the OK button. It will sort the data randomly.

    Randomize List step 5

#2 Pick a Lottery Winner with Randomize List in Excel

If you have seen the lottery industry, they will pick the winner randomly without any kind of bias. By using excel and by using the RAND function, we can also pick a winner.

I have a few names and their lottery ticket numbers; we need to pick a lottery winner from this lot.

Pick Lottery Winner Randomly Step 1

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

By using the RAND function, I will generate some numbers against each name. The problem is RAND can give me all the values which are less than 1, so I will multiply the number 100 with the numbers generated by the RAND function.

Pick Lottery Winner Randomly Step 1-1

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

Pick Lottery Winner Randomly Step 1-2

Now I have the score ready against each name. Criteria are whoever gets the maximum score will be declared as the Winner. I will sort the numbers from largest to smallest.

  • Step 1: Select the data, and Under data, the tab selects the SORT option.
Pick Lottery Winner Randomly Step 1-3
  • Step 2: Now, under Sort by select the column SCORE.
Pick Lottery Winner Randomly Step 1-4
  • 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.
Pick Lottery Winner Randomly Step 1-5
  • Step 4: Click on the OK; it will sort the data from Largest to Smallest of the scoring column.
Pick Lottery Winner Randomly Step 1-6

Wow!!! We have a winner here, and the winner is Ramela, who got the highest score when we have applied the RAND function.

Things to Remember

This has been a guide to the Randomize List in Excel. Here we discuss how to Insert a random number by using the Rand function and to Pick a Lottery Winner Randomly, along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion