How to Autofill Formula References Every Fifth Row in Excel
Question details
The user needs to autofill formulas in Excel that pull data from cells located at regular five-row intervals (e.g., A1, A6, A11).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting or referencing data at regular, evenly spaced intervals in a worksheet rather than copying sequential cells.
- Observed behavior
- Excel's standard drag-and-fill method increments references sequentially by one row (A1, A2, A3) and does not natively follow a five-row skip pattern.
Identify the exact starting cell in your source data and the destination column where you want the extracted interval data to appear.
Use the INDEX and ROW Functions
This method dynamically calculates the correct row number by multiplying the current row difference by the desired interval.
The INDEX function is ideal for returning a value from a specific position within a range. By combining it with mathematical operations on the ROW function, you can force Excel to skip a specific number of rows as you drag the formula down.
Click on the cell (for example, D1) where you want the first referenced value (A1) to be displayed.
Type the formula =INDEX(A:A,5*(ROW(D1)-ROW($D$1))+1) into the formula bar. Assuming your source data is in column A, this sets up the starting point.
Press Enter to evaluate the formula. The cell will now display the value found in A1.
Click and hold the fill handle (the small square at the bottom-right corner of the cell) and drag it downwards to copy the formula. The references will automatically adjust to A6, A11, A16, etc.
Use the OFFSET and ROW Functions
Alternatively, you can use the OFFSET function to shift the cell reference starting from a static base cell.
Extract and Autofill Data Easily with WPS Office
WPS Spreadsheet supports all advanced mathematical and lookup functions, including INDEX, OFFSET, and ROW, allowing you to manipulate complex data intervals seamlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your dataset.
- 2. Select the target cell: Click on the blank cell where you want to start pulling interval data.
- 3. Enter the formula: Input the =INDEX(A:A,5*(ROW(D1)-ROW($D$1))+1) formula, adjusting the cell references as needed.
- 4. Fill the sequence: Use the crosshair cursor on the cell's bottom-right corner to drag and autofill the skipped-row sequence.
Frequently Asked Questions
Can I change the interval to every third row instead of the fifth?
Yes. In the formula =INDEX(A:A,5*(ROW(D1)-ROW($D$1))+1), simply change the multiplier number '5' to '3' to reference A1, A4, A7, etc.
Why does standard dragging not work for skipping rows in formulas?
Excel's default autofill feature handles relative references by incrementing them strictly sequentially by one row (A1, A2, A3). It does not natively recognize or project a custom row-skip pattern without mathematical logic provided by functions like INDEX or OFFSET.
Does this formula work if my destination cells do not start in row 1?
Yes, it works perfectly. The mathematical term (ROW(D1)-ROW($D$1)) evaluates to 0 on the first entry, regardless of the actual starting row number. Just ensure that the $D$1 reference matches the exact cell where you placed the very first formula.
