How to Generate Random List in Excel

This wikiHow teaches you how to create a random selection from given data in Microsoft Excel. Random selection is useful in generating fair, unbiased random lists in a data collection.

Table of Contents

Collect data on Excel

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/ba/Create-a-Random-Sample-in-Excel-Step-1-Version-4.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-1-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/b/ba/Create-a- Random-Sample-in-Excel-Step-1-Version-4.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-1-Version-4.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Open the Microsoft Excel program. You can also open an existing Microsoft Excel document if this fits your random list needs.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/1/11/Create-a-Random-Sample-in-Excel-Step-2-Version-4.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-2-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/1/11/Create-a- Random-Sample-in-Excel-Step-2-Version-4.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-2-Version-4.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Select Blank workbook . Skip this step if you are not opening a new document.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/4d/Create-a-Random-Sample-in-Excel-Step-3-Version-4.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-3-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/4/4d/Create-a- Random-Sample-in-Excel-Step-3-Version-4.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-3-Version-4.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Insert information. Click the cell where you want to fill in the data, then enter the data.

The process will vary depending on the type of data you have. However, you should start from column “A”.
For example, you can put usernames in column “A” and survey answers (for example, “yes” or “no”) in column “B”.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/f/fe/Create-a-Random-Sample-in-Excel-Step-4-Version-4.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-4-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/f/fe/Create-a- Random-Sample-in-Excel-Step-4-Version-4.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-4-Version-4.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

You need to make sure you enter all relevant data into the spreadsheet. Once you’ve made sure you’ve added all the necessary data, you can generate a random list.

Generate random list

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/2/2b/Create-a-Random-Sample-in-Excel-Step-5-Version-4.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-5-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/2/2b/Create-a- Random-Sample-in-Excel-Step-5-Version-4.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-5-Version-4.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Right click on the left column name. For example, if all the data starts from column “A”, you’ll need to right-click the “A” at the top of the page.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/c/c1/Create-a-Random-Sample-in-Excel-Step-6-Version-4.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-6-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/c/c1/Create-a- Random-Sample-in-Excel-Step-6-Version-4.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-6-Version-4.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Insert to add another column to the left of the column you are working with.

Then, all the data in column “A” will be re-listed as column “B” and so on.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/b5/Create-a-Random-Sample-in-Excel-Step-7-Version-3.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-7-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/b/b5/Create-a- Random-Sample-in-Excel-Step-7-Version-3.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-7-Version-3.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Select the new “A1” cell.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/5/5d/Create-a-Random-Sample-in-Excel-Step-8-Version-4.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-8-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/5/5d/Create-a- Random-Sample-in-Excel-Step-8-Version-4.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-8-Version-4.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Type “=RAND()” in this box (excluding quotes). The “RAND” function will return a real number between 0 and 1 in the cell you selected. ^{[1] X Research Source}

If Excel automatically formats your “RAND” function, remove the formatting and re-enter the function.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/4c/Create-a-Random-Sample-in-Excel-Step-9-Version-2.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-9-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/4/4c/Create-a- Random-Sample-in-Excel-Step-9-Version-2.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-9-Version-2.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Press ↵ Enter . You will see a decimal number (for example, 0.5647) appear in the selected cell.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/e/e6/Create-a-Random-Sample-in-Excel-Step-10-Version-2.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-10-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/e/e6/Create-a- Random-Sample-in-Excel-Step-10-Version-2.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-10-Version-2.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Select the last cell again.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/c/c3/Create-a-Random-Sample-in-Excel-Step-11-Version-2.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-11-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/c/c3/Create-a- Random-Sample-in-Excel-Step-11-Version-2.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-11-Version-2.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Hold down the Ctrl and C keys. The function “RAND” will be copied.

On a Mac, you need to hold down the ⌘ Command key instead of Contrp .
You can also right-click the “RAND” box and select Copy .

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/83/Create-a-Random-Sample-in-Excel-Step-12-Version-2.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-12-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/8/83/Create-a- Random-Sample-in-Excel-Step-12-Version-2.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-12-Version-2.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Select the cell below the cell containing the random number. This could be cell “A2”.

Clicking and highlighting from cell “A1” will cause a sorting error.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/f/f5/Create-a-Random-Sample-in-Excel-Step-13-Version-2.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-13-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/f/f5/Create-a- Random-Sample-in-Excel-Step-13-Version-2.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-13-Version-2.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Highlight the rest of the data cells. Hold down the ⇧ Shift key and then click the last cell in your data range.

For example, if the data is in column “B” and “C” extends down to the 100th cell, you’d need to hold down the ⇧ Shift key and then click on cell “A100” to select from A2 to A100 of column “A”.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/8f/Create-a-Random-Sample-in-Excel-Step-14-Version-2.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-14-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/8/8f/Create-a- Random-Sample-in-Excel-Step-14-Version-2.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-14-Version-2.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Hold down the Ctrl and V keys. The function will paste a random value into all selected cells (for example, from A2 to A100). Once done, you need to sort the data of random numbers to choose the result.

As mentioned, Mac users need to hold down the ⌘ Command key instead of Contrp .

Pick a random list

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/f/f9/Create-a-Random-Sample-in-Excel-Step-15-Version-2.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-15-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/f/f9/Create-a- Random-Sample-in-Excel-Step-15-Version-2.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-15-Version-2.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Select the top left cell. This is cell “A1” in most cases. Before you can select a random list, you need to highlight all of the data.

The selection includes the random list to the left of your data.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/3/38/Create-a-Random-Sample-in-Excel-Step-16-Version-2.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-16-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/3/38/Create-a- Random-Sample-in-Excel-Step-16-Version-2.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-16-Version-2.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Hold down the ⇧ Shift key and select the bottom right cell. All data will be highlighted and ready for sorting.

For example, if your data occupies two columns with 50 cells each, you need to select cell “C50” while holding down the ⇧ Shift key.
You can also click and drag your mouse from the top left corner to the bottom left corner of the data (or vice versa) to highlight.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/62/Create-a-Random-Sample-in-Excel-Step-17-Version-2.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-17-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/6/62/Create-a- Random-Sample-in-Excel-Step-17-Version-2.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-17-Version-2.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Right click on the data. A context menu with options to help you sort the data will appear.

On a Mac, you can click with two fingers (or hold down Ctrl and click) to open a context menu.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/3/3e/Create-a-Random-Sample-in-Excel-Step-18-Version-2.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-18-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/3/3e/Create-a- Random-Sample-in-Excel-Step-18-Version-2.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-18-Version-2.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Hover the mouse pointer to the Sort option.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/d/d3/Create-a-Random-Sample-in-Excel-Step-19-Version-2.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-19-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/d/d3/Create-a- Random-Sample-in-Excel-Step-19-Version-2.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-19-Version-2.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Sort Smallest to Largest . You can also click Sort Largest to Smallest , most importantly the values in column “A” are sorted randomly based on the function “=RAND()”.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/2/2f/Create-a-Random-Sample-in-Excel-Step-20-Version-2.jpg/v4-728px- Create-a-Random-Sample-in-Excel-Step-20-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/2/2f/Create-a- Random-Sample-in-Excel-Step-20-Version-2.jpg/v4-728px-Create-a-Random-Sample-in-Excel-Step-20-Version-2.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

See the results after sorting. The process will vary depending on how many results you need. However, you have the following options with sorted data:

Select top, bottom, or middle data . If the number of data points is too large to warrant this, you can lower the scale (e.g. first 1/8 of the data).
Select all odd or even number data . For example, in a set of 10 data points, you can choose the numbers 1, 3, 5, 7, 9 or 2, 4, 6, 8, 10.
Pick a random number of data points . This method is suitable for large data sets, where choosing half the information becomes too difficult.

Advice

If you don’t have Microsoft Excel, some other free online program (such as the Google Sheets app or Outlook’s Excel) can help you generate random lists.
Microsoft brings Excel apps for iPhone and Android platforms so you can create spreadsheets wherever you are.

Warning

Failure to randomly select a list after searching for results (such as sending out a survey after a service update) may cause your answers to appear biased and incorrect.

Steps

Collect data on Excel

Generate random list

Pick a random list

Advice

Warning

Related Posts