In this post we will see how to repeat a number sequence in excel.
To repeat a number sequence in excel we need to define what would be our seed and what would be the difference between two consecutive numbers.
Seed is nothing but the starting number of a sequence and difference is the difference between two consecutive numbers in a sequence.
Let’s under this with the help of an example suppose we need to have a sequence of 9,12,15,18 repeating in a column in excel so here the seed is 9 and the difference is 3 as 12-9=3 and 15-12=3 and so on.
The only caveat in the formula which I am going to explain is that it will work only for the sequence that has the fixed difference between any two consecutive numbers in the consequence, be it 3 or 5 or 10.
Below is the sequence that we want to create in excel with the help of formula.
As you could see, the original sequence is 6,9,12 and 15 and it has been repeated in the column.
We will use MOD function and ROW function in excel to repeat a sequence in excel.
Mod gives the remainder of a number when divided by the divisor and ROW gives the row number in excel.
Step 1
Enter the formula =6+3*MOD(ROW()-1,4) in cell A1 as we want the sequence to start from cell A1.
Step 2
Now copy and paste the formula down in excel till where you want this sequence to be repeated.
Here I have pasted it till row 12 as I want to repeat this sequence three times.
As you could see we have repeated a sequence in column in excel with the help of formula and saved ourselves a lot of manual copy pasting, you could paste it as values just in case.
Also, one thing to notice here is that we wanted to start the sequence from row number 1 and hence we have subtracted the 1 in the formula from ROW() formula, in case you wanted the sequence to start from row 2, you would subtract the number 2 from ROW() formula.
Hope this helped.
