How to Copy Excel Formulas with References That Skip Rows
Question details
The user needs to copy a formula across cells where the row references skip by multiple rows (e.g., 3 rows) instead of the default one-row increment.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Dragging and copying formulas where source data records are grouped in blocks that span multiple rows.
- Observed behavior
- When dragging a formula normally, the spreadsheet software increments the cell references by only one row, causing calculations for multi-row data blocks to pull from incorrect cells.
Verify the exact structure of your source data. Ensure that every data record consistently spans the exact same number of rows (e.g., exactly 3 rows per record) so that the mathematical row multiplier functions correctly.
Use INDIRECT, ADDRESS, and ROW Functions to Skip Rows
Combine these three functions to dynamically calculate and shift the referenced row number by a specific multiplier when dragging formulas.
By using the ROW function to count how many rows you have dragged the formula, you can multiply this count by the number of rows you want to skip. ADDRESS constructs the correct cell reference text, and INDIRECT converts that text into a usable cell reference.
Determine the first row of your source data (for example, Row 2) and count how many rows each record occupies (for example, 3 rows).
Select the destination cell. Use the ROW function to create an incrementing number: `(ROW(A1)-ROW($A$1))*3`. This will increase by 3 for every row you drag down.
Incorporate your logic into the ADDRESS and INDIRECT functions. For a complex calculation spanning 3 rows, input: `=SQRT(INDIRECT(ADDRESS(2+(ROW(A1)-ROW($A$1))*3,COLUMN(C1))) * INDIRECT(ADDRESS(3+(ROW(A1)-ROW($A$1))*3,COLUMN(C1)))) / INDIRECT(ADDRESS(4+(ROW(A1)-ROW($A$1))*3,COLUMN(C1)))`.
Press Enter to calculate the first result. Click the fill handle in the bottom-right corner of the cell and drag it down or across to apply the skipping-row formula to the rest of your data.
Use the OFFSET Function as an Alternative
OFFSET is another efficient way to return a cell reference that is a specific number of rows away from a starting anchor cell.
Easily Manage Complex Formulas with WPS Spreadsheet
WPS Office provides a highly capable and fully compatible spreadsheet tool that seamlessly handles advanced functions like INDIRECT, OFFSET, and ADDRESS. You can build, debug, and drag complex skipping-row formulas exactly as you do in Microsoft Excel.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your existing Excel workbook containing the grouped data.
- 2. Apply the skipping formula: Type your INDIRECT or OFFSET skipping-row formula into the desired target cell.
- 3. Drag the fill handle: Select the cell, click the small square at the bottom-right corner, and drag it down to auto-fill the pattern accurately.
Frequently Asked Questions
Why does dragging a formula normally only increase by one row?
By default, spreadsheet software uses relative cell references. When you drag a formula down one row, the application assumes you want to shift all references down by exactly one row to match the new relative position.
Will using OFFSET or INDIRECT slow down my spreadsheet?
Yes, OFFSET and INDIRECT are 'volatile' functions. This means they recalculate every time any change is made anywhere in the workbook. If used heavily across thousands of rows, they can slow down performance.
How do I skip columns instead of rows when copying formulas?
You can achieve this by swapping the logic to use the COLUMN function instead of ROW. For example, use `(COLUMN(A1)-1)*3` as your multiplier inside the OFFSET or ADDRESS function to jump across columns.
Can I just copy and paste the cells to skip rows?
If you copy and paste a formula normally, it will only shift references by the number of cells you moved. To skip rows automatically in a continuous list without manual editing, you must use dynamic reference functions.
