How to Automatically Repeat a Number Series in Excel
Question details
The user wants to generate a repeating sequence of numbers (e.g., 1 to 10) down a column for a specific number of rows using an automated formula.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating patterned datasets or automating sequential data entry in a spreadsheet without manually typing or copying cells.
- Observed behavior
- Requires a formula-based approach to spill or fill a repeating number sequence dynamically based on defined parameters.
Determine which version of Excel you are using. Dynamic array functions like SEQUENCE are only available in Microsoft 365 and Excel 2021 or newer; older versions will require traditional cell referencing.
Use the SEQUENCE and MOD Functions (Microsoft 365 & Excel 2021)
This is the most efficient method for newer Excel versions, utilizing dynamic arrays to instantly spill the repeated sequence down your chosen number of rows.
The SEQUENCE function generates a straight list of numbers (e.g., 1 to 100). By subtracting 1, applying the MOD function with a divisor of 10, and adding 1 back to the result, the list is forced to cycle through the numbers 1 to 10 repeatedly.
Click on the cell where you want the repeating sequence to begin, for example, cell A1.
Type the formula =MOD(SEQUENCE(100)-1,10)+1 into the formula bar. In this example, '100' is the total number of rows to fill, and '10' is the highest number in your repeating sequence.
Press the Enter key. The formula will automatically 'spill' down the column, creating a 1 through 10 sequence repeated until it reaches 100 rows.
Use a Standard MOD Formula (For Older Excel Versions)
If you are using Excel 2019 or older, dynamic arrays are not supported. You can use a traditional approach referencing the previous cell to create the repeating pattern.
Easily Generate and Repeat Number Series in WPS Spreadsheet
WPS Office offers a fully featured, free spreadsheet application that flawlessly supports advanced Excel formulas. You can easily automate your data entry using the exact same MOD and SEQUENCE formulas without needing an expensive subscription.
- 1. Download and install WPS Office: Get WPS Office for free from the official website and complete the quick installation process.
- 2. Open WPS Spreadsheet: Launch the application and open a blank workbook or your existing spreadsheet file.
- 3. Select your target cell: Click on the cell where you want the automated number sequence to start.
- 4. Enter the repeating formula: Type the formula =MOD(SEQUENCE(100)-1,10)+1 and press Enter to instantly spill the data into your worksheet.
Frequently Asked Questions
Can I change the repeating sequence to a different range, like 1 to 5?
Yes. To change the maximum number of your sequence, adjust the divisor in the MOD function. For a 1 to 5 sequence covering 100 rows, update the formula to: =MOD(SEQUENCE(100)-1,5)+1.
Why is the SEQUENCE function giving me a #NAME? error?
The #NAME? error occurs when Excel does not recognize a function. The SEQUENCE function is only available in Microsoft 365, Excel 2021, and newer versions. If you are on an older version, you must use the standard MOD method referencing the cell above.
How do I repeat text strings instead of numbers in Excel?
To cycle through text values (like 'Red', 'Blue', 'Green'), you can combine the INDEX function with the repeating number sequence. For example: =INDEX({"Red","Blue","Green"}, MOD(SEQUENCE(100)-1, 3)+1).
How do I repeat a number sequence horizontally across columns?
The SEQUENCE function can spill data into columns instead of rows. To repeat numbers 1 to 10 across 100 columns, modify the SEQUENCE arguments to define rows then columns: =MOD(SEQUENCE(1, 100)-1, 10)+1.
