How to Create a Random Sample in Excel

PDF download Download Article
Plus, sort samples easily in Microsoft Excel
PDF download Download Article

This wikiHow teaches you how to generate a random selection from pre-existing data in Microsoft Excel. Random selections are useful for creating fair, non-biased samples of your data collection.

Part 1
Part 1 of 3:

Assembling the Data

PDF download Download Article
  1. You can also open an existing Microsoft Excel document if you have one that correlates to your random sample needs.
  2. If you aren't opening a new document, skip this step.
    Advertisement
  3. To do this, click on a cell into which you wish to input data, then type in your data.
    • Depending on the type of data you have, this process will vary. However, you should start all data in the "A" column.
    • For example: you might place your users' names in the "A" column and their responses to a survey (e.g., "yes" or "no") in the "B" column.
  4. Once you're positive that you've added all necessary data, you're ready to generate your random sample.
    5. Advertisement
Part 2
Part 2 of 3:

Creating a Random Sample

PDF download Download Article
  1. For example, if all of your data begins in column "A", you'd right-click the "A" at the top of the page.
  2. This will add a column to the left of your current left column.
    • After doing this, any data that was in the "A" column will be relisted as being in the "B" column and so on.
  4. Exclude the quotation marks. The "RAND" command applies a number between 0 and 1 to your selected cell.
    • If Excel attempts to automatically format your "RAND" command, delete the formatting and re-type the command.
  5. You should see a decimal (e.g., 0.5647) appear in your selected cell.
  7. Doing this will copy the "RAND" command.
    • For a Mac, you'll hold down ⌘ Command instead of Control .
    • You can also right-click the "RAND" cell and then select Copy .
  8. This will likely be the "A2" cell.
    • Clicking the "A1" cell and highlighting from there can cause a sorting error.
  9. To do this, you'll hold down ⇧ Shift while clicking the cell at the bottom of your data range.
    • For example, if your data in columns "B" and "C" extends all the way down to cell 100, you would hold down ⇧ Shift and click "A100" to select all "A" cells from A2 to A100.
  10. Doing so will paste the random sample command into all selected cells (e.g., A2 through A100). Once this is done, you'll need to sort your data using the random numbers to reorder your results.
    • Again, Mac users will need to hold down ⌘ Command instead of Control .
    11. Advertisement
Part 3
Part 3 of 3:

Sorting the Sample

PDF download Download Article
  1. In most cases, this will be the "A1" cell. Before you can sort your sample, you'll need to highlight all of your data.
    • This includes the random sample numbers to the left of your data as well.
  2. Doing so will highlight all of your data, making it ready for sorting.
    • For example, if your data takes up two columns of 50 cells each, you would select "C50" while holding down ⇧ Shift .
    • You can also click and drag your cursor from the top left corner to the bottom right corner of your data (or vice versa) to highlight it.
  3. This will bring up a context menu with options that will allow you to sort your data.
    • If you're using a Mac, you can click using two fingers (or hold down Ctrl and click) to bring up the context menu.
  5. You can also click Sort Largest to Smallest here--the only thing that matters is that your data is reorganized randomly according to the "= RAND()" values in the "A" column.
  6. Depending on how many results you need, your process from here will vary. However, you can do a couple of things with the sorted data:
    • Select the first, last, or middle half of the data . If your number of data points is too large to warrant this, you can also settle on a lower fraction (for example, the first eighth of the data).
    • Select all odd- or even-numbered data . For example, in a set of 10 data points, you would either pick numbers 1, 3, 5, 7, and 9, or 2, 4, 6, 8, and 10.
    • Select a number of random data points . This method works best for large sets of data where picking half of the information is too ambitious.
  7. Now you have a non-biased sample pool for a survey, product giveaway, or something similar.
    8. Advertisement

Community Q&A

Search
Add New Question
  • Question
    When I copy the random values from column A and paste them in column B following the paste special -> values instruction, I get a different set of numbers from what I have in column A. Is this correct?
    Community Answer
    The values in column B should match what was in column A before, but as you paste them, the values in column A will change. That's expected since the RAND() formula recalculates a new random number each time you do anything else in the spreadsheet. Copying the values only (not the formula) over to column B will prevent them from changing in future.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 3 Helpful 6
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Tips

      • If you don't have Microsoft Excel , there are other free programs online (such as Google Sheets or Outlook's Excel app) that may allow you to create a random sample.
        Thanks
        Helpful 0 Not Helpful 0
      • Microsoft makes an Excel app for iPhone and Android platforms so you can create spreadsheets on-the-go.
        Thanks
        Helpful 0 Not Helpful 0
      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!
      Advertisement

      Warnings

      • Failing to use a random sample when looking for results (e.g., sending out a survey after updating a service) may cause your answers to be biased--and, therefore, inaccurate.
        Thanks
        Helpful 0 Not Helpful 0
      Advertisement

      You Might Also Like

      How to
      Make a Spreadsheet in Excel
      How to
      Create Pivot Tables in Microsoft Excel to Analyze Data
      How to
      Apply Conditional Formatting in Excel
      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Create a Graph in Excel
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      3 Easy Ways to Freeze Panes to Lock Columns or Rows in Excel
      How to
      Change from Lowercase to Uppercase in Microsoft Excel
      Advertisement

      About This Article

      In other languages
      Thanks to all authors for creating a page that has been read 459,422 times.

      Is this article up to date?

      Advertisement