Formula Errors

How to Copy Excel Formulas to Noncontiguous Rows Without Skipping Values

Nimra Malik Oct 14, 2026 869 views

Question details

The user needs a method to retrieve sequential values from a source worksheet into destination cells that are separated by a fixed number of rows, without disrupting the data sequence.

How to Copy Excel Formulas to Noncontiguous Rows Without Skipping Values

Product: Excel
Device & OS: not provided
Scenario: Pulling and linking data from one worksheet into evenly spaced, noncontiguous rows on another worksheet.
Observed behavior: Using standard copy-paste or dragging a formula adjusts the relative cell references by the exact row gap (e.g., 48 rows), which causes the formula to skip the sequential values needed from the source data.

Before you start

Count the exact number of rows between each of your destination cells (your row gap) and identify the starting cell reference for both your source data and your destination sheet.

Solution 1Recommended

Use the INDEX and QUOTIENT Functions

Combine the INDEX, QUOTIENT, and ROW functions to calculate the correct sequential source position dynamically, ignoring the physical empty rows between your destination cells.

Standard relative references skip rows when dragged across noncontiguous cells. By using the mathematical quotient of the row difference, you can force Excel to step sequentially through the source array (e.g., pulling row 1, then row 2) even if the destination cells are dozens of rows apart.

Select the first destination cell

Click on the first cell in your destination worksheet where you want the linked data to appear (for example, cell C9).

Enter the formula

Type the formula =INDEX(Sheet1!$B$2:$B$100, QUOTIENT(ROW()-ROW($C$9), 48)+1) into the cell. Replace 'Sheet1!$B$2:$B$100' with your actual source data range.

Adjust the row gap parameter

Change the number '48' in the formula to match the exact number of rows between your noncontiguous destination cells. Also, ensure '$C$9' matches the cell address of your very first destination cell.

Fill the formula into subsequent cells

Copy the formula from your first destination cell and paste it into the other required destination cells. The formula will retrieve the values exactly as they appear in the source sequence.

Absolute References: Ensure your source data range and your starting destination cell use absolute references (with $ signs, like $B$2:$B$100) so they do not shift when copying the formula down.

Advanced Formulas Made Easy

Use WPS Spreadsheet to Handle Complex Formulas

WPS Office offers a powerful Spreadsheet application that fully supports advanced functions like INDEX, QUOTIENT, and ROW. You can easily manage complex, noncontiguous data retrieval tasks just as you would in Microsoft Excel.

1. Open WPS Spreadsheet: Launch WPS Office and create a new Spreadsheet, or open your existing Excel workbook.
2. Input the data: Ensure your source data is organized on one sheet and navigate to your spaced-out destination sheet.
3. Apply the INDEX formula: Type the INDEX and QUOTIENT formula into your destination cell, using the exact syntax as you would in Microsoft Excel.
4. Copy across gaps: Copy and paste the formula to your noncontiguous rows to pull your data sequentially without skipping any values.

100% compatible with Microsoft Excel formulas and .xlsx formatsLightweight application that runs smoothly on almost any deviceFamiliar user interface requiring no steep learning curveFree to use with built-in PDF editing tools

microsoft office alternative - wps office

Frequently Asked Questions

Why do cell references skip values when I paste them to a lower row in Excel?

By default, Excel uses relative referencing. If you paste a formula 48 rows down, Excel automatically adjusts the cell references within that formula by 48 rows. You must use mathematical logic (like the QUOTIENT function) to force sequential counting regardless of physical row placement.

Can I use the OFFSET function instead of INDEX?

Yes, OFFSET combined with ROW calculations can achieve similar results. However, OFFSET is a volatile function that recalculates every time a change is made in the workbook, which can slow down performance in large files. INDEX is non-volatile and generally preferred.

What does the QUOTIENT function do in this specific formula?

The QUOTIENT function returns the integer portion of a division. By dividing the difference in row numbers by your fixed gap size (e.g., 48), it creates a clean, stepping integer sequence (0, 1, 2) that tells the INDEX function exactly which sequential row to grab from the source data.

Excel & Data Problems