[excel] How to enter a series of numbers automatically in Excel

I have so many records but I need to enter serial numbers automatically in Excel 2007. I have hundreds of records. Instead of entering them manually I want them to show up automatically. Please give me short example for 1 to 10.

This question is related to excel excel-2007

The answer is


Simple Answer:

In A1

=if(b1="","",row()) 

In A2

=if(b2="","",row())  

copy this down the column. Each time an entry is entered in column B the next number in order will be entered in column A.

Suppose you want the answer of 2 in a4 cell because of your heading & other stuff, you can write as

=if(b4="","",row()-2)

Enter the formula =ROW() into any cell and that cell will show the row number as its value.

If you want 1001, 1002 etc just enter =1000+ROW()


I find it easier using this formula

=IF(B2<>"",TEXT(ROW(A1),"IR-0000"),"")

Need to paste this formula at A2, that means when you are encoding data at B cell the A cell will automatically input the serial code and when there's no data the cell will stay blank....you can change the "IR" to any first letter code you want to be placed in your row.

Hope it helps


Use formula =row(b2)-x, where x will adjust the entries so that the first S/No is marked as 1 and will increment with the rows.


=IF(B1<>"",COUNTA($B$1:B1)&".","")

Using formula

  1. Paste the above formula in column A or where you need to have the serial no
  2. When the second column (For Example, when B column is filled the serial no will be automatically generated in column A).

If you want to pick cell entries from a list then you have a couple of non-code based options

I would recommend The Data Validation approach where

  • creating a list of your 100 records in a single column,
  • provide a range name to this list,
  • then using Data Validation's List option

sample from Debra's site below, click on the first link above to access it.

Data Validation


I have a very simple answer. Just put this formula in cell A2:

=Max($A$1:A1)+1

and fill down.

This sets row number automatically, even if you remove some rows or sort rows.

Autosorting numbers of row


Enter the first in the series and select that cell, then series fill (HOME > Editing - Fill, Series..., Columns):

SO8615149 example

It is very fast and the results are values not formulae.