# How to Randomize in Excel? – 5 Methods

The RAND, RANDARRAY, RANDBETWEEN functions will be used, combined with theÂ INDEX, RANDBETWEEN and CHOOSEÂ functions.

This is an overview.

### Method 1 – Using the RAND Function to Get a Random Decimal Number Between 0 and 1

The RAND function is used to get values between 0 and 1.

• Go to C5 and enter the formula:

`= RAND()`

To convert the random decimal numbers to percentages:

• Select column C.
• In Number, choose Percentage.

### Method 2 – Randomize and Sort a List with the SORTBY and the RANDARRAY Functions

• Nest the SORTBY and RANDARRAY functions to randomize and sort column D.
• Use the formula.

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

Â Formula Breakdown

• COUNTA(B5:B8): counts the number of non-empty cells in B5:B8. Â The outputÂ is 4.
• RANDARRAY(COUNTA(B5:B8)): Â generates a random array with the same number of elements as the count of non-empty cells in B5: B8. Each element of the array will be a random number between 0 and 1.
• SORTBY(B5:B8, RANDARRAY(COUNTA(B5:B8))): Â sorts the values in B5:B8 based on the random array. The 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, values will be randomly sorted.

### Method 3 – Using the RANDBETWEEN Function to Generate Random Integers

Use the RANDBETWEEN function to generate random integers.

• To get a random number between 100 to 106, use the formula:

`=RANDBETWEEN(100,106)`

Read More: How to Shuffle Numbers in Excel

### Method 4 – Applying the INDEX and the RANDBETWEEN Functions to Create Randomized Samples

Combine the INDEX function and RANDBETWEEN function.

• To choose a random major, use the formula.

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

Â Formula Breakdown

• RANDBETWEEN(1,4): generates a random number between 1 and 4.
• INDEX(B5:C8, RANDBETWEEN(1,4), 2): retrieves a value in B5:C8 based on the randomly generated row number and column number. The arguments are:
• B5:C8: the range from which we want to retrieve a value.
• RANDBETWEEN(1,4): the randomly generated row number (any value from 1 to 4).
• 2: specifies the column number from which we want to retrieve the value (the second column inÂ  B5:C8).

### Method 5 – Using the CHOOSE and the RANDBETWEEN Functions to Randomize a List

Combine the CHOOSE and the RANDBETWEEN functions.

• Use the formula below.

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

Â Formula Breakdown

• RANDBETWEEN(1,4): generates a random integer between 1 and 4, whichÂ serves as the index number for the CHOOSE function.
• CHOOSE(RANDBETWEEN(1,4), “A”, “B”, “C”, “D”): selects one of the values provided as arguments, based on the index number generated by RANDBETWEEN.
• “A”, “B”, “C”, “D”: are the valuesÂ CHOOSE from. If the RANDBETWEEN function generatesÂ 3, the formula will return “C”.

## 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.
• Copy and Paste Values: After randomizing data using formulas or sorting, it’s a good practice to copy and paste the randomized values as static values. This ensures that the random order is preserved.
• Save a copy of the original data: Before applying any randomization methods, save a copy of the original data.
• Test and verify: Test the randomization method on a small data sample before applying it to the entire dataset.

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

• Verify the formula.
• Check for invalid data: empty cells or non-numeric valuesÂ  can cause errors.
• Refresh calculations: PressÂ Ctrl + Alt + F9 or go 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.
• Enable iterative calculation: If your randomization involves circular references, enable iterative calculation in Excel: Go to “File” > “Options” > “Formulas” and check “Enable iterative calculation”. Adjust the maximum iterations and maximum change values.

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

Excel does not provide any built-in function or feature to shuffle or randomize the order of worksheets within a workbook. Use a VBA macro to loop through the worksheets, assign a random number to each worksheet, and sort them based on the assigned random numbers.

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

Combine the RAND, RANDBETWEEN, CHAR, and CONCATENATE functions.

## Randomize in Excel: Knowledge Hub

<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

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

Advanced Excel Exercises with Solutions PDF