Buzz
Today, Mytour will guide you through the process of creating a random selection range from a given dataset in Microsoft Excel. Random selection is incredibly useful for generating unbiased, fair random lists from your data collection.
Steps
Gather Data in Excel
Launch Microsoft Excel. You can also open an existing Excel document if the data meets the requirements for your random list needs.
Select Blank workbook (Blank spreadsheet). Skip this step if you don't need to open a new file.
Enter your data. Click on the cell where you want to input data, then type in the information.
- This step may vary depending on the type of data you have. However, it’s recommended to start from column 'A'.
- For example: you might list usernames in column 'A' and survey answers (e.g., 'Yes' or 'No') in column 'B'.
Ensure all relevant data is entered into the spreadsheet. Once you've confirmed that all necessary information has been included, you can proceed to create a random list.
Create a random list
Right-click on the column header. For instance, if all your data starts from column 'A', you will need to right-click on the 'A' label at the top of the column.
Click on Insert (Insert) to add an additional column to the left of the one you're working on.
- All the data from column "A" will then be listed in column "B", and the process will continue for subsequent columns.
Select the new "A1" cell.
Enter "= RAND()" into this cell (without quotation marks). The "RAND" function will generate a random decimal number between 0 and 1 in the chosen cell.
- If Excel automatically formats the "RAND" function, remove the formatting and re-enter the function.
Press ↵ Enter. A decimal number (e.g., 0.5647) will appear in the selected cell.
Re-select the cell you just worked on.
Hold down the Control key and C. The "RAND" function will be copied.
- On Mac computers, you will need to press and hold the ⌘ Command key instead of the Control.
- You can also right-click on the "RAND" cell and select Copy (Copy).
Select the cell below the one containing the random number. This could be cell "A2".
- If you click and highlight starting from cell "A1", an error in sorting will occur.
Highlight the remaining data cells. Hold down the ⇧ Shift key and click on the last cell in your data range.
- For example, if your data spans columns "B" and "C" down to row 100, hold the ⇧ Shift key and click on cell "A100" to select the range from A2 to A100 in column "A".
Hold down the Control key and V. This will paste the random values into all selected cells (e.g., from A2 to A100). Afterward, you will need to sort the data to finalize the results.
- As mentioned, Mac users should hold down the ⌘ Command key instead of Control.
Select a random list
Select the top-left cell. This is usually cell "A1". Before you can select the random list, you need to highlight all the data.
- The selection range includes the random list, which is located to the left of your data.
Hold down the ⇧ key and select the bottom-right cell. All your data will be highlighted and ready for sorting.
- For instance, if your data spans two columns with 50 cells in each column, you need to select cell "C50" while holding the ⇧ Shift key.
- You can also click and drag from the top-left corner down to the bottom-left corner of your data (or vice versa) to highlight it.
Right-click on the data. A context menu with options to help you sort the data will appear.
- On Mac computers, you can click with two fingers (or hold the Ctrl key and click the mouse) to open the context menu.
Hover your cursor over the Sort option.
Click on Sort Smallest to Largest. You can also click on Sort Largest to Smallest, but most importantly, the values in column "A" will be randomly sorted based on the "= RAND()" function.
View the results after sorting. The process will vary depending on how many results you need. However, you have a few options for the sorted data:
- Select the top, bottom, or middle section of the data. If the data set is too large to manage, you may want to reduce the ratio (e.g., the first 1/8 of the data).
- Select all odd or even numbers. For example, in a set of 10 data points, you can choose numbers 1, 3, 5, 7, 9, or 2, 4, 6, 8, 10.
- Select a random subset of data points. This method is useful for large data sets, where choosing half of the information becomes too challenging.
Select the random list component. Now you have a non-biased selection list for surveys, product giveaways, and more.
Tip
- If you don't have Microsoft Excel, several other free online programs (such as Google Sheets or Outlook's Excel) can help you create random lists.
- Microsoft offers an Excel app for iPhone and Android, allowing you to create spreadsheets anytime, anywhere.
Warning
- Failing to randomly select a list after conducting a search (for example, sending out a survey after updating a service) can result in biased and inaccurate responses.
