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?

1 Answer 1


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, 2019 at 10:04
  • I've updated the answer with a simpler way to generate an array of sequential numbers.
    – pjmg
    Apr 30, 2020 at 0:10
  • Whoa, that is indeed an even better solution. Where's the double-accept button?
    – Lou
    Apr 30, 2020 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, 2020 at 10:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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