Having blank rows in a Microsoft Excel worksheet is a common scenario. Most of them are unnecessary and they consume more spaces that increase the worksheet size. Which is completely unnecessary. So, you may want to delete those blank rows from your workbook. Fortunately, Microsoft Excel provides multiple ways to delete those blank rows out of your Excel worksheet. But we can also use formulas to remove them all. In this regard, we are going to discuss 5 examples regarding the Excel formula to remove blank rows. Keep reading!

**Table of Contents**hide

## Download the Practice Workbook

You can download the Excel file from the link below and practice along with it.

## 5 Examples Using Formula to Remove Blank Rows in Excel

We made a data table named Student Mark Sheet to demonstrate the usage of the 5 formulas to remove blank rows in Excel. The data table has four columns. The first column contains Name, the second, third, fourth column holds the course names where marks in those courses are reserved.

So, without having any further discussion let’s dive straight into all the methods one by one.

### Formula 1: Remove Blank Rows in Excel Using IFERROR, INDEX, SMALL, IF, ROW, & ROWS Functions

We will make an array formula using the **IFERROR**, **INDEX**,** SMALL**, **IF**, **ROW**, & **ROWS **functions altogether. This array function will clean up 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:

❶ Insert the following array formula in cell **G5**.

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

❷ Press the **ENTER** button.

❸ Now drag the **Fill Handle** icon up to cell **J5** first. Then drag it again up to cell **J11**.

This will exclude all the rows having blank cells in them as in the picture below:

**Formula Breakdown**

**ROW(B$5:B$13)**▶ it gives a start from row 5.**ROWS(B$5:B5)**▶ it returns 1.**ROW(B$5:B$13)),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$13<>””,ROW(C$5:C$13)),ROWS(C$5:C5))**▶ finds the smallest cell address among the 5th row of the datasheet.**=IFERROR(INDEX(B:B,SMALL(IF(B$5:B$13<>””,ROW(B$5:B$13)),ROWS(B$5:B5))), “”)**▶ checks whether there’s any blank cells in the 5th row. If not then it returns the whole row.

**Read More: Delete Blank Rows in Excel (10 Handy Methods)**

### Formula 2: Delete Blank Rows in Excel Using IF, ROWS, COUNT, INDEX, SMALL, ROW, & ROWS Functions

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.

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$13<>"", ROW(B$5:B$13)),ROWS(B$5:B5))))`

❷ Then press the **ENTER** button to execute the formula.

❸ Finally, draw to **Fill Handle** icon to column **J** first. After that, drag it again to the end of the Math column.

This will keep only the solid rows by removing out all the rows having blank cells in them.

**Formula Breakdown**

**ROW(B$5:B$13)**▶ 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$13<>””,ROW(B$5:B$13)),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 Ricky Ben.**=IF(ROWS(B$5:B5)>COUNTA(B:B),””,INDEX(B:B,SMALL(IF(B$5:B$13<>””,ROW(B$5:B$13)),ROWS(B$5:B5))))**▶ checks for the blanks rows within the entire table and returns rows where there’s no blank cell.

**Read More: Excel VBA: Delete Row If Cell Is Blank (A Complete Guide)**

### Formula 3: Clean Up Blank Rows in Excel Using FILTER Function

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.

❶ Enter the following formula in cell **G5**.

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

❷ After that press the **ENTER** button.

**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.**=FILTER(B5:E14,(B5:B14<>””)*(C5:C14<>””)*(D5:D14<>””)*(E5:E14<>””))**▶ filters out all the blank cells by keeping only the rows without those blank cells.

**Read More: ****Excel Shortcut to Delete Rows (With Bonus Techniques)**

**Similar Readings**

**How to Delete Selected Rows with Excel VBA (A Step-by-Step Guideline)****Delete Alternate Rows in Excel (5 Quick Ways)****How to Delete Infinite Rows in Excel (5 Easy Ways)****Delete Filtered Rows in Excel (5 Methods)****How to Delete Unused Rows in Excel (8 Easy Ways)**

### Formula 4: Omit Blank Rows in Excel Using COUNTBLANK Function

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:

❶ First of all, type the following formula in cell **F5**.

`=COUNTBLANK(B5:E5)`

❷ Then hit the **ENTER** button.

This formula will return a value of **0**, as there are no blank cells in the fifth row.

❸ After that, drag the **Fill Handle** icon to the end of the Blanks column.

After all these steps, we will see the blank cell count for each of the rows.

❹ Now 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 Blanks header.

❻ Uncheck the options other than 0 counts, 4 in this case.

❼ Finally, hit the **OK** command.

These steps will filter out all the blank rows and keep only the complete rows as in the image below:

**Read More: How to Delete Rows in Excel without Affecting Formulas (2 Quick Ways)**

### Formula 5: Exclude Blank Rows in Excel Using IF, AND, & ISBLANK Functions

In this section, we will use the **IF**, **AND**, & **ISBLANK **functions to specify which row is blank and which row is not. Using this function, 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 the Excel worksheet.

Now follow the steps below to do that.

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

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

❷ Press the **ENTER** button.

❸ Then draw the **Fill Handle** icon to the end of the Blanks column.

❹ After that, select the whole data table.

❺ Now 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.

❼ Now uncheck the Blank option from the list.

❽ Finally hit the **OK** command.

After doing all the steps, you will have your data table without having any blank rows as in the picture below:

**Read More: How to Delete Rows in Excel with Specific Text (3 Methods)**

## Things to Remember

📌 Press** CTRL + SHIFT + ENTER** to insert the array formulas.

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

## Conclusion

To sum up, we have discussed 5 formulas to remove blank rows in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website **Exceldemy** to explore more.