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.

How to Skip Cells in Excel Formula: 8 Easy Methods

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 as follows.

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.

Read More: How to Skip Hidden Cells When Pasting in Excel

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.

• 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

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.

• Lastly, after pressing the Enter button, you will get the desired result.

Read More: Skip Cells When Dragging in Excel

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.

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

Read More: Skip to Next Result with VLOOKUP If Blank Cell Is Present

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.

• Last, after pressing the Enter button, you will get the desired result.

Read More: How to Skip Lines in Excel

6. Merging IF, ROWS, COUNTA, INDEX, SMALL, ROW & ROWS Functions

In this case, our goal is to skip by using a combination of functions. The COUNTA 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 as follows.

Steps:

• To begin with, arrange a dataset like the below image.

• In addition, insert the following formula in cell F5.

• Furthermore, after pressing the Enter button, you will get the desired result.

• Finally, use the Fill Handle to get the desired result.

Read More: How to Skip Blank Rows Using Formula in Excel

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 >> save the VBA code.

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.

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

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

Henceforth, follow the above-described methods. Hopefully, these methods will help you to skip cells in an Excel formula. We will be glad to know if you can execute the task in any other way. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our level best to solve the problem or work with your suggestions.

Related Articles

• How to Skip a Column When Selecting in Excel
• Excel Formula to Skip Rows Based on Value
• How to Skip Every Other Column Using Excel Formula
• How to Skip Columns in Excel Formula

<< Go Back to Skip Cells  | Excel Cells | Learn Excel