Function Problems

How to Repeat Each Source Value in Two Consecutive Excel Rows

Jason MillerJason Miller Nov 10, 2026 868 views

Question details

The user needs to duplicate each sequential value from a source column into two consecutive rows as a formula is filled down across thousands of rows.

How to Repeat Each Source Value in Two Consecutive Excel Rows
Product
Spreadsheets (Excel/WPS Spreadsheet)
Device & OS
not provided
Scenario
Restructuring a large dataset where every item from a fixed source list needs to appear exactly twice in a new paired-row layout.
Observed behavior
A formula is required to automatically duplicate the source values into consecutive rows without having to manually copy and paste each item twice.
Before you start

Identify the exact starting row of your target output and ensure your source data range is locked using absolute references (such as $J$2:$J$10000) so it does not shift when dragging.

Solution 1Recommended

Use the INDEX and QUOTIENT Formula

The most efficient way to extract and repeat values from a fixed column using the INDEX function combined with row mathematics.

This formula uses the ROW and QUOTIENT functions to mathematically calculate which row of the source data to fetch. It is designed to return the 1st value twice, then the 3rd value twice (skipping the 2nd), following the specific pattern requested in the original scenario.

1
Select the target cell

Click on the first cell in your target range where you want the repeated values to start appearing.

2
Input the INDEX formula

Enter the formula: =INDEX($J$2:$J$10000,2*QUOTIENT(ROW(J2),2)-1). Be sure to change $J$2:$J$10000 to match the actual column where your source data resides.

3
Fill down the formula

Press Enter to apply the formula. Then, click the small square at the bottom-right corner of the cell and drag it down to fill the formula across your thousands of rows.

Use the INDEX and QUOTIENT Formula
Adjusting the Row Reference: The ROW(J2) part of the formula dictates the starting calculation. Ensure this references the correct starting row matching your data layout to maintain the 2-row repetition pattern.
Efficiently Process Large Datasets

Repeat Data Instantly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas, INDEX, and INDIRECT functions to handle massive datasets with ease. Duplicate values and organize your rows efficiently without performance lag.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your source data.
  2. 2. Apply the repetition formula: Select your target cell and input your preferred INDEX or INDIRECT formula.
  3. 3. Auto-fill thousands of rows: Simply double-click the fill handle at the bottom-right corner of the active cell to instantly auto-fill the formula down to the end of your adjacent data.
100% compatible with Microsoft Excel formulas and functions.Smooth and lag-free performance when dragging formulas across thousands of rows.Free built-in data manipulation tools to simplify complex spreadsheet restructuring.
QA img-9

Frequently Asked Questions

Can I repeat the source values three times instead of two?

Yes. To repeat values three times, you need to adjust the divisor in the QUOTIENT or INT function from 2 to 3, and update the multiplier accordingly so the row index increments only every third row.

Why is my formula returning a #REF! error?

A #REF! error usually occurs if the INDIRECT formula is pointing to an invalid cell address. Double-check your column offsets, such as COLUMN(C5)-1, to ensure it resolves to a valid column index greater than 0.

How do I reference a source range on a different worksheet?

You can add the sheet name directly into your formula. For example, with the INDEX function, you would write it as =INDEX('Sheet2'!$J$2:$J$10000, ...) to pull the repeated values from Sheet2.

Why does the INDEX formula skip every other value?

The specific math provided in the accepted formula (2*QUOTIENT(ROW(J2),2)-1) is designed to evaluate to 1, 1, 3, 3, 5, 5. This pulls the 1st value twice, then the 3rd value twice. If you want a sequential 1, 1, 2, 2, 3, 3 pattern, an alternative INT formula like INT((ROW(A1)-1)/2)+1 inside the INDEX function is usually recommended.