How to take a random sample in Excel

person pointing to a spreadsheet

When you need to analyze a large quantity of data, taking a random sample helps you to make accurate, unbiased conclusions. Taking a random sample with Microsoft Excel can assist you with market or product research and serve a wide variety of business purposes. To help you get started, we'll share how easy it is to use Excel to take a random sample using realistic examples. 


Take the next step:  Microsoft Excel Fundamentals + Data Analytics Certificate program


Using a random sample to pull a fraction of data 

Suppose I need to interview 50 random NBA players about their new collective bargaining agreement. First, I created a spreadsheet and listed 478 NBA players in Column A. I want to shuffle the listed players so that any combination of 50 players has the same chance of being at the top of the list. To take a random sample, we would then choose the first 50 players listed after the shuffling.

There are two ways to create a random sample depending on whether or not you have Office 365. 

How to create a random sample without Office 365

The rand() function is the key tool needed to take a random sample. If you enter the function =rand() in a cell, Excel is equally likely to return any number between 0 and 1. For example, there is a 10% chance you will see a number between 0 and 0.1 and a 20% chance you will see a number between 0.8 and 1. The value returned by rand() functions are independent, which means that the value returned by rand() in any cell has no effect on any other rand() values in the workbook.


Description automatically generated

Figure 1: Random Sample without Office 365


To generate our random sample, proceed as follows (see Figure 1):

  1. Generate a random number for each player by copying from B3 to B4:B480 the formula = rand().
  2. “Freeze” the random numbers by copying the range B3:B480 and then select Paste Special Values.
  3. Now, sort the range A3:B480 on Column B (we chose sort smallest to largest). This step effectively shuffles the players so each player has the same chance to be in any position on the list.
  4. The first 50 players listed are your random sample of 50 players; each subset of 50 players has the same chance of being chosen.


How to create a random sample with Office 365

Office 365 makes it much easier to create a random sample in Excel. Cell J3 contains a “magic” formula that will shuffle our list of NBA players (see Figure 2).

Graphical user interface, application, table, Excel

Description automatically generated

Figure 2: Picking a Random Sample with Office 365

The formula =SORTBY(G3:G480,RANDARRAY(COUNTA(G3:G480))) in cell J3 is a dynamic array formula, which can create results in multiple cells and automatically updates if the list of players in G3:G480 updates. This formula generates 477 random numbers via the =RAND() function. Then, these random numbers will be used to sort the player list.

The SORTBY portion of the formula sorts the list based on the random numbers. The default is ascending order, but descending order would work fine. The first 50 players listed would be your random sample. If you press the F9 key, your spreadsheet will recalculate the random numbers and you will have a new random sample.

That's all there is to it! 

Sharpen your Excel skills with CPE courses

This is a simple Microsoft Excel trick that can save you time and make it easier to analyze data. To take your Excel skills to the next level, Becker offers several CPE courses on the topic. 


Microsoft Excel Fundamentals + Data Analytics Certificate

Become an Excel expert in with Becker's comprehensive program that covers everything from essential fundamentals to in-depth applications for data analytics. This certification is a great opportunity to get end-to-end training in data visualization, advanced analytics, and Excel insights. 

Take the next step:  Microsoft Excel Fundamentals + Data Analytics Certificate program

Now Leaving Becker.com

You are leaving the Becker.com website. Once you click “continue,” you will be brought to a third-party website. Please be aware, the privacy policy may differ on the third-party website. Adtalem Global Education is not responsible for the security, contents and accuracy of any information provided on the third-party website. Note that the website may still be a third-party website even the format is similar to the Becker.com website.