How to Select Every Nth Row in Excel

To select every Nth row in Excel, you can use a combination of the INDIRECT and ROW functions in an array formula. This method enables you to select rows at a specific interval, such as every 3rd, 4th, or 5th row.

Follow these steps to select every Nth row in Excel:

1. Open the Excel workbook where you want to select every Nth row.
2. Create a new column where you want to display the selected rows. In this example, we'll use column C.
3. In the first cell of the new column (C1), enter the following formula:

=IFERROR(INDEX($A$1:$A$100,ROW()*N-1),"")

Replace "$A$1:$A$100" with the range of cells you want to select from, and replace "N" with the desired interval (e.g., 3 for every 3rd row, 4 for every 4th row, etc.).

4. Press Ctrl + Shift + Enter to enter the formula as an array formula. Excel will automatically surround the formula with curly braces {} to indicate it's an array formula.
5. Click on the bottom-right corner of the cell containing the formula (C1) and drag it down to fill as many cells as necessary to display all the selected rows.

Example

Let's say you have a list of values in cells A1:A20 and you want to select every 3rd row in the list.

1. In cell C1, enter the following array formula:

=IFERROR(INDEX($A$1:$A$20,ROW()*3-1),"")

2. Press Ctrl + Shift + Enter to enter the formula as an array formula.
3. Drag the formula down from cell C1 to C6 (or as many cells as necessary to display all the selected rows).

The selected rows will now be displayed in column C, starting from C1. In this example, you will see the values from rows 3, 6, 9, 12, 15, and 18 in column C.