What is the RANDARRAY Function in Excel 365?

—

by

Himanshu
in

The RANDARRAY function generates an array of random numbers. You can define the number of rows and columns, the minimum and maximum values, and whether you want whole numbers or decimals.

It’s part of the dynamic array functions introduced in Excel 365 and is useful for simulations, testing, data sampling, random list creation, and more.

🔧 Syntax of RANDARRAY

excelCopyEdit=RANDARRAY([rows], [columns], [min], [max], [whole_number])
ParameterDescription
rows(Optional) Number of rows to return
columns(Optional) Number of columns to return
min(Optional) Minimum value (default is 0)
max(Optional) Maximum value (default is 1)
whole_number(Optional) TRUE for integers, FALSE for decimals (default is FALSE)

✅ Examples of RANDARRAY in Excel

🔹 Example 1: Generate a 5×2 Array of Random Decimals Between 0 and 1

excelCopyEdit=RANDARRAY(5, 2)

Generates 5 rows and 2 columns of decimal numbers between 0 and 1.

🔹 Example 2: Generate 10 Random Whole Numbers Between 1 and 100

excelCopyEdit=RANDARRAY(10, 1, 1, 100, TRUE)

Creates a single column of 10 random whole numbers between 1 and 100.

🔹 Example 3: Generate a 3×3 Matrix of Random Decimals Between 50 and 75

excelCopyEdit=RANDARRAY(3, 3, 50, 75)

Each cell contains a random decimal number in the range 50–75.

🔹 Example 4: Dynamic Range for Randomized Data

If you link rows/columns to cell values:

excelCopyEdit=RANDARRAY(A1, B1, 10, 99, TRUE)

This generates random whole numbers based on user-defined dimensions from cells A1 and B1.

📌 Key Features

  • Recalculates every time the sheet changes (just like RAND or RANDBETWEEN)
  • Generates arrays dynamically — no need to drag formulas
  • Replaces the need for helper columns when generating random values
  • Supports structured logic when used with INDEX, SORTBY, SEQUENCE, etc.

🧠 Use Cases

  • Random student roll numbers
  • Create sample datasets for testing
  • Simulate random sampling in analytics
  • Build games or quizzes in Excel
  • Generate randomized IDs, passwords, or numbers

🔒 Prevent Random Changes

To freeze the results (make them static):

  1. Select the range.
  2. Press Ctrl + C to copy.
  3. Right-click > Paste Values.

🚀 Combine with Other Functions

Example: Randomly sort names in A2:A10

excelCopyEdit=SORTBY(A2:A10, RANDARRAY(ROWS(A2:A10)))

This randomly shuffles the list of names.

🎓 Want to Learn More About Excel 365’s Smartest Tools?

Explore RANDARRAY, SORTBY, UNIQUE, FILTER, LET, and more in real-world projects with my Excel course:

👉 Mastering MS Excel – A Comprehensive Training Course

Top rated products