How to randomize a list in Excel: sort randomly cells, rows and columns

This tutorial demonstrates two efficient methods for randomizing data within Microsoft Excel: utilizing formulas for random sorting and employing a specialized tool for data shuffling.

Excel offers various sorting options, but lacks a built-in random sort function. This tutorial addresses this gap by presenting two straightforward solutions.

Method 1: Randomizing with the RAND Formula

This method leverages Excel's RAND function to generate random numbers for sorting.

1. Add a Helper Column: Insert a new column adjacent to the column containing the data you wish to randomize.

2. Insert the RAND Formula: In the first cell of the new column, enter =RAND().

3. Copy the Formula: Double-click the fill handle (the small square at the bottom right of the cell) to quickly copy the formula down the entire column. This creates a column of random numbers corresponding to each data entry.

4. Sort Ascending: Sort the column containing the random numbers in ascending order. This will automatically rearrange the original data column accordingly. You can achieve this by selecting a cell in the random number column, then navigating to the Home tab > Sort & Filter > Sort Smallest to Largest. Alternatively, use the Data tab > Sort & Filter > A to Z button.

Important Considerations:

• The RAND function is volatile; it recalculates with every worksheet change. To fix the randomized order, copy the random numbers and paste them as values using Paste Special. Alternatively, delete the helper column once the sorting is complete.
• This method works equally well for multiple columns; simply ensure the columns are contiguous.

Method 2: Using Ultimate Suite's Random Generator

For a quicker solution, utilize the Random Generator tool within Ultimate Suite.

1. Access the Tool: Navigate to the Ablebits Tools tab > Utilities group, and click the Randomize button, followed by selecting Shuffle Cells.

2. Select Options: In the Shuffle pane, choose the desired shuffling method (cells within rows, columns, entire rows, entire columns, or all cells).

3. Shuffle: Click the Shuffle button to instantly randomize your selected data.

This tool offers a significantly faster alternative to the formula-based approach. A trial version is available for download.

The above is the detailed content of How to randomize a list in Excel: sort randomly cells, rows and columns. For more information, please follow other related articles on the PHP Chinese website!