Function Problems

How to Repeat Each List Item Seven Times in Excel Formulas

Jason MillerJason Miller Nov 2, 2026 870 views

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.

How to Repeat Each List Item Seven Times Using Excel Formulas
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the target cell

Click on the cell in a new column where you want the repeated list to start, for example, cell C2.

2
Input the formula

Type the formula: =INDEX(A:A,ROUNDUP(ROWS($A$2:A2)/7,0)+1) into the formula bar.

3
Fill down the column

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 the INDEX and ROUNDUP Formula (Recommended)
Modifying the repeat count: If your source data starts on a different row or you need a different repeat count, simply change the '7' in the formula to your target number.
WPS Spreadsheet Data Manipulation

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. 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your original data list.
  2. 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. 3. Auto-fill the rest: Double-click the fill handle to instantly populate the repeated rows down the entire column.
100% compatibility with Microsoft Excel formulas and .xlsx filesHigh-performance calculation engine for large datasetsFree, lightweight, and incredibly fast to install
QA img-9

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.