For this tutorial, I made up an imaginary data set. Let's assume I'm examining individual cells in live cell microscopy and timing how long they stay in prophase (one stage of the cell cycle). In my preliminary data, I counted all 40 cells imaged in this group, but I only want the data from 20 randomly selected cells. Below is the table of all 40 cells and their time in prophase.
Next, make a new column to the right of the data points titled "RANDOM". In the first cell of the column, add the formula "=RAND()". Excel will produce a random number between 0 and 1 in this cell.
Importantly, you must convert all of the random formulas to permanent numbers that won't keep changing every time you refresh the page or perform another function. So, copy all cells in the Random column, and right click on those same cells to choose Paste Special --> Values. This will replace the formula with a permanent number of the same value. This is really really important to do or the next steps won't work.
Next, go to the Data menu in the top bar and select the Sort button (see button in upper right corner in image below).
A window will appear. Choose to sort the RANDOM column by values, smallest to largest.
Now, your list will be sorted by smallest to largest random value, meaning the data points are now in a completely random order. The entire row is maintained when you sort in this way, meaning that the same ID number, data point, and random number are sorted together as a group. Now you can select the first n number of cells that you need for random selection. Since I wanted 20 random data points in this scenario, I highlighted the first 20 entries that appear in the list.
And voila! You have a randomly selected sample from your list! Have any of you used Excel for randomizing data before? Do you prefer a different program? Any other tips or tricks for people learning to randomize data? Comment below!