How to randomly fill values from a list of data in Excel?
Randomly selecting values from a predefined list in Excel is a common task with applications in data analysis, simulations, randomized assignment, sampling, testing scenarios, and more. For example, you might want to simulate draws for a prize, allocate randomized test cases for quality assurance, or distribute tasks randomly among team members. Achieving this in Excel can make your workflow much more efficient and less error-prone compared to manual selection.
This comprehensive guide will walk you through multiple methods to accomplish this goal, ranging from straightforward formula-based approaches suitable for all users, to advanced automation with VBA, and even with specialized, user-friendly tools like Kutools for Excel. Each method has its own strengths and ideal usage scenarios, which are analyzed below to help you pick the best solution for your needs.
Randomly fill values from a list of data with formulas
In this section, we’ll walk you through several practical formula-based methods to randomly fill values from a specified list. These solutions require no extra installation and can be quickly implemented across most modern versions of Excel.
✅ Formula1: INDEX + RANDBETWEEN functions
The combination of the INDEX and RANDBETWEEN functions is a classic and cross-version-compatible way to randomly pick values from a list. It is suitable for quickly generating single or multiple random values where duplicate picks are acceptable, such as in random sampling or mock data generation.
To use this method, simply copy or enter the following formula into a blank cell (for example, B2), then drag the fill handle down to populate as many random values as needed. Remember, since the formula involves volatile functions (like RANDBETWEEN), its result will change each time the sheet recalculates.
=INDEX($A$2:$A$15, RANDBETWEEN(1, COUNTA($A$2:$A$15)))
- A2:A15: Represents the list of values from which you want to randomly select.
- COUNTA($A$2:$A$15): Dynamically counts the number of items in your list, ensuring the formula remains robust if the list length changes.
- RANDBETWEEN(1, n): Generates a random integer between 1 and n (the number of items in the list).
- INDEX(range, number): Retrieves the item corresponding to the randomly selected position from your list.
Precautions: Since the value refreshes on any change in the worksheet, if you require the results to remain unchanged, be sure to copy the filled cells and paste as values. Also, this strategy does not exclude duplicates—if uniqueness is required, consider methods described in later sections or perform post-processing.
✅ Formula2: INDEX + RANDARRAY functions (Excel 365 / 2021+)
The combination of INDEX and RANDARRAY functions is suitable for Excel 365 and Excel 2021 users. This approach utilizes dynamic arrays to output batches of random selections at once, streamlining workflows that require many random picks in a single step. It is especially useful when you need a certain quantity of random selections quickly. Note, however, that like the previous formula, this method does not guarantee the uniqueness of results within the batch.
To use this solution, input the formula into a blank cell, such as B2, and press Enter. Excel will automatically “spill” the generated random values into subsequent rows. For example, the following formula outputs 5 random values from the list:
=INDEX(A2:A15, RANDARRAY(5, 1, 1, COUNTA(A2:A15), TRUE))
- A2:A15: The designated data list for random selection.
- COUNTA(A2:A15): Counts entries in the target list.
- RANDARRAY(5,1,1, COUNTA(...), TRUE): Generates 5 random integers between 1 and the last position in the list, producing a vertical array (1 column).
- INDEX(A2:A15, …): Maps each random number to a value from your list.
Tips: If you require a different number of random values, simply adjust the 5 in RANDARRAY(5,1, ...) accordingly. Always remember to paste as values if you need the results to remain fixed, as formula-based outputs update with sheet changes.
Randomly fill values from a list with VBA (Advanced & Customizable Solution)
If you need to automate large-scale random value assignments, prevent duplicates, or require more customization (such as applying complex logic during selection), a VBA (Visual Basic for Applications) approach is ideal. VBA enables you to generate truly unique random picks, apply custom distribution logic, and repeat tasks with a single command—helpful for advanced simulations, automated random allocation, or working with extensive data sets.
This solution is suitable for users comfortable with macros or those looking to automate their Excel workflows.
1. Open the VBA editor by clicking Developer > Visual Basic (or press Alt + F11), which will open the Microsoft Visual Basic for Applications window. Then, go to Insert > Module and paste the code below into the module window:
Sub RandomFillFromList_NoDuplicates()
Dim srcRange As Range
Dim destRange As Range
Dim srcValues As Variant
Dim destCount As Integer
Dim usedIndexes As Object
Dim i As Integer
Dim randIndex As Integer
On Error Resume Next
Set srcRange = Application.InputBox("Select source list", "KutoolsforExcel", Type:=8)
If srcRange Is Nothing Then Exit Sub
Set destRange = Application.InputBox("Select destination range (number of random values to fill)", "KutoolsforExcel", Type:=8)
If destRange Is Nothing Then Exit Sub
srcValues = Application.Transpose(srcRange.Value)
destCount = destRange.Cells.Count
Set usedIndexes = CreateObject("Scripting.Dictionary")
If UBound(srcValues) < destCount Then
MsgBox "Not enough unique items in the source list to fill destination without duplicates.", vbExclamation, "KutoolsforExcel"
Exit Sub
End If
Randomize
For i = 1 To destCount
Do
randIndex = Int(Rnd() * UBound(srcValues)) + 1
Loop While usedIndexes.Exists(randIndex)
usedIndexes(randIndex) = True
destRange.Cells(i).Value = srcValues(randIndex)
Next
End Sub2. Run the macro by clicking the 
button on the VBA toolbar. The macro will prompt you to select (a) the source list (the range of values to pick from), and (b) the output range (For the number of random values extracted, simply select an equal number of cells). The code ensures that there are no duplicate values in the output if the source list is large enough. If not, it will display a warning.
This VBA method offers the following advantages and considerations:
- Pros: Ensures random, non-repeating selections; allows handling of very large lists and batches; easy to automate repeated tasks.
- Cons: Requires macro-enabled Excel files. If your workbook restricts macros, this approach may not be suitable. Errors may occur if destination count exceeds the number of source items.
- Error reminders: The macro will notify you if there aren't enough unique values in the source list for your request.
- Customization tips: You can further adapt the code to allow duplicates by removing the uniqueness check, or to implement weighting or filtering logic for more specialized scenarios.
Randomly select and fill values from a list of data with Kutools for Excel (All versions)
Kutools for Excel provides an accessible and interactive solution for randomly selecting and filling values from a list. It’s ideal for users who want to handle random assignments without writing formulas or code, or those who need to quickly process selections in bulk with minimal manual input. Kutools also provides options for controlling output, such as the number of values to pick, and does so through a straightforward dialog interface.
After installing Kutools for Excel, proceed as follows to use its built-in random selection functionality:
- Select the range containing the values you want to randomly choose from.
- Click Kutools > Range > Sort / Select Range Randomly. Refer to the screenshot below:
- In the Sort / Select Range Randomly dialog box, go to the Select tab, and do the following:
- Specify the number of cells to select randomly.
- Ensure you choose the Select random cells option within Select Types.
- Finally, click the OK button.
- The indicated number of random cells will be highlighted or selected. You can then copy and paste these cells elsewhere as needed.
In addition to simplicity, the Kutools method also prevents mistakes common with manual randomization and requires no familiarity with Excel’s formulas or macro settings. If you want unique values in your selection, make sure that the source list is larger than the number of items you plan to pick, and verify options within the dialog for selecting without duplicates if available.
🔚Conclusion
Randomly filling values from a predefined list in Excel can be managed efficiently using a variety of techniques suited for different knowledge levels and scenarios:
- For all Excel versions, the INDEX plus RANDBETWEEN formula is quick and reliable for generating random selections, especially in lists where allowing duplicates is acceptable.
- If you have Excel 365 or 2021, the RANDARRAY + INDEX solution provides a more dynamic batch selection that speeds up processes when many outputs are needed at once.
- For highly customizable needs—such as ensuring no duplicates, automating large random assignments, or handling complex selection logic—the VBA method gives maximum flexibility, though users should be familiar with running macros.
- If you prefer a no-code, user-friendly approach, Kutools for Excel allows you to generate randomized selections via a graphical interface, making it suitable for both beginners and advanced users needing quick results.
It's important to consider whether you need unique selections or can allow duplicates, how many random selections you require, and your comfort level with Excel formulas or macros. Before sharing or saving random results, use the paste-as-values feature to prevent accidental recalculation. For users interested in exploring additional Excel solutions, visit our Excel tutorial section for more practical guides and tips.
Troubleshooting suggestions: Double-check list ranges for accuracy, be mindful of recalculation with volatile functions, and make sure your macro security settings allow VBA execution when using code-based solutions. If errors occur when using VBA (such as insufficient source list size), follow the prompt and reconsider your ranges.
Related articles:
Randomly select cells based on criteria in Excel
Randomly add background/fill color for cells in Excel
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