### Example 1 – Using the FILTER Function

In the first example, we are going to use **the FILTER function** to skip rows based on value using the Excel formula. For that, we consider a dataset that has the name and gender of **five** employees of a company. Our dataset is in the range of cells **B5:C9**, and we will show our result in column **E**.

- Select cell
**E5**. - Enter the following formula. This formula will skip rows where the gender of the employees is Female:

`=FILTER(B5:B9,C5:C9="Male")`

- Press
**Enter**.

- You’ll see that the two rows with female employees are skipped, and the names of the other three employees appear in column
**E**.

**Read More: **How to Skip Cells in Excel Formula

### Example 2 – Applying the OFFSET Function

In this example, we will use **the OFFSET function** to skip rows based on value using the Excel formula. To demonstrate the example, we consider a dataset of **12** months with gradually increased points, and we will show our result in columns **E** and **F**. We want to get the rows after every two rows.

- Select cell
**E6**. - Enter the following formula to get the name of the months:

`=OFFSET($B$5,ROW(E1)*3-1,0)`

- Press
**Enter**.

- Select cell
**F6**and enter this formula to get the corresponding points:

`=OFFSET($C$5,ROW(F1)*3-1,0)`

- Press
**Enter**.

- Drag the
**Fill Handle**icon from**E6:F6**to copy the formulas up to cell**F9**. - You’ll notice that
**four**rows are**copied**, and the other**two**rows in between are**skipped**.

**Read More: **How to Skip Columns in Excel Formula

### Example 3 – Using the INDEX and ROW Functions

In the following example, we are going to use the **INDEX** and **ROWS** functions to skip rows based on value. Our dataset is in the range of cells **B5:B14**. We will skip each row after the employee’s name and show our result in column **D**. We will follow the same steps to obtain the salary.

- Select cell
**D5**. - Enter the following formula to get the name of the employees:

`=INDEX($B$5:$B$14,ROWS($E$5:E5)*2-1)`

- Press
**Enter**.

- Select cell
**E5**and enter this formula to get the salary of that employee:

`=INDEX($B$5:$B$14,ROWS($E$5:E5)*2)`

- Press
**Enter**.

- Select the range of cells
**D5:E5**and drag the**Fill Handle**icon to copy the formulas up to cell**F7**. - You’ll see that all the names and salaries are displayed in a column, with every row in between skipped.

** Breakdown of the Formula (for cell D5)**

**ROWS($E$5:E5)**: The**ROWS**function returns the row number. In this case, it returns**1**.**INDEX($B$5:$B$14, ROWS($E$5:E5)*2-1)**: The**INDEX**function uses the result of the**ROWS**function to retrieve the value from the specified row range. In this example, the value is**Harry**.

**Read More: **How to Skip Cells When Dragging in Excel

### Example 4 – Combining INDEX, AGGREGATE and ROW Functions

The **IFERROR**, **INDEX**, **AGGREGATE**, and **ROW** functions will help us to skip rows based on value using the Excel formula. For that, we consider a dataset that has the names and genders of five employees of a company. Our dataset is in the range of cells **B5:C9**, and we will show our result in columns **E** and **F**.

- Select cell
**E5**. - Enter the following formula to get the name of the employees:

`=IFERROR(INDEX($B$5:$B$9,AGGREGATE(15,6,1/($C$5:$C$9="Male")*(ROW($C$5:$C$9)-ROW($C$4)),(ROW()-ROW($E$4)))),"")`

- Press
**Enter**.

- Select cell
**F5**and enter the following formula to get the gender of that employee.

`=IFERROR(INDEX($C$5:$C$9,AGGREGATE(15,6,1/($C$5:$C$9="Male")*(ROW($C$5:$C$9)-ROW($C$4)),(ROW()-ROW($E$4)))),"")`

- Press
**Enter**.

- Drag the Fill Handle icon from
**E5:F5**to copy the formulas up to cell**F7**. - You’ll get the names and salaries of
**male**employees, with**female**employees’ rows skipped.

** Breakdown of the Formula (for cell E5)**

**ROW($E$4)**: Shows the row number of cell**E4**(value is**4**).**ROW()**: Returns the row number of the current cell (row number is**5**).**ROW($C$4)**: Displays the row number of cell**C4**(value is**4**).**ROW($C$5:$C$9)**: Provides the row numbers for cells**C5**to**C9**.**AGGREGATE(15, 6, 1/($C$5:$C$9=”Male”)*(ROW($C$5:$C$9)-ROW($C$4)), (ROW()-ROW($E$4)))**: Using values from the**ROW**function,**AGGREGATE**determines which rows to display. For this cell, the value is**1**.**INDEX($B$5:$B$9, AGGREGATE(15, 6, 1/($C$5:$C$9=”Male”)*(ROW($C$5:$C$9)-ROW($C$4)), (ROW()-ROW($E$4))))**:**INDEX**retrieves the value based on the**AGGREGATE**result. Here, it returns**Harry**.**IFERROR(INDEX($B$5:$B$9, AGGREGATE(15, 6, 1/($C$5:$C$9=”Male”)*(ROW($C$5:$C$9)-ROW($C$4)), (ROW()-ROW($E$4)))), “”)**:**IFERROR**checks the**INDEX**result. If valid, it shows the value; otherwise, it returns a blank.

### Example 5 – Skip Blank Rows

In this example, we will skip the blank rows using a formula. The **FILTER function** will assist in skipping the blank rows. Our dataset is in the range of cells **B5:C13**, and there are four blank rows. We will show our results in columns **E** and **F**.

- Select cell
**E5**. - Enter the following formula to skip blank rows:

`=FILTER(B5:C13,(B5:B13<>"")*(C5:C13<>""))`

- Press
**Enter**.

- You’ll see that all blank rows are skipped, and only rows with values are displayed in columns
**E**and**F**.

**Read More: **How to Skip to Next Cell If a Cell Is Blank in Excel

### Example 6 – Skip Rows That Are Less Than a Specific Value

In this example, we will skip rows that have a value less than our desired value. The **IFERROR**, **INDEX**, **AGGREGATE**, and **ROW** functions will help us. We consider a dataset that has the names and points of **five** employees of a company. Our dataset is in the range of cells **B5:C9**, and we will show our result in columns **E** and **F**.

- Select cell
**E5**. - Enter the following formula to get the names of the employees with less than 50 points:

`=IFERROR(INDEX($B$5:$B$9,AGGREGATE(15,6,1/($C$5:$C$9<50)*(ROW($C$5:$C$9)-ROW($C$4)),(ROW()-ROW($E$4)))),"")`

- Press
**Enter**.

- Select cell
**F5**and enter this formula to get the points of those employees:

`=IFERROR(INDEX($C$5:$C$9,AGGREGATE(15,6,1/($C$5:$C$9<50)*(ROW($C$5:$C$9)-ROW($C$4)),(ROW()-ROW($E$4)))),"")`

- Press
**Enter**.

- Select the range of cells
**E5:F5**and drag the**Fill Handle**icon to copy the formulas up to cell**F7**. - You’ll see the names of employees whose points are less than
**50**displayed in the desired location.

** Breakdown of the Formula (for cell E5)**

**ROW($E$4)**returns the row number of cell**E4**, which is**4**.**ROW()**returns the row number of the current cell (**E5**), which is**5**.**ROW($C$4)**returns the row number of cell**C4**, which is**4**.**ROW($C$5:$C$9)**provides the row numbers for cells**C5**to**C9**.**AGGREGATE(15,6,1/($C$5:$C$9<50)*(ROW($C$5:$C$9)-ROW($C$4)),(ROW()-ROW($E$4)))**determines which row’s value to display. In this case, it’s row**4**.**INDEX($B$5:$B$9,AGGREGATE(15,6,1/($C$5:$C$9<50)*(ROW($C$5:$C$9)-ROW($C$4)),(ROW()-ROW($E$4)))**retrieves the value from column**B**corresponding to row**4**, which is**Chris**.**IFERROR(…,””)**ensures that if there’s an error (e.g., no valid value), it returns a blank cell.

### Example 7 – Skip Rows That Are Greater Than a Specific Value

In our last example, we are going to use the **IFERROR**, **INDEX**, **AGGREGATE**, and **ROW** functions to skip rows where the values are greater than a specific value. We consider a dataset that has the names and points of five employees of a company. Our dataset is in the range of cells **B5:C9**, and we will show our result in columns **E** and **F**.

- Select cell
**E5**. - Enter the following formula to get the names of the employees with more than 50 points:

`=IFERROR(INDEX($B$5:$B$9,AGGREGATE(15,6,1/($C$5:$C$9>50)*(ROW($C$5:$C$9)-ROW($C$4)),(ROW()-ROW($E$4)))),"")`

- Press
**Enter**.

- Select cell
**F5**and enter this formula to get the points of those employees:

`=IFERROR(INDEX($C$5:$C$9,AGGREGATE(15,6,1/($C$5:$C$9="Male")*(ROW($C$5:$C$9)-ROW($C$4)),(ROW()-ROW($E$4)))),"")`

- Press
**Enter**.

- Select the range of cells
**E5:F5**and drag the**Fill Handle**icon to copy the formulas up to cell**F6**. - You’ll see the names of employees whose points are greater than
**50**displayed in the desired location.

** Breakdown of the Formula (for cell E5)**

**ROW($E$4):**The function shows the row number of cell**E4**. Here, the value is**4**.**ROW():**The function returns the row number of this cell. The row number is**5**.**ROW($C$4):**The**ROW**function shows the row number of cell**C4**. Here, the value is**4**.**ROW($C$5:$C$9):**Here, the function provides us with the row number of the cells**C5**to**C9**.**AGGREGATE(15,6,1/($C$5:$C$9>50)*(ROW($C$5:$C$9)-ROW($C$4)),(ROW()-ROW($E$4))):**Using all the values from the**ROW**function the**AGGREGATE**function returns which rows value have to show. For this cell, the value will be**2**.**INDEX($B$5:$B$9,AGGREGATE(15,6,1/($C$5:$C$9>50)*(ROW($C$5:$C$9)-ROW($C$4)),(ROW()-ROW($E$4))):**The**INDEX**function will use the result of the**AGGREGATE**function and display the value of the cell. Here, the value returns**Ron**.- The
**IFERROR(…,””):**The**IFERROR**function checks the result of the**INDEX**function. If the**INDEX**function returns any valid value the function will show it. Otherwise, the function will return a blank. Here, the function returns**Ron**.

**Read More: **Skip to Next Result with VLOOKUP If Blank Cell Is Present

**Download Practice Workbook**

You can download the practice workbook from here:

## Related Articles

- How to Skip Hidden Cells When Pasting in Excel
- How to Skip a Column When Selecting in Excel
- How to Skip Lines in Excel

**<< Go Back to Excel Cells | Learn Excel**