How to Increase Excel References by Multiple Columns When Dragging
Question details
The user needs to increase cell references by a specific interval (e.g., 8 columns) instead of just one column when dragging an Excel formula horizontally.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Dragging a formula horizontally across cells to reference data spaced out by a consistent number of columns.
- Observed behavior
- By default, dragging a formula horizontally shifts the cell reference by only one column at a time, failing to skip multiple columns automatically.
Identify the absolute cell reference of your starting data and the exact destination cell where you will type the first formula, as both are required to calculate the dynamic column jump correctly.
Use the OFFSET Function with COLUMNS
Combine OFFSET and COLUMNS to dynamically advance references by a specific multiplier when dragging formulas horizontally.
The OFFSET function allows you to return a reference to a range that is a specific number of rows and columns away from a starting cell.
By nesting the COLUMNS function inside OFFSET and multiplying it by your desired column jump interval, you can force the spreadsheet to skip columns seamlessly.
Click on the cell where you want to place the first formula (for example, cell F13).
Type the formula =OFFSET($B$33,0,8*COLUMNS($F$13:F13)-8,1,1), replacing $B$33 with your absolute starting data reference and 8 with your desired column multiplier.
Ensure the COLUMNS function specifically targets your starting formula cell and uses a locked first reference ($F$13:F13) so it expands correctly.
Press Enter, click the fill handle (the small square at the bottom right of the cell), and drag it horizontally to fill your sequence.
Use an R1C1-Style INDIRECT Formula
Generate an R1C1 string that calculates the target column dynamically, then convert it to a reference using INDIRECT.
Master Advanced Formulas with WPS Spreadsheet
WPS Spreadsheet is a highly efficient tool that perfectly supports advanced mathematical functions, OFFSET, and R1C1 INDIRECT referencing to help you automate complex data manipulation effortlessly.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your target data.
- 2. Input the dynamic formula: Type your OFFSET or INDIRECT formula into the desired starting cell.
- 3. Drag to fill: Click and drag the fill handle across the row to instantly populate the dynamically spaced column data.
Frequently Asked Questions
Why does my formula output an error when I drag it?
This usually happens if you fail to lock your starting reference cell or leave the COLUMNS function empty. Ensure you use absolute references like $B$33 and specifically reference an expanding range starting with the original cell in COLUMNS.
How can I skip multiple rows when dragging a formula vertically?
You can achieve this by substituting the COLUMNS function with the ROWS function and placing the mathematical calculation within the row argument of the OFFSET function instead of the column argument.
What is the difference between OFFSET and INDIRECT?
OFFSET dynamically finds a cell by shifting a specific number of rows and columns from a defined starting point. INDIRECT, however, takes a literal text string representing a cell location and converts it into a working reference.
