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:

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.

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:

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.

Click here to enlarge image

• To choose a random major, use the formula.

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

Combine the CHOOSE and the RANDBETWEEN functions.

Click here to enlarge the image

• Use the formula below.

Read More: How to Use Random Number Generator in Excel

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.

Download Practice Workbook

Frequently Asked Questions

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