Function Problems

How to Duplicate Every Row Twice in a Large Excel Dataset

John Wilson Sep 28, 2026 870 views

Question details

The user needs to duplicate every row exactly twice across a massive million-row Excel dataset.

How to Duplicate Every Row Twice in a Large Excel Dataset

Product: Excel
Device & OS: not provided
Scenario: Formatting and expanding a large dataset where each record and its associated values must be repeated twice consecutively.
Observed behavior: Manually copying rows is impractical for millions of entries, and a previously attempted formula resulted in a #NAME? error.

Before you start

Verify your current Excel version and regional settings, as advanced functions like SEQUENCE require Microsoft 365, and different regions may use semicolons instead of commas in formulas.

Solution 1Recommended

Use the INDEX and ROUNDUP Functions

A highly compatible formula method that works across all versions of Excel to duplicate rows without requiring Office 365.

This approach uses the INDEX function to retrieve the value and the ROUNDUP function with ROWS to calculate the exact duplicate pacing. It is perfect for users on older versions of Excel.

Select a destination cell

Click on the top cell of a new, blank column where you want the duplicated data to appear.

Enter the duplication formula

Type the formula =INDEX($A$2:$A$1000001,ROUNDUP(ROWS($1:1)/2,0)) into the formula bar. Replace $A$2:$A$1000001 with your actual data range.

Apply to the entire column

Press Enter, then click and hold the fill handle (the small square at the bottom-right of the cell) and drag it down to fill the rest of your dataset.

Repeat for additional columns

Modify the column reference in the formula (e.g., change A to B) and repeat the process to duplicate your value columns.

Performance Tip: After generating the duplicates, copy the new columns and paste them as 'Values' to remove the formulas. This will significantly improve spreadsheet performance on million-row files.

Solution 2

Use the SEQUENCE Function (Microsoft 365 Only)

A powerful dynamic array formula that instantly generates all duplicated rows and columns without manual dragging.

Solution 3

Troubleshoot the #NAME? Error

Fix syntax and compatibility issues if your duplication formula fails to execute properly.

Efficient Data Processing

Duplicate Rows Effortlessly with WPS Spreadsheet

WPS Office fully supports advanced array formulas, dynamic arrays, and functions like INDEX and ROUNDUP. It allows you to seamlessly process millions of rows without lagging, offering a fast and reliable environment for heavy data manipulation.

1. Open your dataset: Launch WPS Spreadsheet and open your massive dataset.
2. Enter the formula: In a blank column, enter the formula =INDEX(A:A, ROUNDUP(ROW(A1)/2, 0)).
3. Apply across rows: Press Enter, click the fill handle, and double-click or drag down to instantly duplicate the pattern across your dataset.

Smoothly handles datasets with up to 1,048,576 rowsFully compatible with Microsoft Excel formulas and functions (.xlsx)Free, lightweight, and easy to use for daily data tasks

microsoft office alternative - wps office

Frequently Asked Questions

How can I duplicate a row three or four times instead of two?

To duplicate rows more than twice, change the divisor in the formula. For example, use /3 instead of /2 in the ROUNDUP(ROWS($1:1)/2,0) segment to repeat each row exactly three times.

Why does my large dataset slow down after applying the formula?

Formulas applied across millions of rows consume significant processing power. Once your data is duplicated, highlight the new columns, copy them (Ctrl+C), right-click, and select 'Paste as Values' to remove the background calculations.

Does WPS Spreadsheet support the SEQUENCE function?

Yes, newer versions of WPS Office fully support dynamic array functions like SEQUENCE, allowing you to use Microsoft 365 style formulas seamlessly.

Excel & Data Problems