sodcalculator

10 Tech How-To

Pick Names From a Hat in Excel: Step-by-Step

Guide 10-45495 · Updated · Step by step

This guide shows you how to use Excel to randomly select one or more names from a list, just like drawing names from a hat. You’ll set up a simple spreadsheet, use Excel’s random number functions, and get a fair, unbiased pick every time. No prior Excel experience is needed beyond basic data entry.

What You’ll Need

  • Microsoft Excel (2010 or later, including Microsoft 365)
  • A list of names you want to pick from (typed into a column)

Step-by-Step

  1. Open a new Excel workbook. Create a new blank workbook or use an existing one where you want to perform the random selection.
  2. Enter your names in a column. In column A, starting at cell A1, type each name you want to include in the draw. Put one name per cell, going down. For example, A1 = “Alice”, A2 = “Bob”, A3 = “Charlie”, and so on. Make sure there are no blank cells in the middle of your list.
  3. Add a helper column for random numbers. Click on cell B1 (or any cell in the column next to your names). Type the formula =RAND() and press Enter. This generates a random decimal number between 0 and 1.
  4. Copy the formula down. Click on the small square at the bottom-right corner of cell B1 (the fill handle) and drag it down to cover all the names in your list. Now each name has a random number next to it.
  5. Create a cell for the result. Pick an empty cell where you want the winning name to appear. For instance, cell D1. This is where you’ll put the formula that pulls out the name.
  6. Use INDEX and MATCH to find the name. In cell D1, type the following formula: =INDEX(A:A, MATCH(MAX(B:B), B:B, 0)). Press Enter. This formula finds the largest random number in column B and returns the name from column A that sits on the same row. That’s your randomly selected name!
  7. Press F9 to re-draw. Every time you press the F9 key (or recalculate the sheet), Excel generates new random numbers, and the name in D1 changes. This simulates drawing a new name from the hat each time.
  8. For multiple names without repeats: If you need to pick several distinct names, the simple formula above won’t work because it always picks the same name if that name has the highest random number. Instead, sort the list by the random numbers. Click anywhere in your data (A1:B10), go to the “Data” tab, and click “Sort”. Sort by column B (the random numbers) from largest to smallest. The names at the top are your random picks. Copy them out. To get a new set, press F9 and sort again.

Common Mistakes

  • Not pressing F9: Forgetting to recalculate means you see the same name over and over. Press F9 to get a new pick.
  • Blank cells in the list: If your name list has empty cells, the random numbers will still generate, but your result might be a blank. Ensure your list is contiguous with no gaps.
  • Using RANDBETWEEN instead of RAND: RANDBETWEEN generates integers, which can cause ties if you have more names than possible random numbers. RAND gives unique decimals, reducing tie chances dramatically.
  • Copying the result as a value: If you copy the name from D1 and paste it elsewhere, it will change when you press F9. To keep a permanent record, copy the cell and use “Paste Special” > “Values”.
  • Sorting the wrong column: When sorting for multiple picks, make sure you sort by the random number column, not the name column, or you’ll just alphabetize your list.

Tips

  • Use a named range for clarity: Instead of referencing A:A, you can select your names and define a name (e.g., “Names”) in the Name Box. Then use =INDEX(Names, MATCH(MAX(B:B), B:B, 0)).
  • Make it a one-click draw: You can add a button (Form Control) that runs a macro to recalculate, but that’s advanced. For most, pressing F9 is enough.
  • Highlight the winner: Use conditional formatting to highlight the name in column A that matches the result in D1. Select A1:A10, go to Home > Conditional Formatting > New Rule > Use a formula. Enter =$A1=$D$1 and set a fill color.
  • For a single pick, use a simpler approach: If you only need one name and don’t care about the helper column, you can use =INDEX(A:A, RANDBETWEEN(1, COUNTA(A:A))). This works if you have no blanks and want a quick result, but it’s less flexible for multiple picks.
  • Keep the random numbers hidden: You can hide column B to keep your sheet tidy. Right-click the column header and select “Hide”.

Troubleshooting

  • The result shows #N/A: This usually means the MATCH function can’t find the maximum random number. Check that column B actually has numbers (not text) and that there are no errors in the cells. Also, ensure your range in the formula matches your data.
  • The result shows #VALUE!: This happens if your name column contains numbers or if the formula is entered incorrectly. Double-check the formula syntax and make sure column A contains text.
  • The name doesn’t change when I press F9: Your calculation mode might be set to “Manual”. Go to Formulas > Calculation Options and select “Automatic”. If it’s already automatic, try pressing Ctrl+Alt+F9 to force a full recalculation.
  • I get the same name repeatedly: This can happen if your random numbers aren’t recalculating, or if you have a very small list (e.g., 2 names) and the same name wins by chance. Press F9 multiple times to see variation.
  • Sorting messes up my data: If you sort only the random column and not the names, they’ll get mismatched. Always select both columns (A and B) before sorting.

Frequently Asked Questions

Q: Can I use this to pick multiple names at once without repeats?
A: Yes. After sorting by the random column, take the top N names. If you need to do this automatically, you’d need a more complex array formula or a macro, but the sort method is the simplest manual way.

Q: Is there a way to make the result permanent?
A: Yes. Copy the cell with the result, then right-click and select “Paste Special” > “Values”. This pastes just the name as text, and it won’t change when you recalculate.

Q: Does this work in Google Sheets?
A: Yes, the same formulas work in Google Sheets. The only difference is that in Google Sheets, you might not need to press F9; it recalculates automatically, but you can force it by changing any cell.

Q: What if I have a very large list of names?
A: The formula works fine with thousands of names. Just make sure you drag the RAND formula down far enough to cover all names. The INDEX/MATCH approach is efficient even for large lists.

Q: Can I exclude certain names from the draw?
A: Yes. You can either delete them from the list, or use a more advanced formula with IF conditions to filter them out. For example, you could add a column with “Yes”/”No” and use an array formula to only consider “Yes” rows.

Q: Is this truly random?
A: Excel’s RAND function uses a pseudo-random number generator that is good enough for most purposes like picking names. It’s not cryptographically secure, but for a fair draw among friends or colleagues, it’s perfectly acceptable.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →