A Microsoft Excel spreadsheet with blank rows is a frequent occurrence. The majority of them are superfluous, and they take up extra space on the worksheet. This is unnecessary. As a result, you might wish to skip those blank rows from your spreadsheet. Fortunately, Microsoft Excel offers several options for skipping those blank rows from your spreadsheet. We can, however, apply formulae to skip them altogether. Today, in this article, we’ll learn eight quick and suitable ways to skip blank rows in Excel using formula effectively.

## How to Skip Blank Rows Using Formula in Excel: 8 Suitable Ways

Let’s say, we have a dataset that contains information about several **Students**. The securing marks in Electrical and Electronics Engineering(**EEE**), Mechanical Engineering(**ME**), and Civil Engineering(**CE**) are given in columns **D, E, **and **F** respectively. Our dataset has some blank rows. Hence, we will skip those blank rows by using the keyboard shortcuts and the **COUNTBLANK****, FILTER,** **IF**, **AND**, **ISBLANK****, SMALL**

**,**

**ROW**,

**ROWS**

**, INDEX,**and

**COUNT**functions in Excel. Here’s an overview of the dataset for today’s task.

### 1. Use the Keyboard Shortcuts to Skip Blank Rows in Excel

From our dataset, we will skip rows that are blank. Undoubtedly, using keyboard shortcuts to skip blank rows is an easy task. Let’s follow the steps below to skip blank rows.

**Step 1:**

- First of all, select cells array
**B5**to**F14**, and press the**F5**key on your**keyboard**.

- Hence, a
**Go To**window will appear in front of you. From that window, type the cell reference**B5:F14**in the**Reference**box. Then, press on the**Special**option.

- After that, the
**Go To Special**dialog box pops up. From the**Go To Special**dialog box, firstly, select**Blank**from the**Select**option and then press**OK**.

**Step 2:**

- After completing the above process, you will be able to select the blank rows.

- Hence, press the
**Ctrl + minus(-) key**simultaneously on your**keyboard,**and instantly a**Delete**window pops up. Now, select the entire row from the**Delete**option and at last, press**OK**.

- When you will complete the above steps, you will be able to skip blank rows which have been given in the below screenshot.

**Read More:** How to Skip Cells in Excel Formula

### 2. Perform the Delete Command to Skip Blank Rows in Excel

The easiest and time-saving way to skip blank rows is to perform the **Delete **command in **Excel**. Let’s follow the instructions below to learn!

**Step 1:**

- First of all, select cells array
**B4**to**F14**. Then, from your**Data**tab, go to,

**Data → Sort & Filter → Filter **

- Hence, a
**Filter**will enable each header cell in the first row of our selected data. After that, click on the**Filter**sign and a window pops up, from that window select**(Blank)**and then press**OK**.

**Step 2:**

- Further, from your
**Home**tab, go to,

**Home → Cells → Delete → Delete Sheet Rows**

- After clicking on the
**Delete Sheet Rows**option, you will be able to delete the blank rows.

- Now, we will remove the
**Filter**sign from the header cell. To do that, go to,

**Data → Sort & Filter → Filter **

- After completing the above process, you will be able to skip blank rows which have been given in the below screenshot.

**Read More: **How to Skip Lines in Excel

### 3. Apply the COUNTBLANK Function to Skip Blank Rows in Excel

We use the **COUNTBLANK Function** to count the number of blank cells in each of the rows. Based on this count value we can filter out the rows having blank cells in them. To do that follow the steps below.

**Step 1:**

- First of all, type the following
**COUNTBLANK**formula in cell**G5**.

`=COUNTBLANK(B5:F5)`

- Then simply press the
**ENTER**button on your keyboard, and you will get**0**as the return of the COUNTBLANK function.

- After that, drag the
**autoFill**Handle downward to the entire column.

**Step 2:**

- After all these steps, we will see the blank cell count for each of the rows. Skip blank rows in Excel using the
**COUNTBLANK**Hence, select the whole data table and then press**CTRL + SHIFT + L**. - This command will enable the
**Filter feature**on the data table like the following picture.

- After that, click on the drop-down icon of the
**Blank**column. Then select**0**, and finally, press the**OK**command.

- After completing the above process, you will be able to skip blank rows which have been given in the below screenshot.

**Read More: **How to Skip Columns in Excel Formula

### 4. Perform the FILTER Function to Skip Blank Rows in Excel

We can use the **FILTER** function to simply filter out all the blank rows from an Excel worksheet. This function is a dynamic array function. That means you only execute this function in a single cell, and then this function will automatically cover all the corresponding cells where the formula result should be. Now follow the steps below to do that.

**Steps:**

- First, select cell
**G5**, and write down the**FILTER**function in that cell. The**FILTER**function is,

`=FILTER(B5:E14,(B5:B14<>"")*(C5:C14<>"")*(D5:D14<>"")*(E5:E14<>""))`

**Formula Breakdown:**

**(B5:B14<>”” )** ▶ Checks for blank cells in the **B** column.

**(C5:C14<>””)** ▶ Look for blank cells in the **C** column.

**(D5:D14<>””)** ▶ Finds blank cell within column **D**.

**(E5:E14<>””)** ▶ Checks out column **E** for the blank cells.

Finally, the formula filters out all the blank cells by keeping only the rows without those blank cells.

- After that, press the
**ENTER**button on your keyboard, and instantly you will be able to skip the blank rows.

**Read More: **Excel Formula to Skip Rows Based on Value

### 5. Merge the IF, AND & ISBLANK Functions to Skip Blank Rows in Excel

In this method, we will use the **IF**, **AND**, and **ISBLANK** functions to specify which row is blank and which row is not. Using this formula, we will mark the rows having blank cells as Blank and rows without a single blank cell as Not-Blank. After that, based on this Blank/Not-Blank specification, we will filter out the blank rows from our Excel worksheet. Hence, follow the steps below to do that.

**Step 1:**

- Type the following formula in cell
**F5**.

`=IF(AND(ISBLANK(B5), ISBLANK(C5),ISBLANK(D5),ISBLANK(E5)),"Blank", "Not-Blank")`

**Formula Breakdown:**

**ISBLANK(B5)** ▶ It finds the blank cell.

**AND(ISBLANK(B5), ISBLANK(C5),ISBLANK(D5),ISBLANK(E5)** ▶ Indicates that the formula works from column B to column E of the 5th row of the worksheet.

The whole formula then finds the row is blank or not.

- After typing these functions in that cell, press the
**ENTER**button on your**keyboard**, and you will get**Not-Blank**as the output of the array functions.

- Then drag the
**autoFill**Handle downward, and you will get the output of the**IF, AND,**and**ISBLANK**Functions in column**F**.

**Step 2:**

- After that, select the whole data table and press
**CTRL + SHIFT + L**to apply the Filter feature on the data table. - Click on the drop-down icon at the corner of the Blanks table header.
- Further, uncheck the Blank option from the list. Finally, press the OK command.

- After pressing the
**OK**option, you will be able to skip blank rows from your dataset.

**Read More: **How to Skip a Column When Selecting in Excel

### 6. Combine the IFERROR, INDEX, SMALL, IF, ROW, & ROWS Functions to Skip Blank Rows

We will make an array formula using the **IFERROR**, **INDEX**,** SMALL**, **IF**, **ROW**, and **ROWS** functions altogether. This array function will skip all the blank rows out of the data table in Excel.

Using this method, we will keep the original data table aside. We will copy only the table headers from the original data table having blank rows in it. Under the copied table header we will use the formula to get all the rows excluding the blank ones. To do that, follow the steps below.

**Step 1:**

- First, write down the following array formula in cell
**G5**.

`=IFERROR(INDEX(B:B,SMALL(IF(B$5:B$14<>"",ROW(B$5:B$14)), ROWS(B$5:B5))), "")`

**Formula Breakdown:**

**ROW(B$5:B$14)** ▶ It gives a start from row 5.

**ROWS(B$5:B5)** ▶ It returns 1.

**ROW(B$5:B$14)),ROWS(B$5:B5)** ▶ Indicates that the formula works from the first column of the 5th row of the worksheet.

**SMALL(IF(C$5:C$14<>””,ROW(C$5:C$14)),ROWS(C$5:C5))** ▶ Finds the smallest cell address among the 5th row of the datasheet.

Lastly, the formula checks whether there are any blank cells in the 5th row. If not, then it returns the whole row.

- Further, press the
**ENTER**button, and you will get**Smith**as the output of the array functions.

**Step 2:**

- Now drag the
**Fill Handle**icon up to cell**J5**.

- Then drag it again up to cell
**J11**. This will exclude all the rows having blank cells in them as in the picture below.

### 7. Merge the IF, ROWS, COUNT, INDEX, SMALL, ROW, and ROWS Functions to Skip Blank Rows in Excel

Now we all create another array formula that you can use as an alternative to the previous formula. This time, we will use the **IF**, **COUNT**, **INDEX, SMALL,** **ROW**, & **ROWS** functions to formulate the array formula. As we will keep the original data table aside, as we did in the previous method, you’ve to copy the table header to another place first.

**Steps:**

- After that, select cell
**G5**to insert the following formula:

`=IF(ROWS(B$5:B5)>COUNTA(B:B),"",INDEX(B:B,SMALL(IF(B$5:B$14<>"", ROW(B$5:B$14)),ROWS(B$5:B5))))`

**ROW(B$5:B$14)** ▶ Tells that consider rows from the 5th to the thirteenth.

**ROWS(B$5:B5)** ▶ Indicates the first cell of the whole row.

**INDEX(B:B,SMALL(IF(B$5:B$14<>””,ROW(B$5:B$14)),ROWS(B$5:B5)))** ▶ Checks the 5th row for any blank cells. As there are no blank cells in the 5th row, it returns the first element of the 5th row which is **Smith**.

Finally, the formula checks for the blank rows within the entire table and returns rows where there’s no blank cell.

- Hence, press the
**ENTER**button to execute the array functions, and you will get**Smith**as the output of the array functions.

- After that, draw to
**autoFill**Handle icon to column**J**first.

- After that, drag it again to the end of the
**CE**This will keep only the solid rows by removing out all the rows having blank cells in them. That’s how you can skip blank rows using formula in excel.

### 8. Run a VBA Code to Skip Blank Rows in Excel

Last but not the least, we will skip blank rows from our dataset by applying the **VBA Code**. Applying the **VBA **code to **skip blank rows** is time-saving also. Let’s follow the instructions below to learn!

**Step 1:**

- First of all, from your
**Developer**tab, go to,

**Developer → Visual Basic**

- After clicking on the
**Visual Basic**ribbon, a window named**Microsoft Visual Basic for Applications – Skip Blank Rows**will instantly appear in front of you. From that window, we will insert a module for applying our**VBA code**. To do that, go to,

**Insert → Module**

**Step 2:**

- Hence, the
**Skip Blank Rows**module pops up. In the**Skip Blank Rows**module, write down the below**VBA**code.

```
Sub Skip_Blank_Rows()
Range("B5:F14").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
```

- After that, run the
**VBA**To do that, go to,

**Run → Run Sub/UserForm**

**Step 3:**

- Further, go back to your active worksheet, and you will be able to
**Skip Blank Rows**in your active worksheet that has been given below screenshot.

## Things to Remember

👉** #NAME** error happens while typing incorrectly the range name.

👉 The **#REF!** error occurs when a cell reference is not valid.

👉 The **FILTER **function is available, only in **Excel 365** version.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

## Conclusion

I hope all of the suitable methods mentioned above to skip blank rows using formula in excel will now provoke you to apply them in your **Excel** spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

**Related Articles**

- How to Skip Cells When Dragging in Excel
- How to Skip Hidden Cells When Pasting in Excel
- How to Skip Every Other Column Using Excel Formula
- Skip to Next Result with VLOOKUP If Blank Cell Is Present
- How to Skip to Next Cell If a Cell Is Blank in Excel

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