How to generate batch numbers in excel with auto increment feature

Open Excel Sheet

Enter 1 into A1

Then enter this formula into A2:

=IF(MOD(ROWS($A$1:A1),5)=0,A1+1,A1)

Then just drag the formula from A2 down

As you can see in my formula batch number is change after 5 rows and batch value is increase by +1.

You can change logic of formula based on your requirement. Like if you want batch number to be change after 10 or 100 counts, change the current value of 5 to 10 or 100. Batch number will change after defined number of rows.

Also if you want batch number to be increment by 1 or more then 1, change the value here "+1" in formula.

comment for more clarifications.

Ref : Click here 

Excel 2010 cursor changed to plus sign, now cannot click to widen cells need help

You have to enable it in Excel 2010

To enable drag and drop, select File > Options
Under Options, select Advanced
Under Editing Options, select "Enable fill handle and cell drag and drop"   Click OK

volla you are done for day