How to Randomize in Excel? (5 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we are going to randomize Excel data and show the real-world use of randomizing data using five suitable methods in Excel. These methods are given below: using RAND, RANDARRAY, RANDBETWEEN functions, applying the combination of INDEX and RANDBETWEEN functions and combination of CHOOSE and RANDBETWEEN functions.

While preparing this article, we used Microsoft 365 for applying all operations but they are also applicable in all Excel versions.

Randomizing data in Excel is a helpful technique that allows you to shuffle or rearrange the order of your data. This can be handy when creating a randomized sample or adding some variation to your data set. Randomizing data can help in various scenarios, such as conducting experiments, selecting survey participants, or generating random numbers for simulations. By randomizing the data, you can eliminate any biases in the original order of your data set. This ensures that each value has an equal chance of appearing in any position, creating a fair and unbiased representation of your data.

We have given an example using the image below. But in this article, we will discuss the matter thoroughly.

Overview of Randomize in Excel

How to Randomize in Excel?

In this section, we will dive into the details of the methods of randomizing in Excel. These methods include: using the RAND function, RANDARRAY function, RANDBETWEEN function, applying the combination of INDEX and RANDBETWEEN function, and combination of CHOOSE and RANDBETWEEN function.

1. Using RAND Function to Get Random Decimal Number Between 0 and 1

The RAND function is used for getting random values from 0 to 1. Here we have used the RAND function to get values between 0 and 1.

RAND function inn Excel

  • In this section, we have to use the RAND function in the C5 cell to get a random number. The formula is:

= RAND()

Using Rand Function

  • We can convert these random decimal numbers to percentages. Thus, we can get Obtained Marks in percentage. To do that, we apply the cells of column C to the Percentage format from the Number ribbon group.

Turn Random number-into percentage

2. Randomize and Sort a List with SORTBY and RANDARRAY Functions

The RANDARRY function generates a random array and the SORTBY function generates a sorted array using the random array, We are going to use both functions to randomize and sort the array. We have given a brief review of how to use SORTBY and  RANDARRAY functions to randomize a list.



  • We have used nested SORTBY and RANDARRAY functions to randomize and sort column D, the formula is:


Randomize and Sort

🔎 Formula Explanation

  • COUNTA(B5:B8): This part of the formula counts the number of non-empty cells in the range B5 to B8. The COUNTA function is commonly used to determine the number of cells that contain any value (text, numbers, dates, etc.) within a given range. Here, the output of the COUNTA(B5:B8) is 4.
  • RANDARRAY(COUNTA(B5:B8)): The RANDARRAY function generates an array of random numbers. In this case, it generates a random array with the same number of elements as the count of non-empty cells in the range B5 to B8. Each element of the array will be a random number between 0 and 1.
  • SORTBY(B5:B8, RANDARRAY(COUNTA(B5:B8))): This is the main part of the formula. The SORTBY function is used to sort the values in the range B5 to B8 based on the random array generated by RANDARRAY. The SORTBY function takes two arguments: the range to be sorted (B5:B8) and the sort order (RANDARRAY(COUNTA(B5:B8))). Since the random array is used as the sort order, the values in the range will be sorted randomly.

3. Using RANDBETWEEN Function to Generate Random Integers

In this section, we will use the RANDBETWEEN function to generate random integers. The function with its arguments is given below.

RANDBETWEEN function in Excel

  • We have to use the RANDBETWEEN function to get a random number between 100 to 106. The formula is:


Randomize with RANDBETWEEN Function

Read More: How to Shuffle Numbers in Excel

4. Applying INDEX and RANDBETWEEN Functions to Create Randomized Samples

Using the combination of the INDEX function and RANDBETWEEN function we can generate random numbers. Here we have given a brief review of the INDEX function in Excel.

INDEX function in Excel

Click here to enlarge image

  • Here we have to use the INDEX and RANDBETWEEN functions to pick a random major. The formula is


Randomize with INDEX and RANDBETWEEN

🔎 Formula Explanation

  • RANDBETWEEN(1,4): This part of the formula generates a random number between 1 and 4 using the RANDBETWEEN function. The RANDBETWEEN function is commonly used to generate random integers within a given range.
  • INDEX(B5:C8, RANDBETWEEN(1,4), 2): This is the main part of the formula. The INDEX function is used to retrieve a value from the range B5:C8 based on the randomly generated row number and column number. The arguments for the INDEX function are as follows:
  • B5:C8: This is the range from which we want to retrieve a value.
  • RANDBETWEEN(1,4): This is the randomly generated row number. It can be any value from 1 to 4.
  • 2: This specifies the column number from which we want to retrieve the value. In this case, it is the second column in the range B5:C8.

5. Using CHOOSE and RANDBETWEEN Functions to Randomize a List

Combination of CHOOSE and RANDBETWEEN functions to randomize list. There is a brief review to the function CHOOSE.

CHOOSE function in Excel

Click here to enlarge the image

  • Here we will use the CHOOSE and RANDBETWEEN function to randomize the group number. We have used the formula below


Randomize dataset using CHOOSE and RANDMIZE function

🔎 Formula Explanation

  • RANDBETWEEN(1,4): This part generates a random integer between 1 and 4 using the RANDBETWEEN It serves as the index number for the CHOOSE function.
  • CHOOSE(RANDBETWEEN(1,4), “A”, “B”, “C”, “D”): The CHOOSE function is used to select one of the values provided as arguments, based on the index number generated by RANDBETWEEN. In this case, the index number is randomly generated between 1 and 4.
  • “A”, “B”, “C”, “D”: These are the values or options from which the CHOOSE function selects one. In this example, there are four options: “A”, “B”, “C”, and “D”. For example, if RANDBETWEEN generates a value of 3, the formula will return “C”.

Read More: How to Use Random Number Generator in Excel

Which Things Should You Remember?

When working with randomization in Excel, here are some important things to remember:

  • Volatility of random functions: Functions like RAND, RANDBETWEEN, and RANDARRAY are considered volatile functions in Excel. This means that they recalculate each time any change is made to the worksheet. Keep this in mind when working with large datasets or complex formulas.
  • Copy and Paste Values: After randomizing data using formulas or sorting, it’s often a good practice to copy and paste the randomized values as static values. This ensures that the random order is preserved even when the worksheet is recalculated or formulas are modified.
  • Save a copy of the original data: Before applying any randomization methods, make sure to save a copy of the original data. Randomization permanently changes the order of the data, and having a backup can be helpful if you need to revert to the original order.
  • Test and verify: Always test the randomization method on a small sample of data before applying it to the entire dataset. Verify that the randomization produces the desired results and that the distribution of randomized data is appropriate for your needs.

Download Practice Workbook


In conclusion, we’ve explored various methods of introducing randomness into Excel. These methods include using the RAND function to generate random decimal numbers, the RANDARRAY function for creating arrays of random values, the RANDBETWEEN function for generating random integers within a specified range, and more advanced techniques like combining INDEX with RANDBETWEEN or using CHOOSE with RANDBETWEEN. These tools offer a wide range of options to incorporate randomness into your Excel work, making it a versatile tool for tasks requiring randomization, simulation, or experimentation. By understanding and applying these methods, you can harness the power of randomness in Excel for data analysis, modeling, and decision-making.

Frequently Asked Questions

1. What to do if you encounter errors while randomizing data in Excel?

If you encounter errors while randomizing data in Excel, here are some steps you can take to troubleshoot and fix the issues:

  1. Verify the formula: Double-check the formula you are using for randomization. Ensure that all function names, parentheses, and references are correct. Make sure you are using the appropriate functions, such as RAND, RANDBETWEEN, INDEX, or SORTBY depending on your specific requirement.
  2. Check for invalid data: If your data contains any empty cells or non-numeric values in a range that is being used for randomization, it can cause errors. Make sure your data is clean and there are no inconsistencies.
  3. Refresh calculations: If you are using functions like RAND or RANDBETWEEN(), try refreshing the calculations. You can do this by pressing Ctrl + Alt + F9 or going to “Formulas” > “Calculation Options” > “Calculate Now” to recalculate all formulas in the workbook.
  4. Adjust range references: Ensure that your range references are correct. If you insert or delete rows or columns, the range references may need to be updated accordingly to include all the necessary data.
  5. Enable iterative calculation: If your randomization involves circular references, you might need to enable iterative calculation in Excel. Go to “File” > “Options” > “Formulas” and check the “Enable iterative calculation” box. Adjust the maximum iterations and maximum change values as needed.

2. How can we shuffle or randomize the order of worksheets in an Excel workbook?

No, Excel does not provide any built-in function or feature to shuffle or randomize the order of worksheets within a workbook. However, you can shuffle by using VBA programming. You can write a VBA macro that loops through the worksheets, assigns a random number to each worksheet, and then sorts the worksheets based on the assigned random numbers.

3. How can we create a random password generator in Excel?

To create a random password generator in Excel, you can use a combination of functions like RAND, RANDBETWEEN, CHAR, and CONCATENATE. For example, if you want to generate a random password consisting of a combination of letters, numbers, and special characters, you will use those functions and combine them with the CONCATENATE function

Randomize in Excel: Knowledge Hub

<< Go Back to Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.


Joyanta Mitra
Joyanta Mitra

Joyanta Mitra, a BSc graduate in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, has dedicated over a year to the ExcelDemy project. Specializing in programming, he has authored and modified 60 articles, predominantly focusing on Power Query and VBA (Visual Basic for Applications). His expertise in VBA programming is evident through the substantial body of work he has contributed, showcasing a deep understanding of Excel automation, and enhancing the ExcelDemy project's resources with valuable... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF