How to Retrieve Every Other Data Row Using Formulas in Excel
Question details
The user needs an Excel formula to automatically extract every alternate data row from a source worksheet into a new destination, especially when header rows separate the data.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting alternating rows (every second row) from a structured dataset to reorganize or summarize data in a new location.
- Observed behavior
- The user is looking for a precise formula combination, such as INDEX and ROWS, customized to their specific workbook structure to accurately return alternating rows.
Ensure your source dataset has a consistent layout, and identify the exact starting cell of your data so the formula accurately targets alternating rows without offset errors.
Use INDEX and ROW Functions to Extract Every Other Row
Combine the INDEX function with math using the ROW function to calculate and retrieve every second row from your source array. This method works universally across all spreadsheet versions.
The INDEX function returns a value from a specific position in a range. By mathematically manipulating the ROW function (e.g., multiplying by 2), you can tell Excel to skip rows and only return the 1st, 3rd, 5th (or 2nd, 4th, 6th) records. You must lock your source range with absolute references ($) before copying the formula.
Locate the column containing your source data (e.g., A2:A100). Click on the topmost cell in the new column where you want the alternating data to appear.
To extract the 1st, 3rd, and 5th items from your list, enter the formula: =INDEX($A$2:$A$100, ROW(A1)*2-1). Make sure to adjust the $A$2:$A$100 range to match your actual data.
If you instead need to extract the 2nd, 4th, and 6th items from the list, omit the minus one and use: =INDEX($A$2:$A$100, ROW(A1)*2).
Press Enter to apply the formula. Click the cell, hover over the square fill handle in the bottom-right corner, and drag it down to extract the remaining alternate rows.
Use FILTER and MOD Functions (For Modern Spreadsheets)
If you use a modern spreadsheet version supporting Dynamic Arrays, the FILTER function combined with MOD provides a cleaner approach that automatically spills the results.
Extract Alternate Rows Easily with WPS Spreadsheet
WPS Office fully supports advanced array formulas, including INDEX, ROW, and FILTER. It allows you to manipulate complex data, organize datasets, and extract alternating rows seamlessly without performance lags.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your source data.
- 2. Input the formula: Select your destination cell and type the =INDEX($A$2:$A$100, ROW(A1)*2-1) formula.
- 3. Drag to fill: Press Enter, then click and drag the fill handle downwards to instantly extract the rest of your alternating rows.
Frequently Asked Questions
Why is my INDEX formula returning a #REF! error when dragged down?
This usually happens if you forgot to lock the source array with absolute references. If you type A2:A100 instead of $A$2:$A$100, the range shifts downwards as you drag the formula, eventually referencing empty cells outside the worksheet bounds. Highlight the range in your formula and press F4 to lock it.
How do I retrieve every third or fourth row instead of every second?
You can easily modify the multiplier in your ROW formula. To extract every third row, multiply by 3 (e.g., =INDEX($A$2:$A$100, ROW(A1)*3)). For every fourth row, multiply by 4, and so on. Adjust the subtraction at the end if you need to offset the starting point.
Can I just delete the alternating blank rows instead of using a formula?
Yes. If your goal is simply to remove every other row because they are blank, select your data range, press F5 to open 'Go To', click 'Special', and select 'Blanks'. Right-click one of the highlighted blank cells and choose 'Delete' -> 'Entire Row'.
