How to Copy a Formula Every Four Columns in Excel
Question details
The user needs to construct a formula that references sequential rows from a source dataset, but only increments the row reference when pasted in four-column intervals.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up a custom dashboard or summary sheet where data points from sequential rows must be spaced out horizontally across columns at specific intervals.
- Observed behavior
- Standard relative references increment every time the formula moves one column. The user requires a specialized formula to hold the row reference steady until a four-column offset is reached.
Identify the exact continuous range of your source data and decide on the absolute starting cell where your first dynamic formula will be placed.
Use the INDEX, QUOTIENT, and COLUMN Functions
Combine mathematical and lookup functions to dynamically calculate the row offset based on the current column position.
Because standard relative references cannot naturally skip columns in this specific pattern, you must calculate the desired row using the column position.
The QUOTIENT function calculates how many 4-column blocks you have moved from the starting cell, which is then used to step down the rows in the INDEX function.
Click on the cell where you want your first formula to appear (for example, cell D2).
Enter the formula: =SUM(INDEX($A$1:$B$1000,QUOTIENT(COLUMN(D2)-COLUMN($D$2),4)+1,0))
Select cell D2 and press Ctrl+C (or right-click and select Copy) to copy the cell containing the formula.
Select the cell four columns to the right (H2), paste the formula, and repeat this for the eighth column (L2), twelfth column (P2), and so on.

Construct Cell References Using INDIRECT
Use the INDIRECT function to manually piece together row and column references as text strings, calculating the exact target row.
Easily Manage Complex Formulas in WPS Spreadsheet
WPS Office natively supports advanced array formulas, INDEX, and mathematical functions. You can seamlessly create, copy, and manage dynamic data offsets just as you would in Microsoft Excel.
- 1. Open your dataset: Launch WPS Office and open your workbook in the WPS Spreadsheet application.
- 2. Apply the INDEX formula: Select your target cell and enter the combined INDEX and QUOTIENT formula to establish the starting row.
- 3. Copy the cell: Use the copy function (Ctrl+C) on your starting cell.
- 4. Paste at intervals: Navigate four columns to the right and paste (Ctrl+V) the formula. WPS Spreadsheet will instantly calculate the offset.

Frequently Asked Questions
Why doesn't regular copy and paste work for this specific scenario?
Regular copy and paste using relative references shifts the column or row reference by exactly the number of cells you moved. It cannot hold a reference steady for three columns and then suddenly change it on the fourth without additional mathematical logic.
Can I change the increment interval from four columns to something else?
Yes. In the formula =SUM(INDEX(...,QUOTIENT(COLUMN(target)-COLUMN(start),4)+1,0)), simply change the number 4 to your desired column interval, such as 3, 5, or 10.
Will this formula continue to work if my source data is on a different worksheet?
Yes. You just need to include the sheet name in your source range reference, such as 'Sheet2'!$A$1:$B$1000. The COLUMN calculations will still apply to the current active sheet where the formula is pasted.




