Function Problems

WPS Spreadsheets: Duplicate Each Excel Cell into Two Consecutive Rows

Khadija KhanKhadija Khan Nov 12, 2026 869 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Select the destination cell

Click the empty cell where the repeated list should begin, such as D1.

2
Enter the dynamic-array formula

Type =TOCOL(HSTACK(C1:C3,C1:C3)) in the formula bar, replacing C1:C3 with your actual source range, and press Enter.

3
Review the spilled results

Check the cells below the formula. The values from C1:C3 should appear as C1, C1, C2, C2, C3, and C3.

Alternative dynamic-array formula: You can also enter =TOCOL(CHOOSECOLS(C1:C3,{1,1})) to return each source value twice in sequence.

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. 1. Open a spreadsheet: Launch WPS Office, open your spreadsheet, and identify the source column.
  2. 2. Choose an empty output cell: Click an unused cell where the duplicated list should begin.
  3. 3. Enter the appropriate formula: Use =TOCOL(HSTACK(C1:C3,C1:C3)) for dynamic arrays, or use the INDEX formula for older versions.
  4. 4. Confirm the repeated list: Press Enter and verify that each source value appears twice consecutively.
Repeat every source value twice without manually copying rowsUse dynamic-array formulas or a compatible fill-down methodWork in a familiar spreadsheet interface with quick formula entryOpen and edit Microsoft Excel XLSX files with broad format compatibility
microsoft office alternative - wps office

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.