Share via

Autofill a REPEATING series of numbers in Excel

Anonymous
2024-01-25T20:21:03+00:00

I have been getting on fine with using AutoFill in Excel using the 'fill handle', up to a point...

The problem I have is I can't figure out how to AutoFill a Series of REPEATING numbers (e.g. 1 to 10) in a Column in Excel - such as A1 to A10, then repeating in A11 to A20 and so on?

I tried the 'Fill' option in the Ribbon - Home>Fill>Series (Columns-Linear)-Step Value:1/Stop Value:10 - but that only fills in one 'set' of 1 to 10... how do I get it to repeat either a set number of times, or until a specific Cell, e.g. A100 ?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2024-01-25T20:36:26+00:00

    If you have Microsoft 365 or Office 2021, enter the following formula in A1:

    =MOD(SEQUENCE(100)-1,10)+1

    It will spill to A1:A100.

    In older versions:

    Enter 1 in A1.

    In A2, enter the formula =MOD(A1, 10)+1

    Fill down to A100.

    6 people found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2024-01-25T22:51:19+00:00

    SEQUENCE(100) generates the numbers

    1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, ..., 100

    SEQUENCE(100)-1 returns

    0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, .., 99.

    MOD(..., 10) calculates the remainder of these numbers after dividing them by 10:

    0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, 1, 2, 3, ..., 9

    Finally, we add 1 to this:

    1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 1, 2, 3, 4, ..., 10

    1 person found this answer helpful.
    0 comments
  2. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2024-01-25T23:29:23+00:00

    Hi,

    Enter this formula in cell A1

    =LET(seq,SEQUENCE(10),INDEX(seq,MOD(SEQUENCE(2*COUNTA(seq),,0),10)+1))

    Hope this helps.

    0 comments
  3. Anonymous
    2024-01-25T21:49:40+00:00

    Thanks @HansV -

    Having researched both the MOD & SEQUENCE functions in your answer, I have no idea how it works but it does...!

    0 comments