How to Autofill Excel Formulas with Every Tenth Row from Another Sheet
Question details
The user needs to autofill a formula that extracts values from specific, evenly spaced intervals (rows 1, 11, 21, 31) across different sheets, but default dragging does not recognize this custom step pattern.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Pulling data at a uniform interval from a source worksheet into a destination worksheet for data summarization or formatting.
- Observed behavior
- Standard cell autofill fails to increment the row reference by 10 when dragging down, requiring a specific offset formula to calculate the correct target row.
Identify the exact starting cell in your destination sheet, as the formula relies on the current row number to calculate the correct 10-row interval from your source sheet.
Use the INDEX and ROW Functions (Universal Solution)
This method uses the ROW function multiplied by your desired interval to mathematically calculate which row INDEX should pull from.
This is the most compatible solution and works across all older and newer versions of spreadsheet applications.
Click on the first cell in your destination sheet where you want the extracted data to appear.
If your destination starts exactly in row 1, type `=INDEX(Sheet1!A:A,ROW()*10-9)` and press Enter.
If your series starts in a different cell, such as C5, use `=INDEX(Sheet1!A:A,(ROW()-ROW($C$5)+1)*10-9)` instead.
Select the cell containing the formula, click the small square at the bottom-right corner, and drag it down to fill the rest of your list.
Use INDEX with SEQUENCE for Dynamic Arrays
For newer software versions supporting dynamic arrays, you can generate the entire series from a single cell without dragging.
Use TAKE and WRAPROWS Functions
An alternative dynamic array method that reshapes the 1D source data into a 2D grid and extracts the first column.
Extract Every Nth Row Easily with WPS Office
WPS Spreadsheet fully supports advanced array functions and mathematical lookup formulas like INDEX, ROW, and SEQUENCE. You can seamlessly extract intervals from cross-sheet ranges with robust performance.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your source data.
- 2. Apply the Formula: Navigate to the destination sheet, select the starting cell, and input the `=INDEX(Sheet1!A:A,ROW()*10-9)` formula.
- 3. Drag to Autofill: Click the fill handle on the bottom right of the active cell and drag downward to extract the targeted rows instantly.
Frequently Asked Questions
How do I modify the formula to pull every 5th row instead of 10th?
To change the interval, adjust the multiplier and subtractor in the formula. For a 5-row interval starting at row 1, change the formula to `=INDEX(Sheet1!A:A,ROW()*5-4)`.
Why does standard autofill not work for skipping rows?
When dragging a direct reference like `=Sheet1!A1` down by one cell, the application natively increments the row reference by exactly 1 (becoming `=Sheet1!A2`). It cannot automatically deduce a custom geometric or arithmetic step pattern just by dragging.
Can I use this method to extract data from a different workbook entirely?
Yes. You must include the workbook name in brackets within the reference, such as `=INDEX([Data.xlsx]Sheet1!A:A,ROW()*10-9)`. Ensure the source workbook is open to prevent #REF! errors in some application versions.
Why am I getting a #REF! error when I drag the formula down?
A #REF! error typically occurs if the calculated row number exceeds the total number of rows available in the worksheet, or if the source sheet name ('Sheet1') is misspelled or has been changed without updating the formula.
