How to select a random sample from all your data [trick]

Posted on January 31st, 2014 in Excel Howtos - 9 comments

The other day, I got a text message (SMS) from one of our readers. It read,

How to select random samples from data in Excel?

So today, let us learn a very easy trick to select random sample from your data.

Lets take a look at the data

Since the text message has no actual data, I made up this.

Random samples needed from this data set

Now, if you just want to select any 10 (or x number of) random items from this list, then your job would very simple.

  1. Shuffle (or randomly arrange) this list
  2. Just pick first 10 items

But our problem is to get 2 random samples per user.

Selecting random samples from data

Follow below steps.

  1. Add an extra column and fill it with =RAND() formula. This generates random fraction between 0 and 1.

    Add RAND() function to the adjacent column

  2. Create a pivot table from this data (tutorial: How to create a pivot table?)
  3. Add User ID & Case ID as Row labels and Random as value field.
    Pivot table layout for selecting random samples
  4. Click on the filter icon on Case ID column, choose Value filter > Top 10
  5. Filter for top 2 random values. (related: Filter top 10 values in pivot tables – how to?)

    Pivot Table value filters - filtering top 2 values

  6. Adjust report layout (Table layout, no sub-totals, no grand totals)

    Report layout to show just the samples and nothing else

  7. Done!

Final random samples - easy and awesome.

To see new samples

Just select any cell in the pivot table, press ALT+F5. Your pivot table will be refreshed and you get new samples.

That is just easy and awesome!

Download Example Workbook

Click here to download the example file. Refresh the pivot table (ALT+F5) to see fresh samples.

Do you sample your data?

Drawing samples, running experiments, analyzing results are life breath for many businesses. As business data is growing, these analytical skills  are becoming important.

How do you draw samples? What techniques you use when analyzing the data? Please share your stories, experiences & tips using comments.

A sample of our awesome collection of Excel tips

Here is a tiny sample of our awesome Excel tips. Don’t hold back, take them all, and more.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

9 Responses to “How to select a random sample from all your data [trick]”

  1. Juan says:

    Great tip and very easy to apply, thank you very much! How do you make the text of the cell screen blur (besides – 99?

  2. zx8754 says:

    After adding “RAND” column, we could add “rank per group” column. Then by filtering for 1 and 2, we will get random 2 rows per user.

    =SUMPRODUCT(($A$2:$A$20=A2)*($C$2:$C$20>C2))+1

  3. Manish says:

    Is it possible for anybody to create this in VBA with a button or with the help of macro ? It shpuld give randomly 2 cases per user with one click. Please help me if you can.

  4. Robert Clark says:

    First time I have tried to download a sample spreadsheet for some time – I am missing something? It is downloading as a zip file even though the shortcut shows it as http://img.chandoo.org/pivot/random-samples-thru-pivot-tables.xlsx
    How do I turn the zip file back into something useful?

    • Hui... says:

      @Robert
      Save the file instead of Opening the file
      Then in Explorer rename it to random-samples-thru-pivot-tables.xlsx
      Then Open from Explorer or Excel

  5. […] How to select a random sample from all your data […]

  6. mehdi says:

    thanks for the trick

  7. Blaine Sharpe says:

    Hey Chandoo!
    We emailed last week. I’ve been trying to download your dashboards, no luck! I just set up paypal, but doesn’t seem to be working yet, although I’ve done all they’ve asked.
    Is there anyway we can do this billing directly??
    I would like to get this dashboards going asap and have spent two days waiting for paypal !
    Regards,
    Blaine
    PS- Nice looking family you have!

  8. Robert Clark says:

    Is there a way of selecting the number of cases to be extracted based on the value in a cell, so that the user can decide how few or many random elements they want selected?

Leave a Reply