How to Use SEQUENCE or REPT to Repeat Values in a Column in Excel
Question details
The user wants to repeat a cell's value across multiple rows in a single column.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Generating multiple rows of repeated data from a single input value for data structuring or automated list creation.
- Observed behavior
- The standard REPT function combines repeated text into a single cell, whereas the goal is to output an array of repeated values spilled down multiple rows using dynamic array formulas.
Ensure you are using a version of Excel that supports Dynamic Arrays (such as Microsoft 365 or Excel 2021), as the SEQUENCE function is required for spilling results.
Use SEQUENCE and T Functions to Spill Repeated Values
This method creates a dynamic array that automatically repeats the desired text down multiple cells in a column without manual copying.
Because the REPT function outputs a single text string, it cannot be used alone to distribute data across multiple rows. By combining the target cell with the T and SEQUENCE functions, you can force Excel to evaluate the output as a dynamic array.
Click on the first empty cell in the column where you want your repeated values to start (for example, B2).
In the formula bar, type `=A2&T(SEQUENCE(3))`. Replace A2 with the cell reference of the text you want to repeat, and change 3 to the exact number of rows you want to generate.
Press the Enter key. The formula will evaluate and automatically spill the repeated text down the specified number of rows in the column.
Use the REPT Function for In-Cell Repetition
Use this classic approach if you actually need the repeated text to stay grouped together inside a single cell rather than spreading down a column.
Use WPS Spreadsheet to Handle Dynamic Arrays
WPS Office Spreadsheet fully supports advanced mathematical functions and dynamic arrays, making it incredibly easy to manage, format, and generate complex data lists without expensive subscriptions.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and select the target cell for your repeated list.
- 2. Input the array formula: Enter the dynamic array formula `=A1&T(SEQUENCE(3))` into the top formula bar.
- 3. Generate the list: Press Enter to instantly generate and spill the list of repeated values down the column.
Frequently Asked Questions
Why is my SEQUENCE formula returning a #SPILL! error?
A #SPILL! error occurs when the dynamic array formula does not have enough empty cells below it to display all the results. Ensure that the cells directly below your formula are completely blank and free of hidden spaces.
What does the T function do in this specific formula?
The T function evaluates whether a value is text. In the formula `=A1&T(SEQUENCE(3))`, it converts the numerical array generated by SEQUENCE into an array of empty text strings. This allows the target text to be appended seamlessly without altering the original value.
Can I use the REPT function to repeat values across multiple rows?
No, the standard REPT function is specifically designed to repeat a text string multiple times within one single cell. To spill results down multiple cells in a column, you must rely on dynamic array functions like SEQUENCE.
