Answered by:
Fill up a column in Excel with sequential numbers

Hey,
In column B4 I want to enter the value 4. In the next cells of that column I need a formula or function to automatically fill up the cells with sequential numbers like 5 6 7 8 9 10 11 12 and so on. However, if I insert or delete a row the numbers must update. See what I mean?
Does anyone know how to do so?
Thanks!Best Regards
Cathrine
Question
Answers

Two easy options that I can see.

You can use the ROW() function. That returns the current row number. So in your example, fill all the cells from row 4 down with "=ROW()". If you want it to be higher or lower than the row number, just add or subtract a constant (eg "=ROW()+2"). If you want the increment to be something other than 1, use a multiplier (eg "=ROW()*2+1").

Use the OFFSET function to refer to the cell one row above the current cell. For example, in Cell B5 put the formula "=OFFSET(B5,1,0)+1". This is not a circular reference, because veen though the formula contains the current cell's address, it is still referring to a different cell. The formula will always point to the cell one row above, regardless of cells being moved, rows inserted or deleted, etc.

All replies

Two easy options that I can see.

You can use the ROW() function. That returns the current row number. So in your example, fill all the cells from row 4 down with "=ROW()". If you want it to be higher or lower than the row number, just add or subtract a constant (eg "=ROW()+2"). If you want the increment to be something other than 1, use a multiplier (eg "=ROW()*2+1").

Use the OFFSET function to refer to the cell one row above the current cell. For example, in Cell B5 put the formula "=OFFSET(B5,1,0)+1". This is not a circular reference, because veen though the formula contains the current cell's address, it is still referring to a different cell. The formula will always point to the cell one row above, regardless of cells being moved, rows inserted or deleted, etc.

