Function Problems

How to Copy a Formula Every Four Columns in Excel

Anum MushtaqAnum Mushtaq Nov 11, 2026 869 views

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.

How to Copy a Formula Every Four Columns in Excel
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.
Before you start

Identify the exact continuous range of your source data and decide on the absolute starting cell where your first dynamic formula will be placed.

Solution 1Recommended

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.

1
Select your starting cell

Click on the cell where you want your first formula to appear (for example, cell D2).

2
Input the dynamic formula

Enter the formula: =SUM(INDEX($A$1:$B$1000,QUOTIENT(COLUMN(D2)-COLUMN($D$2),4)+1,0))

3
Copy the formula

Select cell D2 and press Ctrl+C (or right-click and select Copy) to copy the cell containing the formula.

4
Paste at four-column intervals

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.

Use the INDEX, QUOTIENT, and COLUMN Functions
Understanding the logic: The QUOTIENT function divides the difference between the current column and the starting column by 4. It returns an integer, ensuring the row index only increases when a full 4-column gap is met.
Advanced Data Calculation

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. 1. Open your dataset: Launch WPS Office and open your workbook in the WPS Spreadsheet application.
  2. 2. Apply the INDEX formula: Select your target cell and enter the combined INDEX and QUOTIENT formula to establish the starting row.
  3. 3. Copy the cell: Use the copy function (Ctrl+C) on your starting cell.
  4. 4. Paste at intervals: Navigate four columns to the right and paste (Ctrl+V) the formula. WPS Spreadsheet will instantly calculate the offset.
Fully compatible with Microsoft Excel formulas and .xlsx file formats.Lightweight architecture ensures smooth performance even with massive datasets.Free access to powerful functions like INDEX, INDIRECT, QUOTIENT, and MATCH.Intuitive user interface for fast formula auditing and error checking.
microsoft office alternative - wps office

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.