# how to filter each 4 rows in Excel?

Actually I have an Excel file(xlsx - MS Office 2010) file that it has a column (Just A) and 3000 rows, I want to filter it each 4 rows, for example at first I want Row 4 after that row 8 after that row 12 and ...

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
``````

how can I fiter it please ? sorry for bad english ...

-

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