How to Randomize in Excel? (5 Effective Ways)

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.

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.

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

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

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:

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.

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

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.

Click here to enlarge image

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

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.

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

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

Conclusion

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