📎 This article includes 1 downloadable practice file ↓
In this article
RANDARRAY returns an array of random numbers, optionally whole numbers between limits — instant test data.
Syntax
=RANDARRAY([rows], [columns], [min], [max], [whole_number])| Argument | What it means |
|---|---|
whole_number |
TRUE for integers. |
Examples
Example 1
=RANDARRAY(100, 1, 1000, 50000, TRUE)100 random amounts.
Example 2
=SORTBY(A2:A50, RANDARRAY(49))Shuffle a list, e.g. for a random draw.
RANDARRAY in practice
Where you will use it
- Sample test data quickly
- Random rows for audits
- Simulations
Worked example
| Rows | Formula | Result |
|---|---|---|
| 3 | =TEXTJOIN(", ",,RANDARRAY(A2,1,1,100,TRUE)) | (changes every recalculation) |
| 5 | =TEXTJOIN(", ",,RANDARRAY(A3,1,1,100,TRUE)) | (changes every recalculation) |
| 2 | =TEXTJOIN(", ",,RANDARRAY(A4,1,1,100,TRUE)) | (changes every recalculation) |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Values keep changing | Copy > Paste Values to freeze |
| Duplicates | RANDARRAY can repeat; use SORTBY(SEQUENCE(n),RANDARRAY(n)) for unique order |
Related functions
RAND · RANDBETWEEN · SORTBY
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
⚡ Interactive Practice Lab & Formula Tester:
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Dynamic arrays18 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 35 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong