How to Repeat Each Source Value in Two Consecutive Excel Rows
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.

- 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.
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.
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.
Click on the first cell in your target range where you want the repeated values to start appearing.
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.
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 INDIRECT, ADDRESS, and INT Formula
A dynamic approach that calculates the source cell address based on the current row and column offsets, useful when the target and source are side-by-side.
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. Open your workbook: Launch WPS Spreadsheet and open the file containing your source data.
- 2. Apply the repetition formula: Select your target cell and input your preferred INDEX or INDIRECT formula.
- 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.

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.




