See all Microsoft Excel tips
Strategy: Use the RAND function in column B and then sort by column B. Follow these steps.
1) Enter a heading of Rand in B1.
2) Select cells B2:B22. Enter =RAND() and press Ctrl+Enter. Each student will be assigned a random decimal between 0 and 1, as shown in Fig. 298.
3) Select a single cell in column B and choose the AZ button on the Standard toolbar. The list will be sorted in a random sequence.
Gotcha: The data is sorted and then column B is recalculated. It will appear that the new figures in column B are not in ascending order, as shown in Fig.
299. This is because the sort was based on the previous values in column B.
4) You can now delete column B.
Additional Information: If you want to fill column B with sequential numbers, then enter 1 in B2 and 2 in B3. Highlight these two cells and double-click the Fill handle to extend the series to your entire dataset.
Summary: The RAND function can be used to provide a column of data to fairly and randomly sort a list of students.
Commands Discussed: Edit – Paste Special – Values
Functions Discussed: =RAND()
See all Microsoft Excel tips