How to Repeat Two Rows and Skip One Using Excel Formulas
Question details
The user needs to copy data from another worksheet in a specific pattern: grab two consecutive rows, skip the third row, and repeat this sequence continuously.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Copying and transforming worksheet data into a new structured layout using dynamic formulas.
- Observed behavior
- The user wants to automatically pull values in a two-on, one-off sequence without manually copying, pasting, and skipping rows.
Ensure your destination column has enough empty rows to accommodate the expanded pattern, and verify the exact name of your source worksheet before applying the formula.
Use the INDEX and ROW Functions to Create a Skipping Pattern
This method uses a mathematical combination of INDEX, ROW, and ROUND functions to dynamically calculate which rows to pull and which to skip as you drag the formula down.
By dynamically calculating row offsets, Excel can shift the lookup reference down an extra row at regular intervals. This completely automates complex layout changes.
Click on the cell where you want your new pattern sequence to begin (for example, cell B13).
Type the formula =INDEX('Smoky River'!A:A,ROW(A13)-1+ROUND((ROW(A13)-ROW(A$13)+1)/2,0)) into the formula bar. Replace 'Smoky River' with the actual name of your source sheet.
Press Enter to execute the formula, then click and drag the fill handle (the small square at the bottom-right of the cell) downwards to populate subsequent rows with the pattern.
Organize Complex Data Patterns Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced mathematical and lookup formulas like INDEX, ROW, and ROUND, allowing you to manipulate and reformat complex datasets with ease.
- 1. Open your Workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the source data you want to reformat.
- 2. Input the Pattern Formula: Select your target cell and paste the INDEX and ROW formula to establish the two-on, one-off sequence.
- 3. Drag to Fill: Use the fill handle at the bottom right of the active cell to drag down and instantly populate the rest of your column.
Frequently Asked Questions
Does this formula work if my starting row is not row 13?
Yes, but you must update the cell references in the formula. For example, if your first formula is placed in row 2, change every instance of A13 to A2 and A$13 to A$2 to maintain the correct mathematical offset.
Why am I getting a #REF! error when I drag the formula down?
A #REF! error usually occurs if the source sheet name is misspelled, if the sheet was deleted, or if the formula's calculation results in a row number that exceeds the maximum row limit of the spreadsheet.
Can I adapt this formula to skip two rows instead of one?
Yes, you can adjust the division and addition logic inside the ROUND and ROW functions to change the interval. However, for highly complex skips, using a helper column or the FILTER function combined with modular math (MOD) is often easier to manage.
