I want to produce an array of numbers from 1 to n, where n is a value in another cell. So if the cell value is 10, I want to generate an array of the numbers 1 to 10.

I found this resource which appears to answer a similar question:


But I think that it requires you to drag down the fill handle, also I didn't really understand from the resource how to apply this formula to generating n numbers. I want a formula which generates the numbers 1 to n without using the fill handle.

Is there a way to do this?


The easiest way to generate an array of sequential number in Google Sheets is the =Sequence function.

Syntax : SEQUENCE(rows, columns, start, step)
SEQUENCE function on Google's Help Center


There's a way without dragging a cell with Arrayformula :

=ArrayFormula(ROW(INDIRECT("A1:A"&A2))) Where A2 is the n value

enter image description here

There's no incrementation in Google spreadsheet so the only way is to find a build-in counter : the rows/columns.
ROW() and COLUMN() return the adress of a cell or an array with Arrayformula. Now your case is using a dynamic range, to do so we use INDIRECT()

INDIRECT() returns a cell reference specified by a string.

Using & to concatenate the first row number (1) with the n value.

  • 1
    Perfect, does exactly what I need it to do. Thank you! – Lou Aug 13 '19 at 10:04
  • I've updated the answer with a simpler way to generate an array of sequential numbers. – pjmg Apr 30 '20 at 0:10
  • Whoa, that is indeed an even better solution. Where's the double-accept button? – Lou Apr 30 '20 at 9:44
  • you can ask the question a second time, answer it while checking the community wiki box ! But I'm not sure this is necessary :) – pjmg Apr 30 '20 at 10:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.