# How to filter for every 4th row in Excel?

I have an Excel file (xlsx - MS Office 2010) that has a column (Just A) and 3000 rows.

I want to filter for every 4th row.

for example:

``````TEMP -- 1
TEMP -- 2
TEMP -- 3
TEMP -- 4
TEMP -- 5
TEMP -- 6
TEMP -- 7
TEMP -- 8
TEMP -- 9
TEMP -- 10
TEMP -- 11
TEMP -- 12
TEMP -- 13
``````

and I want this result:

``````TEMP -- 4
TEMP -- 8
TEMP -- 12
``````
-

The easiest way is to use the `OFFSET` function. The function prototype appears as follows:

``````=OFFSET(range, rows, columns, height, width)
``````

To apply this for your situation, set `range` to the first cell of data (use an ABSOLUTE reference, see below in the formulas), `rows` to the offset, `columns` to 0 (since we don't want to shift the column), and finally set `height` and `width` to 1 (since we are returning a single cell).

So, assuming your data above is in column A, you can return the 4th, 8th, and 12th entry via the following formulas:

``````=OFFSET(\$A\$1,  3, 0, 1, 1)  or  =OFFSET(\$A\$1,  4 - 1, 0, 1, 1)
=OFFSET(\$A\$1,  7, 0, 1, 1)  or  =OFFSET(\$A\$1,  8 - 1, 0, 1, 1)
=OFFSET(\$A\$1, 11, 0, 1, 1)  or  =OFFSET(\$A\$1, 12 - 1, 0, 1, 1)
``````

Note that you subtract 1 from each, since `rows` is an OFFSET (so to get cell A4, you offset A1 by 3 rows, not 4). If you enter the first few cells manually, you can use Excel's auto-fill function to perform this automatically for the rest of the data.

You can also use the `INDIRECT` formula to accomplish this. First, make a new column with the row numbers you want (e.g. 1, 5, 9...). Then, in the adjacent columns, put the formula `=INDIRECT("A" & REF)` where `REF` is the cell to the left (with the row number), and "A" is the column containing your data.

Obviously, you can do this for any arbitrary number set. So, if you enter the following into Excel:

``````    A    B          C
1   D1   4   =INDIRECT("A" & B1)
2   D2   8   =INDIRECT("A" & B2)
3   D3  12   =INDIRECT("A" & B3)
4   D4
5   D5
6   D6
7   D7
8   D8
9   D9
10 D10
11 D11
12 D12
``````

You will see the contents `D4`, `D8`, and `D12` in column C. You can use auto-fill to extend these formulas as far as you need.

-
Thanks, is there anyway to handle it automatically ??? I can handle it with JAVA but I want to fix it with Excel if it possible –  Sam Jan 17 '12 at 15:30
If you fill in the first three cells, then select all three cells. In the bottom right corner of the selection, there will be a little black square. Click and drag the square down, and it should automatically continue filling in the formula (and incrementing the offset for you). You can also double-click the little square to have Excel automatically determine how far to bring it (depending on the contents around it). –  Breakthrough Jan 17 '12 at 15:32
OK, Thank you very much indeed, I'll do it !!! –  Sam Jan 17 '12 at 15:35
@MikeRedford also if that doesn't work, you can put the row offset in a different column, and then use Excel's auto-fill (sometimes it works kind of funky with formulas). So again let's say your data is in column A. In column B, put 4, 8, and 12, and then you should be able to auto-fill down as far as you need. Then in column C, first row put the formula `=OFFSET(\$A\$1, B1-1, 0, 1, 1)`. Finally, you should be able to auto-fill down this cell to get your desired data. –  Breakthrough Jan 17 '12 at 15:36
It works perfectly, thanks again & good luck buddy !!! –  Sam Jan 17 '12 at 15:49

The "modulus" operator using the ROW() operator would let you filter quickly. In a 2nd column, add the formula shown below. Note that I have put your data beginning in the 2nd row. Then all you need to do is filter column B on the "0" values.

-