Can you randomize in excel

The upside of this method is that you keep the original list and randomize that instead of creating a new list from the original as a base. We have the steps as follows:. The ROWS function returns the number of rows in a reference. There is no need to make the list of names into absolute references in the formula. There is also no need to drag the formula since the results of this formula spill to create the randomized list. Select the new randomized column, and copy-paste the values on themselves or in a new column to avoid REF errors when the original column is deleted.

Delete rest of the data which is not needed anymore. All three methods mentioned up until now are good if you do not want to duplicate values in your finished randomized list.

Recommended Reading : How to count non-blank cells from a list. Before you decide to proceed with this method, there will most probably be a repetition of values repetitions of names in this case. The INDEX function returns a value or its cell reference at the intersection of a particular row and column in a given range.

In the first case, the random number generated is 4. This number signifies the column number from which the result is to be picked. Select the new randomized column, and copy-paste the values on themselves or in a new column to stop the column values from recalculating. Delete the rest of the data not needed anymore. But as a caution, I recommend you check for duplicate values when you use this function.

Note: RAND is a volatile formula and would recalculate every time there is any change in the worksheet. Make sure you have converted all the RAND function results to values. You can use Conditional Formatting to highlight duplicates or use the Remove Duplicate option to get rid of it. To avoid this, you can create a Pivot Table referencing the cells with the random values functions and then reference the Pivot Table values.

Best Excel Shortcuts. Conditional Formatting. Creating a Pivot Table. Excel Tables. How to randomize in Excel by using a RAND formula Shuffle cells, rows and columns with Ultimate Suite How to randomize a list in Excel with a formula Although there is no native function to perform random sort in Excel, there is a function to generate random numbers Excel RAND function and we are going to use it.

Assuming you have a list of names in column A, please follow these steps to randomize your list: Insert a new column next to the list of names you want to randomize. If your dataset consists of a single column, skip this step. The fastest way to do this is by double-clicking the fill handle: Sort the column filled with random numbers in ascending order descending sort would move the column headers at the bottom of the table, you definitely don't want this.

So, if you are not happy with how your list has been randomized, keep hitting the sort button until you get the desired result. To prevent the random numbers from recalculating with every change you make to the worksheet, copy the random numbers, and then paste them as values by using the Paste Special feature.