How to Repeat a Number Sequence in Excel: Step-by-Step Guide
Repeating a number sequence in Excel—such as 1, 2, 3, 4, 1, 2, 3, 4—can be incredibly useful for organizing data, creating structured patterns, and automating repetitive tasks. Whether you're handling small datasets or large tables, Excel offers several efficient methods to repeat number sequences with ease. This article explains several practical ways to create repeating patterns in your spreadsheets and highlights their best-use scenarios, advantages, and limitations. You will also find solutions for custom sequence repeats, including both built-in Excel offerings and VBA code methods to meet diverse and advanced needs.
- Repeat number sequence with Fill Handle
- Repeat number sequence with MOD function
- Repeat number or text string sequence with a powerful feature
- Repeat number sequence with SEQUENCE function (Excel365/Excel2021）
- Repeat custom sequence with INDEX formula (for text or custom order)
- Automate sequence repetition with VBA code
Repeat number sequence with Fill Handle
The Fill Handle is one of Excel’s most intuitive tools for creating patterns and repeating sequences. It enables users to replicate recurring values in just a few clicks and works well for relatively short or visually manageable sequences. This method is especially handy when you need to quickly create repeating lists without complex formulas or when demonstrating patterns for data entry and templates.
- Enter the sequence you want to repeat in a few cells. For example, type 1, 2, 3, 4 in cells A1 to A4.
- Select the cells containing your sequence. Move your mouse cursor to the bottom-right corner of the selection (the small square), allowing it to turn into a small, black cross.
- Hold down the Ctrl key then drag the Fill Handle down (or across) to fill more cells. This creates seamless repetition of the selected pattern without further configuration.
Applicable Scenarios: The Fill Handle is great for quick, one-time setup of short, visible patterns. It's particularly practical when you only need to repeat the sequence a few times or when working interactively and visually with your data.
Limitations: For large datasets or when an automated, formula-driven approach is required, manual use of the Fill Handle becomes less efficient and can introduce inconsistencies or errors in long lists.
Repeat number sequence with MOD function
The MOD function is a flexible way to create recurring sequences, especially beneficial when you want to automate repetition over a large dataset. It allows for precise, formula-based repetition that can easily be adjusted in length, pattern, and starting point. This approach fits especially well for consistent, stepwise sequences like 1, 2, 3, 4 repeatedly down a column.
Enter or copy the below formula into a blank cell where you want to insert the number sequence, then, drag the formula down to fill the column with sequence numbers. See screenshot:
=MOD(ROW()-1,4)+1- ROW()-1 gives a running count starting from 0, as Excel row 1 minus 1 yields 0.
- MOD(...,4) "wraps" the count back to 0 every 4 rows, causing the sequence to repeat after every 4 values.
- +1 shifts the 0-based result so your sequence starts from 1 instead of 0 (producing 1–4 instead of 0–3).
- The formula is ideal for repeating linear numbers (like 1–4) and cannot directly handle non-linear, custom, or text-based sequences (e.g., skipping numbers or mixing letters and numbers).
- If your sequence doesn’t start on row 1, you need to adjust the formula (for example, change ROW()-1 to ROW()-x, where x adjusts the starting number) to keep the intended pattern aligned.
- The sequence repeats as far as you copy the formula—if you need a fixed number of repetitions or to stop at a certain value, you’ll have to manually restrict the copied range or add extra controls.
Repeat number or text string sequence with a powerful feature
Repeating a number or text sequence in Excel can be tedious—especially when working with large datasets or non-standard patterns. While formulas like MOD, or SEQUENCE are helpful, they can be time-consuming and require specialized formula understanding. Here, Kutools for Excel streamlines the process. With its user-friendly interface, Kutools enables you to repeat number or text sequences—linear or customized—in just a few steps, without writing formulas or VBA code, and allows direct use on extensive data ranges for maximum efficiency.
After installing Kutools for Excel, please do with the following steps:
- Select the column or cell range where you want to insert the repeated sequence. This can be any range, no requirement for starting cell or column.
- Then, click Kutools > Insert > Insert Sequence Number. See screenshot:
- In the dialog box, specify the Fill order, and then, click New button to create a custom sequence.
- In the following dialog box, configure the number sequence as you need:
- Enter a name for your custom sequence list. This makes it easy to manage and reuse in the future.
- Specify the Start Number, End Number, Increment, and Number of Digits to fully customize the repeated numbers.
- You can also define a Prefix and/or Suffix for additional structure (for example, “Q1-”, “SFX”).
- Click OK to save your settings.
- When it returns to the previous dialog box, click OK or Apply. The specified pattern will fill your selected range immediately, and you can see a preview before confirming. See illustration:
Tips and Notices: With Kutools you can generate both numeric and text sequences, control repeating intervals, and easily handle merged ranges or discontinuous selections.
When to use: Ideal when you want to customize both simple and complex repeating patterns (including mix of numbers and text), repeat large amounts of data reliably, or automate sequence insertion without building formulas or code.
Limitations: Kutools requires an additional installation, but offers substantial flexibility for users who frequently manipulate large or uniquely patterned sequences.
Repeat number sequence with SEQUENCE function (Excel 365/Excel 2021）
If you're using a modern version of Excel (Excel 365 or Excel 2021), the SEQUENCE function introduces dynamic array handling that allows effortless pattern creation across columns or rows. It is particularly effective for creating basic repeating numeric sequences that fill large ranges instantly.
Enter or copy the following formula into a blank cell (e.g., A1) where you want to insert the number sequence, then press Enter. Excel will automatically spill the sequence down as many rows as specified:
=MOD(SEQUENCE(20,1,0),4)+1- SEQUENCE(20,1,0): Generates a vertical series of numbers, here from 0 to 19—20 rows in total.
- MOD(...,4): Divides each result by 4 and returns the remainder, creating a looping effect.
- =MOD(SEQUENCE(20,1,0),4)+1: Shifts the sequence so numbers repeat between 1 and 4. Adjust 20 and 4 for different sequence lengths and repeat ranges.
If you want a longer or shorter repeating sequence, adjust the '20' in SEQUENCE. For a different range to repeat (e.g., 1 to 7), change the '4' to your new range's size.
- Only Works in Excel 365 / 2021: Earlier Excel versions do not support SEQUENCE or dynamic array behavior.
- Numeric Output Only: This formula is designed for numbers. To repeat custom text or mixed value sequences, see the INDEX formula solution below.
- Linear Pattern Only: The pattern produced is always sequential. Non-standard, irregular, or custom-patterned repeats need a different approach (e.g., INDEX formula).
Repeat custom sequence with INDEX formula (for text or custom order)
Excel's INDEX function provides significant flexibility for repeating custom sequences—including both numbers and text, or a specific, user-defined order. Unlike MOD formulas that are limited to evenly stepped numeric patterns, the INDEX formula can repeat any list, regardless of content or order, making it a versatile solution for mixed, non-linear, or text-based sequences.
This approach is especially valuable when you need to repeat non-standard patterns such as 1, 5, 9 or custom text labels like A, B, C, or mixed values.
1. In a blank cell (e.g., B1), enter the following formula to repeat a text sequence such as A, B, C down your dataset:
=INDEX({"A","B","C"},MOD(ROW()-1,3)+1)2. Press Enter to confirm the formula, then drag the fill handle down to apply the pattern to additional rows.
How it works: The array {"A","B","C"} holds your custom sequence—these can be numbers, text, or any values you need. MOD(ROW()-1,3)+1 cycles through the array: as you copy the formula down, the index number repeats and thus the sequence recurs.
Customizing: To use a different pattern (e.g., {5, 10, 15, 20}), adjust the array:
=INDEX({5,10,15,20},MOD(ROW()-1,4)+1)Tips: For sequences stored in range cells (say D1:D3), use INDEX($D$1:$D$3,MOD(ROW()-1,3)+1)—making it easy to update or expand the repeating list without editing the formula.
Advantages: INDEX overcomes limits of MOD and SEQUENCE formulas by supporting mixed-type sequences and any custom order, making it suitable for advanced, irregular, and text-based repeats.
Possible issues: If your formula always returns the same value, check absolute/relative references and make sure your row calculations align with where your formula starts.
Automate sequence repetition with VBA code
For advanced scenarios or whenever patterns are highly complex, generated programmatically, or need more automation than spreadsheet formulas can easily deliver, VBA (Visual Basic for Applications) lets you dynamically construct and fill any repeat pattern you can describe. This approach is highly useful when:
- The sequence is generated by rules, functions, or fetched dynamically (not known in advance).
- The repeat count, block size, or sequence length varies or is user-input driven.
- Your sequence includes complex logic, conditional repetition, or needs automatic range expansion.
Operation steps:
1. In Excel, click Developer Tools > Visual Basic to open the VBA editor. In the VBA window, click Insert > Module, and paste the following code into the Module:
Sub RepeatCustomSequence()
Dim seqArr As Variant
Dim repeats As Long
Dim outRange As Range
Dim i As Long, n As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
' Prompt for sequence to repeat (as comma-separated values)
Dim seqInput As String
seqInput = Application.InputBox("Enter your sequence to repeat (comma separated):", xTitleId, "A,B,C")
If seqInput = "" Then Exit Sub
seqArr = Split(seqInput, ",")
repeats = Application.InputBox("How many repeats?", xTitleId, 10, Type:=1)
Set outRange = Application.InputBox("Select top-left cell of output range", xTitleId, Type:=8)
n = UBound(seqArr) - LBound(seqArr) + 1
For i = 0 To repeats - 1
outRange.Offset(i, 0).Value = seqArr((i Mod n) + LBound(seqArr))
Next i
End Sub2. In the VBA editor, click the 
Run button, or press F5 to execute. Enter the custom sequence you want to repeat (separated by commas, such as A, B, C, or 5, 10, 15). Specify the number of repeats and the first output cell when prompted. The VBA script will output your repeated pattern directly to your sheet.
Customizing: This VBA method can be further adapted—for example, to write repeating blocks horizontally, prompt for intervals, or support more complex sequence logic. Simply modify the output range logic or the way the sequence is cycled.
Troubleshooting: If nothing appears, double-check macro security settings in Excel, and ensure the provided sequence and count are valid. Always save your workbook before running new code to protect existing data.
✅ Conclusion
Repeating number or text sequences in Excel can be accomplished in several efficient ways, depending on your needs and Excel version.
- The Fill Handle is perfect for quick, manual patterns and is easy to use for short sequences or basic visual layouts.
- The MOD function offers a dynamic, formula-driven solution for repeating evenly spaced numbers—the best choice for linear numeric repetition.
- For more flexibility and ease—especially for custom or mixed text/number sequences—Kutools for Excel provides a user-friendly interface and robust sequence handling without complex setup.
- Excel's SEQUENCE function is ideal for rapid, formula-only generation of linear numeric repeats in modern Excel versions.
- For non-linear, custom, or text pattern repeats, the INDEX formula method offers unmatched control and can accommodate any pattern specified in an array or range, surpassing the limits of numeric-only or stepwise repetition.
- When ultimate flexibility or dynamic patterning is needed—including generated, highly complex, or runtime-determined sequences—VBA automation brings advanced power and is suitable for professional and technical tasks where built-in tools fall short.
Choose the method that best suits your workflow and sequence pattern requirements to boost productivity and streamline your repetitive data tasks. Don't forget to explore our resources for more Excel solutions and strategies for every skill level: our website offers thousands of tutorials to help you master Excel.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in