Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

# How to Skip Cells in Excel Formula (8 Easy Methods)

This tutorial will demonstrate how to skip cells in an excel formula. When dealing with lots of data, sometimes we have to skip unnecessary cells for ease of work. Moreover, after using certain if it’s not needed anymore, then it should be easily skipped to avoid confusion. So, it is very to learn how to skip cells in an excel formula.

## 8 Easy Methods to Skip Cells in Excel Formula

We’ll use a sample dataset overview as an example in Excel to understand easily. In this case, we have the Name in column B, ID in column C, EEE, ME, and CE in columns D, E, and F. If you follow the steps correctly, you should learn how to skip cells in the excel formula on your own. The steps are: ### 1. Use of Filter Option

Now, we want to skip cells by using the Filter Option. This is the easiest way to skip cells in excel. The steps of this method are.

Steps:

• At first, go to select the table > Data > Filter options. • Second, you will get the result like the below image. • Third, click on the filter point (in this case Name), untick the desired data, and press OK. • Last, you will get the desired result. ### 2. Utilizing COUNTBLANK Function

Next, we can skip cells by using the COUNTBLANK function. This function is used for counting. In our case, we will use this function to count skip cells. We can learn the method by following the below steps.

Steps:

• To begin with, create a dataset similar to the below image. • In addition, insert the following formula in cell G5.
`=COUNTBLANK(B5:F5)` • Furthermore, you will get results for the cell.
• Then, use the Fill Handle to apply it to all desired cells. • Next, you will get the desired result. • After that, go to select the table > Data > Filter options like the first method. Then click on the filter point, untick the desired cells, and press OK. • Finally, you will get the desired result. Read More: How to Skip to Next Cell If a Cell Is Blank in Excel (5 Easy Ways)

### 3. Applying FILTER Function

In this case, we aim to skip cells by applying the FILTER function. The work of this function is to filter a data range and give a filtered result. To learn this method, we have to follow the below steps.

Steps:

• Firstly, create a dataset like the below image. • Secondly, insert the following formula in cell F5.
`=FILTER(B5:D11,(B5:B11<>"")*(C5:C11<>"")*(D5:D11<>""))` • Lastly, after pressing the Enter button, you will get the desired result. ### 4. Combining IF, AND & ISBLANK Functions

We can also apply a combination of IF, AND & ISBLANK functions to skip cells. The If function gives logical comparison, and the And function returns if the condition is true or false, but when the cell has no information but still true or false comparison is needed then the ISBLANK function is used.  The steps of this method are.

Steps:

• First, arrange a dataset like the below image. • Next, insert the following formula in cell F5.
`=IF(AND(ISBLANK(B5), ISBLANK(C5),ISBLANK(D5),ISBLANK(E5)),"Blank", "Not-Blank")` • After that, you will get the result for the cell and then use the Fill Handle option. • Afterward, you will get the below result. • Next to that, go to select the table > Data > Filter options like the first method. Then click on the filter point, untick the desired cells, and press OK. • Finally, after pressing the Enter button, you will get the desired result. 🔎 How Does the Formula Work?

• ISBLANK(E5)): It represents the selected cell E5.
• AND(ISBLANK(B5), ISBLANK(C5), ISBLANK(D5), ISBLANK(E5)), “Blank”, “Not-Blank”: This portion presents the selected cell the conditions will be applied on.
• IF(AND(ISBLANK(B5), ISBLANK(C5),ISBLANK(D5),ISBLANK(E5)),”Blank”, “Not-Blank”): This portion presents the condition in the function.

### 5. Combining IFERROR, INDEX, SMALL, IF, ROW & ROWS Functions

Now, we will combine multiple functions to skip cells. The IFERROR function traps and handles errors in a formula, the INDEX function returns a value within a range, the SMALL function categorizes excel data statistically and the ROW or ROWS functions are mainly used in counting selected rows or numbers. We have to follow the below steps to learn this whole method.

Steps:

• First, arrange a dataset like the below image. • Second, insert the following formula in cell F5.
`=IFERROR(INDEX(B:B,SMALL(IF(B\$5:B\$11<>"",ROW(B\$5:B\$11)), ROWS(B\$5:B5))), "")` • Last, after pressing the Enter button, you will get the desired result. 🔎 How Does the Formula Work?

• ROWS(B\$5:B5): This portion represents the reference cell.
• ROW(B\$5:B\$11): this portion represents the selected range of the cell.
• SMALL(IF(B\$5:B\$11<>””,ROW(B\$5:B\$11)), ROWS(B\$5:B5): this portion represents fixed cells of reference of columns.
• IFERROR(INDEX(B:B,SMALL(IF(B\$5:B\$11<>””,ROW(B\$5:B\$11)), ROWS(B\$5:B5))), “”): this portion presents the whole condition.

### 6. Merging IF, ROWS, COUNT, INDEX, SMALL, ROW & ROWS Functions

In this case, our goal is to skip by using a combination of functions. The COUNT function is used when a large number of data entries are needed within a certain range. All the other functions are already explained in the previous methods. The steps of this method are.

Steps:

• To begin with, arrange a dataset like the below image. • In addition, insert the following formula in cell F5.
`=IF(ROWS(B\$5:B5)>COUNTA(B:B),"",INDEX(B:B,SMALL(IF(B\$5:B\$11<>"", ROW(B\$5:B\$11)),ROWS(B\$5:B5))))` • Furthermore, after pressing the Enter button, you will get the desired result. • Finally, use the Fill Handle to get the desired result. 🔎 How Does the Formula Work?

• ROWS(B\$5:B5): This portion represents the reference cell.
• ROW(B\$5:B\$11): this portion represents the selected range of the cell.
• INDEX(B:B,SMALL(IF(B\$5:B\$11<>””, ROW(B\$5:B\$11)),ROWS(B\$5:B5))):this portion represents the count function the formula will work on.
• IF(ROWS(B\$5:B5)>COUNTA(B:B),””,INDEX(B:B,SMALL(IF(B\$5:B\$11<>””, ROW(B\$5:B\$11)),ROWS(B\$5:B5)))): This portion represents the whole condition.

### 7. Applying VBA Code

We can skip cells by using the VBA code. We have to follow the below steps to learn this whole method.

Steps:

• Firstly, arrange the dataset like the below image. • Secondly, press Alt+F11 options to open the VBA window. • Thirdly, insert the following code in the window.
``````Sub Skip_Blank_Rows()
Range("B5:F11").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub`````` • Lastly, after pressing the RUN or F5 button, you will get the desired result. ### 8. Using OFFSET Function

In this case, our goal is to skip cells in excel by using the OFFSET function. When any complicated conditional result is needed, then it is the most useful method to use. The steps of this method are.

Steps:

• First, arrange a dataset like the below image. • Next, insert the following formula in cell E5.
`<span style="font-size: 14pt;">=OFFSET(\$B\$5,ROW(D1)*2-1,0)</span>` • After that, after pressing the Enter button, you will get the desired result. • Finally, use the Fill Handle to get the final result. ## Things to Remember

• If there remains any error in any formula, excel will show an error.
• The Filter Function is only available for Microsoft 365 users.
• While using the formulas, you should give proper attention to the cell you are using. Otherwise, the formula won’t give the desired result.

## Conclusion  