How to pick a random cell in Excel

Excel provides various advantageous features for its users. These features and shortcuts are very beneficial while dealing with large data files. One can manage his files and documents smartly by using these tools.

In this tutorial, we’ll discuss the two most convenient methods to select a random cell in Excel. 

METHOD 1 – By using the VLOOKUP Function

In this method, we’ll pick up a random cell from a data set using the Excel VLOOKUP function.

Given below is a data set containing a list of customers of a store and their details:

We will consider the following steps to pick up a random customer from the list:

STEP 1 – Create a column to use the formula

• Move your cursor to the top cell of an empty column beside the data set.
• Write the heading Random Customer in the cell.
• A new column will be created to perform operations to pick up a random cell.

STEP 2 – Use the VLOOKUP function

• Select the cell beneath the heading of the new column.
• In this case, the required formula to pick up a random cell from the customer list is:

=VLOOKUP(RANDBETWEEN(1,15),A1:D16,2,FALSE)

where RANDBETWEEN(1,15) generates a random whole number between one and fifteen. This number serves as the search value for the VLOOKUP function. 2 represents the index column number. FALSE is an argument of the VLOOKUP function, indicating an exact match.

• Write the formula in the cell and press Enter key.
• A random customer name will be generated in the cell.
• To select the next random customer you can press the F9 key. This will update the calculations every time and you will get a new random name.

METHOD 2 – Using the INDEX, COUNTA, and RANDBETWEEN functions

In this method, we’ll use the INDEX, COUNTA, and RANDBETWEEN functions to select a random cell in Excel.

We will consider the same data set as in the above case:

The following steps should be considered to pick up a random cell in Excel:

STEP 1 – Create a column to apply the formula

• Move your cursor to the top cell of an empty column beside the data set.
• Write the heading Randon Customer in the cell.
• A new column will be created to perform operations to pick up a random cell.

STEP 2 – Apply the formula

• Select the cell beneath the heading of the new column.
• In this case, the desired formula to pick up a random cell from the customer list is:

                        =INDEX(B2:B16, RANDBETWEEN(1, COUNTA(B2:B16)))

           , where RANDBETWEEN(1, COUNTA(B2:B16)) generates an arbitrary number between   

             one and the count of non-empty cells in the range B2:B16.

• Write the formula in the cell and press Enter key.
• A name from the customer list will appear in the cell.