Sometimes, we need to skip rows based on the cell value. Using Excel formulas, we can easily do such jobs in a few seconds. In this article, we will demonstrate seven suitable examples, to skip rows based on value using the Excel formula. If you are also curious about it, download our practice workbook and follow us.

**Table of Contents**Expand

## How to Skip Rows Based on Value Using Excel Formula: 7 Suitable Examples

To demonstrate the examples, we need four different types of datasets because every formula will not be able to skip rows in each dataset.

### 1. Utilizing 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**.

The steps to complete the example are given below:

**📌 Steps:**

- First of all, select cell
**E5**. - Now, write down the following formula into the cell. Here, we will skip those rows where the gender of the employees is Female.

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

- Press
**Enter**.

- You will see those two rows skipped, and the name of the other three employees are shown in column
**E**.

Thus, we can say that our formula works perfectly, and we are able to skip rows based on value using the Excel formula.

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

### 2. Applying 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.

The steps of this example are given as follows:

**📌 Steps:**

- First, select cell
**E6**. - After that, write down the following formula into the cell to get the name of the months.

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

- Then, press
**Enter**.

- Again, select cell
**F6**and write down the following formula to get the points of that corresponding month.

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

- Similarly, press
**Enter**.

- Next, select the range of cells
**E6:F6**and**drag**the**Fill Handle**icon to copy the formula up to cell**F9**. - You will see
**four**rows are copied and the other**two**rows between them are skipped.

So, we can say that our formula works precisely, and we are able to skip rows based on value using the Excel formula.

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

### 3. Using 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 name and show our result in column **D**. For getting the salary, we will do the same task.

The steps of this process are given below:

**📌 Steps:**

- At first, select cell
**D5**. - Afterward, write down the following formula into the cell to get the name of the employees.

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

- Press
**Enter**.

- Similarly, select cell
**E5**and write down the following formula to get the salary of that employee.

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

- Again, press
**Enter**.

- Now, select the range of cells
**D5:E5**and**drag**the**Fill Handle**icon to copy the formula up to cell**F7**. - You will notice that all the names and salaries are shown in a column, and every row between each is skipped.

Hence, we can say that our formula works effectively, and we are able to skip rows based on value using the Excel formula.

**🔎 Breakdown of the Formula**

We are breaking down the formula for cell **D5**.

`👉`

**ROWS($E$5:E5)**: The **ROWS** function shows the row number. Here, the function will return **1**.

`👉`

**INDEX($B$5:$B$14,ROWS($E$5:E5)*2-1)**: The **INDEX** function uses the result of the **ROWS** function and returns us the value from the selected row range. Here, the value is **Harry**.

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

### 4. Combining INDEX, AGGREGATE and ROW Functions

Here, 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 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 columns **E** and **F**.

The steps of this process are explained below:

**📌 Steps:**

- Firstly, select cell
**E5**. - Now, write down the following formula into the cell 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)))),"")`

- Then, press
**Enter**.

- Similarly, select cell
**F5**and write down the following formula to get the salary 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)))),"")`

- Again, press
**Enter**.

- After that, select the range of cells
**E5:F5**and**drag**the**Fill Handle**icon to copy the formula up to cell**F7**. - You will get those employees who are
**male**and the**female**employee’s row will skip.

Therefore, we can say that our formula works successfully, and we are able to skip rows based on value using the Excel formula.

**🔎 Breakdown of the Formula**

We are breaking down 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=”Male”)*(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 **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))))**: The **INDEX** function will use the result of the **AGGREGATE** function and display the value of the cell. Here, the value 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)))),””)**: 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 **Harry**.

**Read More: **How to Skip Every Other Column Using Excel Formula

### 5. Skip Blank Rows

In this example, we will skip the blank rows using formula from our dataset. For skipping the blank rows **the FILTER function** will help us. 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**.

The steps to complete the example are given as follows:

**📌 Steps:**

- First of all, select cell
**E5**. - Now, write down the following formula into the cell.

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

- Press
**Enter**.

- You will see all the blank rows will be skipped and the rows with values are shown.

At last, we can say that our formula works perfectly, and we are able to skip rows based on value using the Excel formula.

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

### 6. Skip Rows That Are Less Than Specific Value

Here, we will skip those rows that have a value less than our desired value. To fulfill the operation the **IFERROR**, **INDEX**, **AGGREGATE**, and **ROW** functions will help us. 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 columns **E** and **F**.

The procedure is described below steps by step::

**📌 Steps:**

- In the beginning, select cell
**E5**. - Then, write down the following formula into the cell to get the name of the employees.

`=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**.

- Similarly, select cell
**F5**and write down the following formula to get the salary of that employee.

`=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 the
**Enter**key.

- Select the range of cells
**E5:F5**and**drag**the**Fill Handle**icon to copy the formula up to cell**F7**. - You will see those employees whose points are less than
**50**shown in our desired location.

In the end, we can say that our formula works effectively, and we are able to skip rows based on value using the Excel formula.

**🔎 Breakdown of the Formula**

We are breaking down 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 **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))))**: The **INDEX** function will use the result of the **AGGREGATE** function and display the value of the cell. Here, the value returns **Chris**.

👉 **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)))),””)**: 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 **Chris**.

### 7. Skip Rows That Are Greater Than Specific Value

In our last example, we are going to use the **IFERROR**, **INDEX**, **AGGREGATE**, and **ROW** functions to skip those rows whose values are greater than a specific value. 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 columns **E** and **F**.

The procedure to complete this example is explained below steps by step::

**📌 Steps:**

- At first, select cell
**E5**. - Next, write down the following formula into the cell.

`=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**.

- Again, select cell
**F5**and write down the following formula to get the value of the salary 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)))),"")`

- Similar, press
**Enter**.

- After that, select the range of cells
**E5:F5**. - Now,
**drag**the**Fill Handle**icon to copy the formula up to cell**F6**. - You will see those employees whose points are greater than
**50**shown at our desired location.

Finally, we can say that our formula works successfully, and we are able to skip rows based on value using the Excel formula.

**🔎 Breakdown of the Formula**

We are breaking down 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**.

`👉`

**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)))),””))**: 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**

Download this practice workbook for practice while you are reading this article.

## Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to skip rows based on value using the Excel formula. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

## 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 Skip Cells | Excel Cells | Learn Excel**