RANDARRAY: Random Number Arrays (2025)
Quick Answer: RANDARRAY creates random arrays: =RANDARRAY(10,5) generates 10×5 decimals 0-1. Integers: =RANDARRAY(10,1,1,100,TRUE) for 1-100. Custom range: min/max parameters. Volatile - recalculates automatically. Use for: test data, simulations, sampling. Freeze: Paste Special → Values. Excel 365 only.
What is RANDARRAY?
RANDARRAY is a dynamic array function in Excel 365 that generates arrays of random numbers. It's perfect for creating test data, running simulations, statistical sampling, and generating random datasets without manual entry or complex formulas.
⚠️ Excel 365 Only
RANDARRAY requires Excel 365 subscription with dynamic arrays. Not available in Excel 2019, 2016, or earlier.
Alternative for older Excel: Use RAND() or RANDBETWEEN() with drag-fill, or array formulas with ROW/COLUMN.
Simple Examples:
10 Random Decimals (0-1):
=RANDARRAY(10)
0.847...
0.231...
0.956...
...
Random Integers 1-100:
=RANDARRAY(10,1,1,100,TRUE)
47
83
12
...
Common Use Cases:
✓ Test Data Generation
Create sample datasets for testing formulas and features
✓ Monte Carlo Simulations
Financial modeling and risk analysis scenarios
✓ Random Sampling
Select random items from datasets for analysis
✓ Game Development
Dice rolls, card shuffling, probability testing
RANDARRAY vs Traditional Functions:
RAND() - Single Random Decimal
Returns 0-1 decimal. Must drag-fill for multiple values. Recalculates continuously.
RANDBETWEEN() - Single Random Integer
Returns integer in range. Must drag-fill. Recalculates continuously.
RANDARRAY() - Dynamic Array
Returns entire array with single formula. Decimals or integers. Recalculates automatically. Excel 365 only.
💡 Volatile Function
Auto-recalculation: RANDARRAY recalculates on any worksheet change, generating new random values each time.
To freeze values: Copy result, then Paste Special → Values to convert to static numbers.
RANDARRAY Syntax
=RANDARRAY([rows], [columns], [min], [max], [integer])
Arguments (All Optional)
rows (optional, default 1)
Number of rows in the array
columns (optional, default 1)
Number of columns in the array
min (optional, default 0)
Minimum value in range
max (optional, default 1)
Maximum value in range
integer (optional, default FALSE)
Return integers or decimals
TRUE: Return whole numbers (integers)
FALSE or omitted: Return decimal numbers
Common Patterns
Default (Single Decimal 0-1)
=RANDARRAY()
Returns one random decimal between 0 and 1
10 Random Decimals
=RANDARRAY(10)
10 rows, 1 column, decimals 0-1
5×5 Grid of Decimals
=RANDARRAY(5, 5)
5 rows, 5 columns, decimals 0-1
Random Integers 1-100
=RANDARRAY(10, 1, 1, 100, TRUE)
10 whole numbers between 1 and 100
Random Decimals -10 to 10
=RANDARRAY(10, 1, -10, 10)
Negative min value allowed
Integer vs Decimal Examples
Decimals (FALSE/omitted)
3.847192...
7.234871...
1.928374...
9.103847...
5.719283...
Integers (TRUE)
4
7
2
9
6
Special Cases
Coin Flip (0 or 1)
Binary random values
Dice Roll (1-6)
Simulate dice throws
Percentage (0-100)
Random percentages with decimals
⚠️ Important Notes
- • Excel 365 only: Not available in earlier versions
- • Volatile function: Recalculates on any worksheet change
- • Dynamic array: Spills into multiple cells automatically
- • #SPILL! error: If target cells are blocked
- • Min < Max: Max must be greater than min value
- • Inclusive range: Both min and max are possible results
- • Uniform distribution: All values equally likely within range
- • Freeze values: Copy and Paste Special → Values to make static
10+ RANDARRAY Examples
Test Data Generation
1. Sample Sales Data
=RANDARRAY(100, 1, 1000, 50000, TRUE)
100 random sales amounts $1,000-$50,000
2. Random Ages
=RANDARRAY(50, 1, 18, 75, TRUE)
50 random ages 18-75 for demographic testing
3. Test Scores Grid
=RANDARRAY(20, 5, 0, 100, TRUE)
20 students × 5 tests, scores 0-100
Probability & Simulations
4. Coin Flips (1000 trials)
=RANDARRAY(1000, 1, 0, 1, TRUE)
0=Tails, 1=Heads simulation
5. Dice Rolls (Multiple Dice)
=RANDARRAY(100, 3, 1, 6, TRUE)
100 rolls of 3 dice each
6. Monte Carlo Price Simulation
=100 * (1 + RANDARRAY(252, 10, -0.05, 0.05))
252 trading days, 10 scenarios, ±5% daily returns
Sampling & Selection
7. Random Sample IDs
=RANDARRAY(25, 1, 1, 1000, TRUE)
25 random IDs from population 1-1000
8. Random Sort Key
=SORT(A2:B100, RANDARRAY(ROWS(A2:A100)), 1)
Randomize row order for random sampling
Specialized Applications
9. Random Percentages
=RANDARRAY(20, 1, 0, 1) & TEXT(RANDARRAY(20, 1, 0, 1), "0%")
For percentage-based scenarios
10. Random Dates (Next Year)
=TODAY() + RANDARRAY(50, 1, 0, 365, TRUE)
50 random dates in next 365 days
11. Temperature Data
=RANDARRAY(365, 1, -10, 35)
Year of daily temperatures -10°C to 35°C
🎯 Pro Tips
- • Freeze values: Copy result, Paste Special → Values to stop recalculation
- • Unique randoms: Combine with SORTBY(UNIQUE()) for no duplicates
- • Normal distribution: Use multiple RANDARRAY with AVERAGE for bell curve
- • Reproducible: Set calculation to manual for consistent testing
- • Large datasets: Consider performance with 10,000+ values
- • Statistical analysis: Use with AVERAGE, STDEV for distribution testing
- • Date ranges: Add RANDARRAY result to base date for random dates
Frequently Asked Questions
How do I stop RANDARRAY from recalculating?
RANDARRAY is volatile and recalculates automatically. To freeze values: (1) Copy the array result, (2) Select same cells, (3) Paste Special → Values (Ctrl+Alt+V, then V). This converts formulas to static numbers. Alternative: Set calculation mode to Manual (Formulas tab → Calculation Options → Manual) but this affects entire workbook. For reproducible testing, freeze values after generation.
Can RANDARRAY generate unique random numbers without duplicates?
RANDARRAY can produce duplicates. For unique values: =SORTBY(SEQUENCE(100), RANDARRAY(100)) then take first N rows with INDEX or TAKE. Or: =TAKE(SORTBY(source_range, RANDARRAY(ROWS(source_range))), count) for random selection without replacement. For true random unique: generate more values than needed, use UNIQUE(), then limit with TAKE().
How do I generate normally distributed random numbers?
RANDARRAY creates uniform distribution (all values equally likely). For normal distribution (bell curve): Use Box-Muller transform: =SQRT(-2*LN(RANDARRAY(n)))*COS(2*PI()*RANDARRAY(n)). Or approximate with Central Limit Theorem: =AVERAGE(RANDARRAY(12,1,0,1)) generates mean≈0.5, std≈0.29. Excel lacks built-in NORM.RANDARRAY but these methods approximate.
What's the maximum size array RANDARRAY can generate?
Limited by Excel worksheet limits (1,048,576 rows × 16,384 columns) and available memory. Practical limits lower: 10,000×10 (100K values) performs well. 100,000+ values may slow recalculation. Large arrays: consider freezing to values immediately, use manual calculation mode, or break into smaller chunks. Performance varies by computer specifications.
Can I use RANDARRAY to generate random text or names?
RANDARRAY only generates numbers. For random text: (1) Create number then use CHOOSE/INDEX to map to text: =CHOOSE(RANDARRAY(10,1,1,3,TRUE),'Red','Blue','Green'). (2) Random names: =INDEX(names_range, RANDARRAY(count,1,1,ROWS(names_range),TRUE)). (3) Random letters: =CHAR(RANDARRAY(10,1,65,90,TRUE)) for A-Z. Combine RANDARRAY with text functions for text generation.
Does RANDARRAY work in older Excel versions?
No, Excel 365 only (dynamic arrays required). Alternatives for Excel 2019/2016: (1) RAND() or RANDBETWEEN() with drag-fill down, (2) Array formula: {=RAND()*(max-min)+min} entered as Ctrl+Shift+Enter, (3) VBA macro for bulk generation. Dynamic array functions (RANDARRAY, SEQUENCE, FILTER) exclusive to Excel 365 subscription.