How to Use the Excel RANDARRAY Function to Generate Random Datasets

The Problem with Dummy Data

If you are an analyst trying to build a complex dashboard, test a new financial model, or teach a coworker how to use pivot tables, you need data to work with.

Using real corporate data is often a security risk, and manually typing out hundreds of fake numbers is an enormous waste of time. For years, Excel users relied on the RAND() and RANDBETWEEN() functions to generate dummy numbers. However, those functions only fill one cell at a time. If you wanted a dataset of 10,000 numbers, you had to write the formula and physically drag it down across thousands of rows.

With the introduction of Dynamic Arrays in modern Excel (Microsoft 365), you no longer have to drag formulas. You can use the powerful RANDARRAY function to instantly blast thousands of random numbers across a massive grid, generating a complete dataset in a single keystroke.

Step 1: Understanding the RANDARRAY Syntax

The RANDARRAY function is a “spill” function. You type the formula into a single cell, and the results automatically spill out into the surrounding rows and columns to fill the size you specify.

The Syntax:
=RANDARRAY([rows], [columns], [min], [max], [whole_number])

• rows: How many rows tall you want the dataset to be.
• columns: How many columns wide you want the dataset to be.
• min: The lowest possible random number.
• max: The highest possible random number.
• whole_number: Use TRUE for integers (no decimals) or FALSE for decimals.

Step 2: Generating a Massive Grid

Let’s say you want to generate a massive grid of data to test how fast Excel can process a VLOOKUP. You want the grid to be 500 rows tall and 5 columns wide.

Select cell A1 and type:

=RANDARRAY(500, 5)

Press Enter. Instantly, a block of 2,500 random numbers will appear, filling the grid from A1 down to E500. Because we did not specify a minimum or maximum, Excel defaults to generating long decimal numbers between 0 and 1.

Step 3: Simulating Realistic Financial Data

A grid of decimals isn’t very realistic for testing financial models. Let’s create a simulated dataset of “Daily Sales Revenue” for a small store.

You need a single column (1) that is 30 rows tall (for a month). The store makes between $500 and $2,000 a day, and you only want whole dollar amounts.

Type this formula:

=RANDARRAY(30, 1, 500, 2000, TRUE)

Press Enter. You will immediately have a clean, realistic column of 30 integers ranging between 500 and 2000. You can then format that column as Currency.

Step 4: The Volatility Problem

You will quickly notice a frustrating quirk of RANDARRAY: it is a “volatile” function.

This means that every single time you do anything in the workbook—type a word in another cell, delete a row, or even just press F9—the function recalculates, and all your random numbers will instantly change.

If you are trying to build a static test dashboard, shifting numbers will ruin your tests.

To fix this, you must freeze the generated data:

1. Highlight the entire block of numbers that spilled from your RANDARRAY formula.
2. Copy the block (Ctrl+C).
3. Without moving your cursor, right-click the exact same highlighted area.
4. Under Paste Options, select Paste as Values (the icon with the clipboard and the numbers 123).

This deletes the underlying formula and replaces it with the hardcoded, static numbers, locking your dummy dataset in place permanently.

Step 5: Combining RANDARRAY with CHOOSE for Text Data

RANDARRAY only generates numbers, but what if you need a random list of names or cities to test a Pivot Table?

You can combine RANDARRAY with the CHOOSE function. The CHOOSE function picks an item from a list based on an index number. By feeding it random index numbers, you get random text.

To generate a random list of 20 cities (choosing between New York, London, and Tokyo), type:

=CHOOSE(RANDARRAY(20, 1, 1, 3, TRUE), "New York", "London", "Tokyo")

How it works: The RANDARRAY forces the system to generate 20 random numbers between 1 and 3. The CHOOSE function reads those numbers and outputs the corresponding city from your text list. You now have a realistic, randomized categorical dataset.