How to Fix Excel AutoFill Not Recognizing Every Fourth Cell Reference
Question details
The user needs to extract values from every fourth cell in a column using a drag-down AutoFill pattern, but standard cell references fail to increment correctly.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Trying to pull data at regular intervals (e.g., every 4 rows) from a column to condense or reorganize information.
- Observed behavior
- Excel AutoFill fails to recognize the interval pattern (e.g., =C2, =C6, =C10) and does not output the expected sequential skipped cell references when dragged down.
Identify the exact starting row of your target data and the row number where you are placing your new formula, as the mathematical calculation depends on these starting coordinates.
Use INDIRECT and ROW Functions to Calculate Row Intervals
Bypass AutoFill's limitations by creating a dynamic formula that mathematically calculates every fourth row.
Excel's AutoFill does not understand skip patterns for cell references directly. Using the INDIRECT function combined with ROW allows you to mathematically calculate the row numbers you need (e.g., 2, 6, 10, 14) and convert them into an actual cell reference.
Click on the first empty cell where you want the extracted data to appear (for example, in a new summary column).
Type the formula `=INDIRECT("C"&((ROW()-2)*4+2))` into the formula bar. In this example, 'C' is the target column, the first '2' matches the row where you are typing the formula, '4' is the interval, and the second '2' is the starting row of your target data.
Modify the formula if your data starts elsewhere. If you are typing this formula in row 1, change `ROW()-2` to `ROW()-1`.
Press Enter, then click and drag the small square at the bottom-right corner of the cell downwards to automatically pull every fourth value.
Extract Every Nth Row Using the OFFSET Function
Another dynamic approach using OFFSET to define a starting point and move down a specific number of rows based on the current row.
Easily Extract Every Nth Cell with WPS Office
WPS Spreadsheet fully supports advanced mathematical referencing functions like INDIRECT, OFFSET, and ROW, making it incredibly simple to handle complex data extraction and interval patterns without manual copying and pasting.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing the data you want to extract.
- 2. Select your destination: Click the empty cell where you want the first interval value to be displayed.
- 3. Input the dynamic formula: Type `=INDIRECT("C"&((ROW()-2)*4+2))` and press Enter.
- 4. Use AutoFill: Hover over the bottom-right corner of the cell until the cross cursor appears, then drag down to instantly fill the column with every fourth value.
Frequently Asked Questions
Why doesn't Excel AutoFill recognize the =C2, =C6 pattern?
Excel AutoFill is designed to increment cell references strictly by 1 row or column when dragged. It can recognize linear number patterns (like 1, 3, 5) but it does not natively calculate skip patterns for actual cell references (like intervals of 4), which is why dynamic formulas are required.
How do I change the formula to extract every 5th row instead?
To change the interval, simply update the multiplier in the formula. For example, replace the `*4` with `*5` in the expression: `=INDIRECT("C"&((ROW()-2)*5+2))`.
Does this INDIRECT formula work horizontally across columns?
If you are dragging horizontally across columns rather than down rows, you need to use the COLUMN() function instead of ROW() to ensure the mathematical multiplier increments correctly from left to right.
