Lesson#152: Formula to repeat serial number

Hey fellas! I am going to discuss some tricks to make a formula to repeat serial numbers. You may think how? Yes, these are easy formulas. Let’s say how about a formula that will continuously repeat the number 5 times then increase by +1. Just like it is shown in this picture.

Now put the first number manually and from the second cell put a formula like this.

=IF(COUNTIF(A1:$A$1,A1)<5,A1,A1+1)

Drag the formula and see the magic.

Now, do you want to make the serial number limited to 1 to 5? Even how much longer you drag below the formula it will repeat 1 to 5 only. Just like it is shown in this picture.

Now put the first number manually and from the second cell put a formula like this.

=+IF(A1<5,A1+1,1)

Drag the formula and see the magic.

This is how to make a formula to repeat serial numbers.

I hope you liked these small tricks.

Subscribe to the channel Excelabcd to get excel related tutorial videos.