Ever needed to generate a full array of random values in Excel? This is a great way to test out formulas. Fortunately, Excel will do this automatically. You don’t have to input numbers yourself. Let’s learn how to use RANDARRAY in Microsoft Excel.
How to Use RANDARRAY in Excel
Imagine this scenario: you need to generate five rows and ten columns of random numerical values. Of course, you could make up 50 numbers, but this would take a while. And it wouldn’t be truly random. The best way to do this is to use Excel’s RANDARRAY function.
To use RANDARRAY, click into the first cell where you want to place your random array of values. For simplicity, we’ll use cell A1 in this example, but you can use any cell in your workbook.
Begin by typing an = sign, telling Excel that you’re inputting a formula into the selected cell. Then, insert the RANDARRAY function. So far, your formula reads:
=RANDARRAY(
Then, Excel asks for five inputs. First, you’ll need to input the number of rows, which is 5. Next, add the number of columns, which here is 10:
=RANDARRAY(5,10,
Then, Excel needs a minimum and maximum for your range of random values. All values in the array will fall between these two. We’ll use 1 and 500 here:
=RANDARRAY(5,10,1,500,
Finally, Excel needs to know if you want to have integer or decimal values. Integers, in essence, are round numbers. Decimal values may have one or more decimal places. To use round integer values, type TRUE. If you want decimals in your array, type FALSE. Here, we’ll use TRUE:
=RANDARRAY(5,10,1,500,TRUE)
When you’re finished keyboarding your formula, press Enter. Excel will return a random array comprising fifty unique values, all within your selected range.
As you can see, it’s easy to use RANDARRAY in Excel to generate a large volume of random values.
Calculate ATR in Excel