Categories
Excel

Random Sort using Rand() Function

The RAND function is a volatile function, you can use this volatility to your benefit and record a macro that sorts data immediately after you recalculate and force the RAND function to return another set of random numbers. You then can attach this macro to a button so that each time you want to shuffle (random sort) data, all you need to do is click the button.

  1. Understanding RAND() Function
  2. Generate Random Numbers Within A Specific Range
  3. Random Sort

RAND() Function

Excel RAND() function generates a random decimal number between 0 and 1. Type RAND() in the cell where you want the random number to appear, Excel will generate a random decimal number between 0 and 1 in the selected cell.

The RAND function is a volatile function that will recalculate automatically whenever an action takes place in Excel e.g., entering data somewhere else, or forcing a recalculation of the worksheet by pressing F9.

You can copy and paste the formula to other cells to generate additional random numbers.

If you want to generate a new random number, you can press F9 or recalculate the worksheet. It’s important to note that each time the worksheet is calculated, all instances of the RAND function will be recalculated, resulting in different random numbers. If you want to keep the generated random numbers static, you can use the Paste Special feature to convert the formulas to values.

Generate Random Number Between 1 and 100

If you want to generate random numbers within a specific range, you can use formulas to manipulate the random numbers generated by the RAND() function. Assume you want to generate random whole numbers between 1 and 100. Here’s the formula:

=INT( RAND() * 100 ) + 1

Here’s how it works:

  1. The RAND() function generates a random decimal number between 0 and 1.
  2. RAND()*100:
    We multiply the random decimal number generated by RAND() by 100. This step scales the random number to a range between 0 and 100.
  3. The INT() function rounds down a number to the nearest integer. In this case, it ensures that the decimal portion of the multiplied random number is discarded, giving us a whole number between 0 and 99.
  4. We add 1 to the result of the INT() function. This shifts the range of the generated random number from 0-99 to 1-100. So, the final result will be a random whole number between 1 and 100.

Random Sort

Assume you have a three-column table in your spreadsheet, starting from column B. You can place the RAND() function in cell A2 and copy this down as many rows as needed, all the way to the end of your table. As soon as you do this, each cell in column A containing the RAND function will automatically return a random number by which you can sort the table. In other words, you can sort columns A, B, C, and D by column A in either ascending or descending order.

The RAND function is a volatile function, you can use this volatility to your benefit and record a macro that sorts data immediately after you recalculate and force the RAND function to return another set of random numbers. You then can attach this macro to a button so that each time you want to shuffle (random sort) data, all you need to do is click the button.

For example, assume you have your data in columns B, C, and D and that row 1 is used for headings:

  1. First, place the heading RAND in cell A1. Enter =RAND( ) in cell A2 and copy down as far as needed.
  2. Then select any single cell and select View » Macro » Record Macro. It will open the Record Macro dialog box.
  3. Assign the macro a name i.e. RandomSort and click OK button, see the following figure:
  1. Select columns A, B, C, and D
  2. Click Formula » Calculate Now button (or press F9) to force a recalculation.
  1. Select Data » Sort and sort the data by column A.
  1. Stop recording the macro by clicking the View » Macros drop-down.
  1. Next, click Developer » Insert in the Controls group.
  2. Click the Button icon from the Forms Controls.
  3. The Assign Macro dialog box will appear, assign the macro you just recorded to this button and click OK.
  4. Place the button anywhere on the worksheet.

Each time you click the button, your data will be sorted randomly. You can select column A and hide it completely, as there is no need for a user to see the random numbers generated.

Sort and Filter Data

  1. Quickly Sort Data by Single or Multiple Columns
  2. Sort Data with Conditional Formatting Criteria
  3. Sort Weekdays and Months
  4. Sort and Fill Data with Custom Lists
  5. Random Sort using RAND() Function
  6. Filter (AutoFilter) Data
  7. Excel Advanced Filter