Generating a Range of Sequential Numbers
August 22, 2022 - by Bill Jelen
![Generating a Range of Sequential Numbers Generating a Range of Sequential Numbers](/img/excel-tips/2022/08/generating-a-range-of-sequential-numbers.jpg)
I have a cool trick in my Power Excel seminars where I show how you can Ctrl + drag the fill handle from a cell with the number 1, and Excel will extend to 2, 3, 4. It is an obscure trick, and not many people know it. When I first saw the SEQUENCE
function, I thought it might be for people who had never discovered this trick. But as you will see here, the SEQUENCE
function is the key to making many other Excel functions into array functions.
SEQUENCE
generates an array of numbers. The syntax is =SEQUENCE(rows, [columns], [start], [step])
.
While =SEQUENCE(10)
generates 1 through 10, you can customize the numbers with =SEQUENCE(10,3,5,10)
to generate a 10-row by 3-column array starting at 5 and stepping by 10.
![The syntax of SEQUENCE is Rows, Columns, Start, Step. In this figure =SEQUENCE(10,3,5,10) returns three columns and 10 rows of numbers. The first row is 5, 15, 25. The next row is 35, 45, and 55. This pattern continues down to 275, 287, 295 in the tenth row.](/img/content/2022/08/sequence-and-randarray-functions-01.jpg)
The RANDARRAY
function is great for driving Monte Carlo analysis. To use it, you just specify how many rows and columns of random numbers you want.
![The syntax for RANDARRAY is Rows, Columns, Min, Max, Integer. Every argument is optional. In this image, =RANDARRAY(10,3) returns 10 rows and 3 columns of RAND.](/img/content/2022/08/STPDA-1.jpg)
A March 2019 improvement to RANDARRAY
allows you to specify a min and max and also that you only want integers.
![This formula shows how RANDARRAY can now return integers between a Min and a Max. Five columns of integers between 11 & 17 happen with =RANDARRAY(,5,11,17,True).](/img/content/2022/08/STPDA-2.jpg)
This article is an excerpt from Power Excel With MrExcel
Title photo by Jeffrey Brandjes on Unsplash