# Fill up a column in Excel with sequential numbers

### Question

• 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

Thursday, March 27, 2008 10:09 PM

• Two easy options that I can see.

1. 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").
2. 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.
Friday, March 28, 2008 7:06 AM

### All replies

• Two easy options that I can see.

1. 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").
2. 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.
Friday, March 28, 2008 7:06 AM
• Dragon,