How to make lucky draw names in Excel?
In workplace activities, team meetings, or special events, it is often necessary to select a few lucky participants or winners at random from a large list of names—for example, for a team raffle, random awards, or choosing volunteers. Manually drawing names from a hat can be inefficient or impractical when dealing with digital lists, especially as the number of names increases. Fortunately, Excel provides a variety of practical methods for making a random selection from your list, allowing you to create transparent, repeatable, and customizable lucky draw experiences directly within your spreadsheets. This article will walk you through several effective ways to randomly pick names in Excel, highlighting their scenarios, advantages and considerations, as well as sharing helpful tips to avoid common mistakes along the way.
Extract random names for making lucky draw with formula
Select random names for making lucky draw with Kutools for Excel
Extract random names for making lucky draw with VBA code
Alternative: Extract random names using the RAND function and sorting
Extract random names for making lucky draw with formula
If you need to randomly select a specific number of names (for example, 3 winners) from a column of names, you can use a complex formula approach. This approach automatically avoids duplicate selections and updates the result each time the workbook recalculates. It is especially suitable for drawing a small, fixed number of names from a medium-sized list, particularly when you want the process to be traceable and not require additional add-ins or code.
To use this method, follow the steps below:
Enter the following formula into a blank cell where you want the first lucky draw result (for example, C2):
=IF(ROWS(C$2:C2)>B$2,"",INDEX(A$2:A$16,AGGREGATE(15,6,((ROW(A$2:A$16)-ROW(A$2)+1)/ISNA(MATCH(A$2:A$16,C$1:C1,0))),RANDBETWEEN(1,ROWS(A$2:A$16)-COUNTA(C$1:C1)+1))))After entering the formula, drag the fill handle down for as many rows as the number of names you want to draw (for example, if you want to draw3 names, drag it down 3 rows to C4). The drawn names will automatically appear in the cells. See screenshot:
Parameter explanations and practical tips:
- In this formula:
- A2:A16 — this is your source name list. Change this range to match your actual name data.
- B2 — this cell should hold the total number of names you want to pick randomly (for example, enter 3).
- C2 — this is the first cell in your results list where you enter the formula.
- C1 — this is the cell directly above the formula. It is required for the formula structure to work correctly, even if left blank.
- This method is dynamic: if you need a new set of random names, simply press F9 to recalculate and get a fresh result set.
- To prevent formulas from changing each time the worksheet recalculates, you may wish to copy the results and use Paste Special > Values to make the drawn names static.
- If your names list is larger or if you want to run the draw multiple times, be sure not to overlap your result column with your name list, as this can cause errors.
Caution: Double-check that cell references are correct and ranges match your actual data. Changing worksheet structure or deleting referenced cells may cause the formula to break.
Select random names for making lucky draw with Kutools for Excel
If you prefer a simple and interactive method without writing formulas, Kutools for Excel provides a straightforward way to randomly select names directly through its Sort Range Randomly feature. This solution is particularly useful for non-technical users or when you want to work visually and quickly, especially with large datasets or when you need to repeat draws frequently.
After Kutools for Excel is installed, follow these steps:
1. Select the entire name list you want to use for the lucky draw. Then click Kutools > Range > Sort / Select Range Randomly. See screenshot:
2. In the Sort/Select Range Randomly dialog box, go to the Select tab. Here, type in the number of random names you want in the No. of cells to select box (for example, 3), then choose Select random cells under the Select Type section. This allows you to pick any number of unique names at random. See screenshot:
3. Click Ok. The specified number of names will be randomly selected and highlighted in your list, so you can easily identify the winners or selected participants. See screenshot:
This method stands out for its ease of use and reliability, with additional options for sorting or shuffling names if desired. You can use this feature as many times as needed, and it avoids manual mistakes or repetition common with hand calculations. It’s ideal for those who want a quick solution without worrying about formulas or coding.
Note: Make sure to not select other irrelevant data in your range, as only the highlighted cells represent your winning names. Highlighted names can be copied or marked as needed for further use.
Click to Download Kutools for Excel and free trial Now!
In summary, using Kutools for Excel provides a user-friendly and highly efficient way to manage random draws. It is especially suitable when reliability and ease of use are your primary concerns, or when you need to conduct multiple draws with different group sizes.
Extract random names for making lucky draw with VBA code
For advanced scenarios or when you want to automate the process with more flexibility, VBA code can be used to extract random names from your list. This solution is suitable if you are comfortable with Excel’s Developer options and want to repeat draws or modify procedures—such as outputting results to a specific location or handling larger lists.
Follow these steps to use VBA for lucky draw:
1. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module to create a new module, then copy and paste the VBA code below into the module window.
VBA code: Extract random names from a list:
Public Sub LuckyDraw()
Dim I, J, xRnd As Long
Dim xSRg, xDRg As Range
Dim xDic As New Dictionary
Dim xnum, xLastRow As Long
On Error Resume Next
Set xSRg = Application.InputBox("Please select the data list:", "KuTools for Excel", Selection.Address, , , , , 8)
If xSRg Is Nothing Then Exit Sub
Set xDRg = Application.InputBox("Please selecta cell to put the result:", "KuTools for Excel", , , , , , 8)
If xDRg Is Nothing Then Exit Sub
xLastRow = xSRg.Rows.Count
Set xSRg = xSRg(1)
Set xDRg = xDRg(1)
xnum = Range("B2")
If xnum < 1 Then Exit Sub
J = 0
For I = 1 To xnum
LabExit:
xRnd = Int(Rnd() * xLastRow)
If xDic.Exists(xRnd) Then GoTo LabExit
xDic.Add xRnd, ""
xDRg.Offset(J, 0).Value = xSRg.Offset(xRnd, 0).Value
J = J + 1
Next
End Sub
Parameter clarification: In the code, B2 is the cell where you enter the number of random names to extract. You can change the cell references as needed.
3. After pasting the code, go to Tools > References in the VBA editor window. In the dialog that opens, check the option Microsoft Scripting Runtime in the Available References list. This step is required for enabling the scripting dictionary used in the code. See screenshot:
4. Click OK to close the dialog, then press F5 to run the code. A prompt box will appear asking you to select the data list containing the names you want to draw from. See screenshot:
5. Click OK. Another prompt box will appear for you to choose the target cell where you want the lucky draw results to be displayed. See screenshot:
6. Click OK to complete the process. The randomly selected names will then be output immediately starting from the cell you specified. See screenshot:
Practical tips: Before running the code, make sure you save your work. If you encounter errors, double-check your reference settings and cell range selections. This method gives you more control, but is best for users who are comfortable with basic VBA operations.
Pros and cons: The VBA approach is powerful for customization and can be tailored for advanced requirements, such as excluding former winners, automating notifications, and more. However, it requires basic VBA knowledge and may not be suitable unless macros are allowed in your environment.
Alternative: Extract random names using the RAND function and sorting
Besides the above methods, another practical and visual solution is to use Excel’s RAND function combined with sorting. This method is simple, requires no formulas with complex structures, no add-ins, and no coding—making it suitable for quick, occasional draws in any Excel version. It is especially helpful when you want to manually see and verify how randomization takes place.
Here’s how to do it:
- Add a helper column next to your names list, and enter =RAND() in the first cell of the helper column (for example, if your names are in A2:A16, enter =RAND() in B2).
- Copy the formula down alongside your entire list. Each cell will populate with a random decimal number.
- Select both your original names and the RAND helper column.
- Go to the Data tab and choose Sort. Set the sort to use the helper column with the RAND values, sorting smallest to largest (or vice versa). This will randomly reorder the entire list.
- Once sorted, simply pick the top N names from the reordered list as your lucky draw winners.
Tips and notes: Each time your worksheet calculates, the RAND function will update. If you want to freeze the draw results, copy the names and paste them as values elsewhere. If you want another draw, simply recalculate (F9).
Advantages: This approach is extremely easy to implement, requires no additional setup, and is clear for demonstrating fairness during live draws. However, it's less suitable if you need to frequently repeat draws or require advanced features such as exclusion lists, which can be better handled by formulas, VBA, or Kutools.
In summary, Excel offers multiple ways to select names randomly for lucky draws. The choice of method depends on your preference for simplicity, customizability, or visual interaction. For straightforward manual use, RAND and sorting or Kutools for Excel are recommended. For dynamic and reusable solutions, formulas or VBA provide additional flexibility. If you encounter errors or unexpected results, double-check your cell references, range selections, and ensure any necessary add-ins or macro settings are enabled. For best results, always save your data before starting a draw and validate results to avoid mistakes in important activities or team events.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in