none
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

Answers

  • 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,

     

    Thanks a lot for your answer!

     

    Cath ;-)

    Friday, March 28, 2008 4:27 PM