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