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.
- 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.
- 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 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.
- 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: 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.
- 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 (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.
- 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.
- 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
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!