If you need to fill up a column with running numbers, 1,2,3…, you can either use the autofill function in Excel or by adding one to the cell above it, e.g. =A1+1.

Using the auto fill function will cause you to re-do the autofill whenever you insert a row in between the data range.

If you use the second method =A1+1, the number will be in running order when you copy the formula to the newly inserted row. But if you delete a row, you will get #REF for the row below it. In this case, what can you do to make sure that you can insert row and delete with ease?

Instead of using =A1+1, you can use the offset formula to replace A1, i.e. in A2, use =OFFSET(A2,-1,0,1,1)+1. The offset formula will always look for the cell above it. In this way, you can add and delete rows with min intervention.

Leave a Reply