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.

**Table of Contents**Expand

## 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:

`= RAND()`

- 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:

**
**

`=SORTBY(B5:B8,RANDARRAY(COUNTA(B5:B8)))`

**🔎 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.

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

`=RANDBETWEEN(100,106)`

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

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

`=INDEX(B5:C8,RANDBETWEEN(1,4),2)`

**🔎 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**.

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

`=CHOOSE(RANDBETWEEN(1,4),"A","B","C","D")`

**🔎 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**

## 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:

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