Microsoft Excel Functions & Formulas

Use the OFFSET function to filter every

other row

In the previous example, we filtered every other column. To do the

same with rows, use the ROW function to get the actual row and

combine it with the OFFSET function to get the result shown

below.

4

To extract every other row:

1. In cells A2:A16 type any numbers.

2. Select cells B2:D9 and type the following formula:

=OFFSET($A$2,(ROW()-2)*COLUMN(),0).

3. Press <Ctrl+Enter>.

Note: To hide all cells containing zero, select Options from

the Tools menu, click the View tab, and deactivate Zero

values.

236 Chapter 9

Figure 9-20