How to Randomize in Excel? – 5 Methods

This is an overview.

Overview of Randomize in Excel

 


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

RAND function inn Excel

  • Go to C5 and enter the formula:

= RAND()

Using Rand Function

To convert the random decimal numbers to percentages:

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

Turn Random number-into percentage


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

RANDARRAY in Excel

SORTARRAY in Excel

  • Use the formula.

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

Randomize and Sort

 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

RANDBETWEEN function in Excel

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

=RANDBETWEEN(100,106)

Randomize with RANDBETWEEN Function

Read More: How to Shuffle Numbers in Excel


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

INDEX function in Excel

Click here to enlarge image

  • To choose a random major, use the formula.

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

Randomize with INDEX and RANDBETWEEN

 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

CHOOSE function in Excel

Click here to enlarge the image

  • Use the formula below.

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

Randomize dataset using CHOOSE and RANDMIZE function

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

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!

Tags:

Joyanta Mitra
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo