Google Sheets: Macro-Based Random Name Picker

This guide will show you how to create a macro-based random name picker in Google Sheets, customized to fit your needs.

Before we jump in, here are some common questions you might have and their answers. These will help you determine whether this method suits your requirements.

Can I select multiple winners?

Absolutely! You can easily decide how many winners to pick—such as a first winner, second winner, and so on. With a small adjustment to the provided formula, you can select multiple winners effortlessly.

Can it handle a large number of participants?

Yes! This method works seamlessly, even with hundreds or thousands of participants. You can select winners randomly from a large dataset without any issues.

Can I use it for lotteries or other types of giveaways?

Yes, this method is highly versatile. Whether you’re selecting names, lottery numbers, or any other type of data, the same macro-based approach applies. Just ensure your data is prepared in the correct format, as explained in the guide.

Preparing the Data

Before recording the macro, organize your data in a structured format. For example:

  • The first column should always contain unique IDs, manually entered (do not use formulas like SEQUENCE or ROW).
  • If you only want to pick names, ensure column A contains IDs and column B contains names.
  • In this example, we’ll randomly pick a winner along with their associated data. We’ll use the range A2:F for selecting winners (with headers in A1:F1, which will be excluded).
ABCDEF
IDEmailFirst NameLast NamePhoneTime Stamp
1889@example.comLynnMaxwell1594872632018-04-18 10:15:07
2123@example.comDanielWheeler1234567892018-04-18 08:15:46
3234@example.comRodneyPowell9876543212018-04-19 08:48:12
4567@example.comAllanWashington4567891232018-04-19 09:15:03
5999@example.comDaisyEvans3692581472018-04-18 12:15:07
6111@example.comCarlaMoore7418529632018-04-18 12:18:07

Applying the Formula

To display random winners, enter the following QUERY formula in H2:

=QUERY(A2:F, "SELECT * WHERE A IS NOT NULL LIMIT n")
  • Replace n with the number of winners you want to pick.
  • Update A2:F to match the range of your data.

When you run the macro, it will randomize the range. The formula will then select the top n rows from the newly randomized range. This is the logic behind the macro-based random name picker.

Note: Ignore the current results in cell H2 until after running the macro, as they won’t reflect the randomized output yet.

Recording the Macro for Random Name Selection

Follow these steps to record the macro:

  1. Navigate to the first cell of your data (e.g., A2, below the header row).
  2. Click Extensions > Macros > Record Macro.
  3. Select the entire data range (e.g., A2:F). Use these shortcuts for quick selection:
    • Windows: Press Ctrl + Shift + Right (repeatedly if there are empty cells) and then Ctrl + Shift + Down (until the entire range is selected).
    • Mac: Use Command + Shift + Right and Command + Shift + Down.
  4. Click Data > Randomize Range.
  5. Click Save to stop recording the macro.
  6. Name the macro (e.g., “Winner”). Optionally, assign a shortcut like Ctrl + Alt + Shift + [Number] (or Command + Option + Shift + [Number] on Mac).
  7. Click Save.

Running the Macro

Running the macro to pick random names is straightforward:

Go to Extensions > Macros > Winner. If this is your first macro in the sheet, you’ll be prompted to authorize it. Follow the on-screen instructions to grant the necessary permissions.

The formula will then return the randomly selected winners.

Macro-Based Random Name Picker in Google Sheets

Reverting the Randomization

If you wish to remove the randomization and restore the original order of the table:

  • Sort the table by the first column (Serial Number/ID) in ascending order.

This will reset the data to its original order since we added serial numbers in the first column for this purpose.

Note: Once you reset the order, the randomization will be lost, and you’ll need to run the macro again to pick new random winners.

Additional Tip: Toggle Winners Display

To hide or display the winners dynamically, use a checkbox:

  1. Navigate to H1 and insert a checkbox:
    • Click Insert > Tick Box.
  2. Replace the formula in H2 with the following:
=IF(H1, QUERY(A2:F, "SELECT * WHERE A IS NOT NULL LIMIT n"), )

When the checkbox is ticked, the winners will appear. When unticked, the cell will be blank.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.