WPS Spreadsheets: Duplicate Each Excel Cell into Two Consecutive Rows
Question details
The user needs to repeat each value in one spreadsheet column twice in sequence in a new column.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A source range contains values that must be duplicated into consecutive rows for reporting, transformation, or further analysis.
- Observed behavior
- The user needs a formula-based result where each source value appears twice before the next source value begins.
Identify the source range, such as C1:C3, and choose an empty destination cell for the repeated results. Confirm that the destination area has enough empty rows for twice the number of source values.
Use a dynamic-array formula to repeat each value twice
Use this option when your WPS Spreadsheets version supports dynamic-array functions such as TOCOL, HSTACK, or CHOOSECOLS.
The formula creates two copies of the source column and converts them into one vertical list, so each value appears twice consecutively.
Click the empty cell where the repeated list should begin, such as D1.
Type =TOCOL(HSTACK(C1:C3,C1:C3)) in the formula bar, replacing C1:C3 with your actual source range, and press Enter.
Check the cells below the formula. The values from C1:C3 should appear as C1, C1, C2, C2, C3, and C3.
Fill down an INDEX formula for older spreadsheet versions
Use this option when dynamic-array functions are unavailable or when you need a formula that can be copied down manually.
Repeat Spreadsheet Values Twice with WPS Spreadsheets
WPS Spreadsheets lets you create a repeated vertical list with dynamic-array formulas or use a traditional INDEX formula when dynamic arrays are unavailable. Enter the formula in an empty destination cell, adjust the source range, and review or fill the results as needed.
- 1. Open a spreadsheet: Launch WPS Office, open your spreadsheet, and identify the source column.
- 2. Choose an empty output cell: Click an unused cell where the duplicated list should begin.
- 3. Enter the appropriate formula: Use =TOCOL(HSTACK(C1:C3,C1:C3)) for dynamic arrays, or use the INDEX formula for older versions.
- 4. Confirm the repeated list: Press Enter and verify that each source value appears twice consecutively.

Frequently Asked Questions
How can I duplicate every value in a column twice in WPS Spreadsheets?
In an empty destination cell, enter =TOCOL(HSTACK(C1:C3,C1:C3)), replacing C1:C3 with your source range. Press Enter to spill each value twice in sequence.
What formula works when TOCOL is not available?
Use =INDEX($C$2:$C$9,INT((ROWS($A$1:A1)-1)/2)+1) in the first destination cell, adjust the source range, and fill the formula down for twice the number of source rows.
Can I use CHOOSECOLS to repeat an Excel column?
Yes. If supported by your WPS Spreadsheets version, enter =TOCOL(CHOOSECOLS(C1:C3,{1,1})) in an empty cell to return two copies of each value consecutively.
Where should the duplication formula be entered?
Enter it in an empty cell outside the original source range, with enough blank cells below it for the complete output. A source range with three values requires six output rows.




