This is an overview.
Method 1 – Using the RAND Function to Get a Random Decimal Number 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
- Use the formula.
=SORTBY(B5:B8,RANDARRAY(COUNTA(B5:B8)))
Formula Breakdown
Method 3 – Using 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
- To choose a random major, use the formula.
=INDEX(B5:C8,RANDBETWEEN(1,4),2)
Formula Breakdown
Method 5 – Using the CHOOSE and the RANDBETWEEN Functions to Randomize a List
- Use the formula below.
=CHOOSE(RANDBETWEEN(1,4),"A","B","C","D")
Formula Breakdown
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
Get FREE Advanced Excel Exercises with Solutions!