How to Repeat Each List Item Seven Times in Excel Formulas
Question details
The user needs to expand a variable list so that every single item from the original column appears consecutively a set number of times (e.g., seven times) in a new column.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Expanding a dataset for analysis or formatting where individual entries must be duplicated sequentially in rows.
- Observed behavior
- The goal is to automatically generate a new column where Item 1 populates rows 1-7, Item 2 populates rows 8-14, and so on.
Ensure your source list in column A is contiguous, and decide exactly how many times you want each item to repeat to adjust the divisor in the formula accordingly.
Use the INDEX and ROUNDUP Formula (Recommended)
This method uses the INDEX function combined with ROUNDUP and ROWS to mathematically dictate how many times a cell reference is repeated before moving to the next.
This is the most efficient and robust way to duplicate items sequentially. The ROWS function counts the output rows, and dividing by your desired repetition number (e.g., 7) tells INDEX when to jump to the next item.
Click on the cell in a new column where you want the repeated list to start, for example, cell C2.
Type the formula: =INDEX(A:A,ROUNDUP(ROWS($A$2:A2)/7,0)+1) into the formula bar.
Press Enter, then click the fill handle at the bottom-right corner of cell C2 and drag it down to expand your list as far as needed.

Use IF, INDEX, and INT to Handle Empty Cells
If your list contains blank cells at the end, standard INDEX functions will return zeroes. This solution adds an IF condition to leave output cells blank.
Easily Manage and Expand Data Lists in WPS Office
WPS Spreadsheet fully supports advanced array formulas, including INDEX, MATCH, and complex ROW logic. You can accomplish data expansion and formatting tasks smoothly, with an interface designed to boost your productivity.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your original data list.
- 2. Enter the INDEX formula: Select the blank column where you want the expanded data, and paste the INDEX formula exactly as you would in Excel.
- 3. Auto-fill the rest: Double-click the fill handle to instantly populate the repeated rows down the entire column.

Frequently Asked Questions
How can I change the formula to repeat items 5 times instead of 7?
Simply modify the divisor in the formula. For example, change /7 to /5: `=INDEX(A:A,ROUNDUP(ROWS($A$2:A2)/5,0)+1)`. The number dictates how many rows are generated before moving to the next source item.
Why is my formula returning a '0' for empty cells?
The standard INDEX function returns a 0 when it references a blank cell. You can fix this by wrapping the formula in an IF statement or by appending `&""` to the end of your formula to force it to output a blank string instead of a zero.
Can I repeat list items across columns horizontally instead of vertically?
Yes. Instead of using the ROWS() or ROW() functions, you can substitute them with COLUMNS() or COLUMN() functions within your ROUNDUP formula, which allows you to drag the data horizontally across columns.




