Categories

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