XL.Random function: generate an array of unique random numbers

How to use Random function for Excel: formula and results

XL.Random function – Array function type – generates an array of random numbers. Supports the ability to generate a unique set of random numbers.

XL.Random function helps avoid the limitations of the standard Excel functions like RANDARRAY and RANDBETWEEN:

  • The function is not volatile, i.e. values will not be recalculated every time you make changes in your spreadsheet
  • Makes it easy to generate an array of unique random numbers

Before you begin, add Functions to Excel

Functions are one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.

Get started with XLTools ribbon
Download XLTools for Excel
– free trial gives you 14 days of full access to all features.

Syntax

=XL.Random(from, to, unique)

ArgumentDescription
from
[required]		The smallest integer that the function will return
to
[required]		The largest integer that the function will return
unique
[optional]		Return a unique array of numbers:
  • TRUE for a whole number
  • FALSE for a decimal number

Examples [download]

Download this spreadsheet with examples and try the function yourself.

Download examples with this function (xlsx)

FormulaDescription
{=XL.Random(1,22,TRUE)}Fill the array with unique random numbers from 1 to 22
{=XL.Random(1,22)}Fill the array with random numbers from 1 to 22. Numbers are not unique and can repeat.

Explore other functions

Any questions or suggestions?
How can we help?