Google Sheets: Macro Based Random Name Picker

I am providing you a Macro based Random Name Picker in this post. But you can create one such name picker yourself too by following my tips. So I am concentrating on that aspect also. I mean you can learn here how to generate a Macro based Random name picker in Google Sheets.

Can I have the option to select multiple numbers of Winners?

Of course! You can decide how many numbers of winners to choose like the first winner, second winner, third winner and so on. You just need to make a minor change in the provided formula for that.

I am running giveaways. Is it possible to pick a few names from a large number of participants?

Yes! It’s possible to pick the winners from hundreds of participants.

How to pick winners of the lottery randomly using this Macro based Random Name Picker?

The procedure is the same for picking any type of winners. It can be lottery numbers, name, phone number or anything. You can randomly choose the winner. The Macro is the same.

You just need to enter the name, lottery numbers, or other details of all the registered people for the giveaway, contest or the lottery in the specified format. Once learned, you can use any type of data set for this.

If you follow my below tutorial step by step, you can yourself create your own Google Sheets Macro to pick a lottery winner or randomly pick a name.

If you just want to pick a random name from a single column, no other details, I’ve one more solution using the RANDBETWEEN function in Google Sheets.

Related: How to Pick a Random Name from a Long List in Google Sheets.

How to Create a Macro Based Random Name Picker in Google Sheets

This is the sample data I am using. Please make a copy of this data on another tab also that we may require later.

Sample data for Macro Based Name Picker

Now the time to create Macro.

The Steps Involved In Creating the Name Picker

I am creating a random name picker to pick single or multiple winners from 1000 of entrants.

To do that first go to the Tools menu and click on Macro and hit the record Macro button.

Screenshot of recording Macro in Google Sheets

Make sure that “Use relative references” is selected, if not, select it. Now select the number of rows you want in column A, B, C, D, and E.

The selection should be smart as the Macro is already started saving your task. Select the rows like;

Go to the cell A2 and then press and hold the Shift key.

Then press right arrow key simultaneously until you reach the cell E2. This’s to select the range A2: E2. Once selected the range, you can release the keys.

Now apply Ctrl+Shift+down arrow key twice. It would select all the rows in your sheet  (please make sure it does)

In my sheet, I’ve selected the range A2: E1000. I have only data in the first few rows, i.e. in A2: E7. Still, I’ve selected the whole rows to give room for adding additional data and that to cover in the Macro automatically in the future.

Keep the selection active. Go to the menu Data and click Randomise range. Please note that it will Shuffle your existing data in the selected range.

So if you want your original data as it’s, before recording Macro, make a copy of your sheet and keep it separately.

Hope you have already done it. You can right click on the tab name to create a duplicate sheet.

Now go to the cell G2 and enter the below Query formula. Later you can delete this formula. The Macro can insert the formula itself!

Macro Based Name Picker Formula

=query(A1:D1000,"Select * where B<>'' limit 1",1)

This’s the time to decide how many numbers of winners that you want to pick. You can change the limit 1 in the formula to limit 2 to pick two winners randomly. Hope you have got the idea.

Now click “Save” to save the Macro and then name your Macro. I’ve named the Macro as Winner and the shortcut key number is 1.

So I can either use the shortcut key Ctrl+Alt+Shift+1 or run the macro from the Tools menu by selecting the Macro name Winner.

Now you should go to the Cell G2. As said earlier, delete the just entered formula. Then go to the Tools menu, click the macro “Winner” or use the shortcut Ctrl+Alt+Shift+1.

Pick a lottery, give away winners in Google Sheets

The first time you have to give Google Sheets the necessary permission to run the Macro. You will see the below message.

A script attached to this document needs your permission to run

Click “Continue” and choose your account and click on the “Allow” button. Google Sheets will instantly pick a random name for you.

To test again, delete the generated random name in the cell G2. Again use the shortcut key. That’s all.

As committed, here is the link to my sheet.

https://docs.google.com/spreadsheets/d/1y07gNmqAhbHA5doWz8ew5z_rH0oDUzvWNPo59lYJZMc/edit?usp=sharing

This file is in “View” the only mode. Please go to the File menu and make a copy for edit access.

Further, there are two choices. One is the Macro based one and the other one is based on the Randbetween formula.

Conclusion

As you can see, the above Google Sheets Macro-Based Random Name Picker will shuffle your data first and return the top row content as the winner.

So obviously, the existing data will be altered. So I suggest you make a copy of your sheet first, that before running the Macro.

Once you set everything as above, I mean after creating the Macro and giving necessary permission to run the Macro, replace your original data from the duplicate sheet.

So at the time of the announcement of the winner, you only need to go to the cell G2 and press the keyboard shortcut Ctrl+Alt+Shift+1.

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.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

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...

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.