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.


Download Practice Workbook

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


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

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.

Utilizing FILTER Function to Skip Rows for Specific Value

  • 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 (8 Easy Methods)


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.

Applying OFFSET Function to Skip Two Months Row Using Excel Formula

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

Applying OFFSET Function for Skipping Points Rows by Excel Formula

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

Skip Every Two Rows and Get Third One Based on Value Through Excel Formula

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 Easy Ways)


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.

Skip Other Rows to Get Employee Name

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

Skip Other Rows to Get the Value of Salary

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

Using INDEX and ROW Functions to Skip Based on Value

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: Skip Cells When Dragging in Excel (4 Easy Methods)


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.

Skip Employee's Name Rows Based on Value

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

Skip Employee's Salary Based no Value

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

Combining INDEX, AGGREGATE and ROW Functions to Get Desired Row

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 (3 Methods)


5. Skip Blank Rows

In this example, we will skip the blank rows 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.

Skip Blank Rows Using FILTER Function

  • 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 (5 Easy Ways)


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.

Skipping Employee Name Rows That Are Less Than Specific ValueUsing Ecel

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

Skipping Points Rows That Are Less Than Specific Value Using Excel Formula

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

Skipping Rows That Are Less Than Specific Value

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.

Skipping Employee Name Rows That Are Greater Than Specific Value

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

Skipping Points Rows That Are Greater Than Specific Value

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

Skipping Rows That Are Greater Than Specific Value Using Formula

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


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.

Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo