Experts know the importance of serial numbers.

Serial numbers are like salt. You never feel their presence but their absence makes your data tasteless.

Yes, they are important.

Because with a serial number you can have a unique identity to each entry of your data.

But the sad news is, adding them manually is a pain.

It’s really hard to add a number in every row one after another.

The good news is, there are some ways which we can use to automatically add serial numbers in a column.

## 14 Ways to Insert Serial Number Column in Excel

And today, in this post, I’d like to share with you **14-Quick Methods**.

You can use any of these methods which you think is perfect for you.

These methods can generate numbers up to a specific number or can add a running column of numbers.

…choose one of the below methods as per your need and if you think you have a way, then please share it with me in the comment section.

#### Method #1

#### Method #2

#### Method #3 (Fastest)

#### Method #4

#### Method #5 (My Favorite ?)

#### Method #6

#### Method #7

#### Method #8 (VBA Code)

#### Method #9

#### Method #10

#### Method #11

#### Method #12 (For Pivot Table Lovers)

#### Method #13

#### Method #14

## Conclusion

If you have data whether small or large it is must to add serial numbers to it.

The one thing which you really need to understand that a serial number give a unique identity to each entry.

And, with all the methods you have learned above it’s no big deal to create a serial number column in the data, no matter which situation you are in.

I hope you found this useful, but now, tell me one thing.

*Which method do you prefer to insert serial numbers?*

Please share your views with me in the comment section. I'd love to hear from you and please don’t forget to share it with your friends, I am sure they will appreciate it.

**You must Read these Next**

- Bullet Points in Excel: Unlike Word, in Excel, there is no default option to insert bullet points. But there are totals...
- Check Mark in Excel: Eventually today morning, I thought maybe there is more than one way to add a check mark...
- Strikethrough in Excel: When it comes to Excel, we don’t have any direct option to apply strikethrough to a cell...
- Degree Symbol in Excel: While working on that data we have found that in Excel you can enter/type it using...
- Insert a Timestamp in Excel: In general, it contains the current date and time, and we use it to capture completing...
- Insert Square Root Symbol: Once you calculate a square root from a number, the next thing you can do is to...
- Delta Symbol in Excel: Take an example of Delta (Δ) symbol which we can use to present the difference between...
- Add Leading Zeros: Whenever you try to insert a zero before a number, Excel removes it and you’ll only get the number starting from...

Totally not what I was looking for. I have a single excel file. I have a field at the top “work order number”. Each time I open the file and do a “save as”, I want it to increment. How do I do that?

how to insert 4 columns in between sl.no. 1 to 800 for example sl.no.1 4 empty columns, sl.no.2 4 empty columns, sl.no.3 4 empty columns, this through out the 800 sl.nos

i want to put serial no as below data in automatic (colon A having s/n, B having group data but i need sn in group data wise

1 HS Code

24031100

24031100

24031100

2 HS Code

22 02 99 90

3 HS Code

24031920

24031920

24031920

4 HS Code

24022010

5 HS Code

24021000

24021000

24021000

Dynamic Serial Number method good find.

Also Learnt usage of Row and CountA function in Serial number from this. Thank you 🙂

Thanks a lot for your explanations! it really helped me..I personally like method 3 and 5.

What formula will be use for this type of numbring

S.N. QUALITY

1 2/60 ERI

1 10 S SLUB COTT

1 25S ACRLYIC

2 2/60 ERI

2 2/20 COT

2 10 COTTON

2 2/40 COTTON

3 2/60 ERI

3 2/20 COT

3 10 COTTON

3 2/40 COTTON

4 2/20 COTTON

4 2/60 ERI

4 25 S ACRLYIC

I am not clear with your Method #10. can you explain clearly?

I have a serial numbers as A,B,C,D,…Z. If I delete one of the row, the series of A,B,C,D,E…Z should not be changed. it should again shows A,B,C,D,E,…..Z. please help…its urgent.

I WANT TO ADD SR NUMBERS AS 1(18-19) AND THAN 2(18-19) IN A COLUMN BUT BECAUSE OF () I AM NOT ABLE TO ADD SERIAL NUMBERS PLS HELP

You need to use ROW function and then add (18-19) as a test so if I have to insert serial number in cell A1 then the formula would be =ROW()&”(18-19)”

How to add Serial no. having figure more than 15 digits, like 89080068770002819

Give me a better example. what do you mean by 15 digits? 🙂

what the formula for method 10

awesome work !

excel

I often use MAX to achieve automated numbering, with cell formatted as a number ending in decimal point (“#,##0.”), with a dependency on the cell to the right having a value in it.

For example

With Row 1 having column headings, I would have the following formula in call A2 –

=if( $B2 = “” , “” , max( $A$1:$A1) + 1

which can then be dragged / copied down to the bottom of the sheet or area you need it to apply to. Useful to apply step by step documentation of steps in a process, where blank lines between any steps do not get numbered.

I need to insert automatic serial no.s like :

TK001

TK002

TK003 ….and so on.

with condition that when there is some entry in the next coloum even then this serial no. has auto entered.

DID YOU GOT IT ? I AM SEARCHING FOR THE SAME

i want to get a series like 1,1,2,2,3,3,4,4,5,5,.. and so on. How to do that?

Sub AddSerialNumbers()

Dim i As Integer

On Error GoTo Last

i = InputBox(“Enter Value”, “Enter Serial Numbers”)

For i = 1 To i

ActiveCell.Value = i

ActiveCell.Offset(1, 0).Activate

ActiveCell.Value = i

ActiveCell.Offset(1, 0).Activate

Next i

Last:

Exit Sub

End Sub

Let’s say your sequence begins in cell A2.

In cell A2, enter a 1

In cell A3, enter this formula:

=IF(COUNTIF($A$2:A2,A2)=1,A2,A2+1)

Use the fill handle to drag that down as far as needed.

hii

i want to make auto fill column like 3 9 27 81

Multiply with three and drag down.

Its owesome Yaar

Thanks for your words. 🙂

I often use COUNTA(A$1:A1) to add serial number.for filering i’d love SUBTOTAL(3,B$1:B1)

Thanks for sharing.

The other day I learned the following:

– in Cell A1 type the number 1

– while holding down CTRL button, use Fill Handle on bottom right hand corner of cell A1 and drag down

– you will see the numbers filled in sequential order as you drag down

Thanks Derek

Well,

this is my favorite as it works with filtering:

=subtotal(3,a$1:a1)

Wow, MMA,

Thanks for sharing.

Do I miss something here? I have put the subtotal function in A1 and copied it down. What I get is 0 (zero) in all my cells. I use the norwegian version and the 3 points to COUNTA, is that correct?

formula should be =SUBTOTAL(3,B$1:B1)