How to Increase Excel Column References by Two Columns with Autofill
Question details
The user needs to sum pairs of columns across a dataset and automatically advance the column references by two (e.g., D:E, then F:G) when applying a formula horizontally.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filling a formula horizontally across cells while grouping and summing the source data in pairs of columns.
- Observed behavior
- Standard autofill shifts column references by one column at a time instead of pairs, requiring a specific dynamic array formula to achieve the desired two-column grouping.
Ensure you are using a modern spreadsheet version that supports dynamic array functions, as older versions may require complex volatile functions like OFFSET.
Use Dynamic Array Functions (WRAPROWS, BYROW, TRANSPOSE)
This is the most efficient method to dynamically group and sum adjacent pairs of columns without relying on manual adjustments or volatile formulas.
By combining WRAPROWS to structure the data, BYROW to calculate the sum, and TRANSPOSE to reorient the results, you can automate the process completely in modern spreadsheet software.
Click on the cell where you want the first summed result of your column pairs to appear.
Type the formula =TRANSPOSE(BYROW(WRAPROWS(D3:S3,2),SUM)) into the formula bar. Make sure to adjust the range D3:S3 to match the actual source range of your data.
Press Enter. The dynamic array will automatically calculate and spill horizontally, filling the adjacent cells with the sums of each consecutive column pair.
Sum Column Pairs Effortlessly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions, making it simple to manipulate, group, and analyze large datasets without relying on complex manual workarounds.
- 1. Open your dataset: Launch WPS Office and open your workbook in WPS Spreadsheet.
- 2. Enter the formula: Click on your target cell and input the =TRANSPOSE(BYROW(WRAPROWS(range,2),SUM)) formula.
- 3. Get automatic results: Press Enter to instantly calculate and spill the paired column sums horizontally across your sheet.
Frequently Asked Questions
Why does normal autofill only advance by one column?
By default, Excel increments relative cell references by exactly one row or column for each cell you drag the fill handle across. To jump by two, you must use mathematical offsets or dynamic arrays to redefine how the data is grouped.
Can I use the OFFSET function to increase column references by two?
Yes, you can use a combination of SUM, OFFSET, and COLUMN functions to achieve this in older versions of Excel that lack dynamic arrays. However, OFFSET is a volatile function that recalculates constantly, which can slow down performance on large sheets.
How do I group columns by three instead of two?
You can easily modify the dynamic array formula by changing the second argument in the WRAPROWS function. For example, use =TRANSPOSE(BYROW(WRAPROWS(D3:S3, 3), SUM)) to sum columns in groups of three.
