Excel Formula to Skip Rows Based on Value (7 Examples)

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.

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.

• 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.

• Then, press Enter.

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

• 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.

• Press Enter.

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

• 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.

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.

• Then, press Enter.

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

• 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.

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.

• 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.

• Press Enter.

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

• 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.

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.

• Press Enter.

• Again, select cell F5 and write down the following formula to get the value of the salary of that employee.

• 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.

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