top of page
  • Writer's pictureDp

How To Quickly Generate Serial Numbers In Excel

Updated: Apr 30, 2019

In this video tutorial, let us learn how to easily generate serial numbers in excel data range.

This is very quick and easy to perform.

This is the auto generated serial number system in excel.

ROWS and ROW function have been used for this purpose, which gets automatically updated.

First Let me show the drawbacks of using general ways of performing this action.

1st Wrong Method:

Generally we would be simply using the fill down option like shown in the demo below.

the drawback of using this way, is that when we delete any particular row at later stage, it won't get updated automatically and shows wrong values. (like shown below)

2nd Wrong Method:

We would be entering the formula in the 2nd cell as +1 to the preceding cell and then fill down to generate serial numbers.

the drawback of this method is also the same , but here when we delete any row, it would clearly show error as #REF (shown below)

So, let us now see the right way of doing this.

1st Right Method:

Here, we will be using the function called ROWS to fetch the row number in each cell.

See, even if we delete any row, the serial number gets automatically updated unlike in our previous case.

2nd Right Method:

We can even use the function called ROW, but here we need to subtract each Row's number with the locked first cell's row number.

If we delete any particular row, you can observe that the serial numbers getting updated automatically.

the other benefit is that , when you insert a new row, observe that the serial numbers automatically getting updated, , you just need to apply the same formula to the new cell by dragging the preceding cell's formula.

Watch this video tutorial for better understanding:

If you liked this tutorial, share it with your friends. And also you can follow us on Youtube, Twitter and Facebook. We would love to hear from you, Please do comment, suggest or compliment our work and we shall make it better for you. You can write us at


Recent Posts

See All


bottom of page