Formula to Remove Blank Rows in Excel (5 Examples)

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!


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:

Remove Blank Rows in Excel Using IFERROR, INDEX, SMALL, IF, ROW, & ROWS Functions

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.

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.

Delete Blank Rows in Excel Using IF, ROWS, COUNT, INDEX, SMALL, ROW, & ROWS Functions

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.

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.

Clean Up Blank Rows in Excel Using FILTER Function

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.

Formula 4: Omit Blank Rows in Excel Using COUNTBLANT Function

We use the COUNTBLANT 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.

Omit Blank Rows in Excel Using COUNTBLANT Function

❹ 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:


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.

Exclude Blank Rows in Excel Using IF, AND, & ISBLANK Functions

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

Remove blank rows using formula and filter

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


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.

Mrinmoy

Hi! This is Mrinmoy. I write about Excel and VBA stuff for Exceldemy. I have my graduation completed from Khulna University of Engineering & Technology, Bangladesh. I have an immense interest in Datascience and Machine Learning. I like to play guitar and watch Tedtalks in my leisure time. I used to be an indie filmmaker and a wedding photographer. I'm liberal to any kind of constructive criticism and inclined towards expanding my learnings.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo