# 5 Ways to Remove Blank Rows Using Formula in Excel

In this lesson, Iโll demonstrate 5 different approaches to remove blank rows using formula in Excel. Moreover, these techniques will enable you to solve the problem that led you here. However, you will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.

## Learn to Remove Blank Rows Using Formula in Excel with These 5 Useful Methods

Here in this article, we will learn to remove blank rows using formula in Excel using different approaches. To be exact, Iโve provided a total of 5 methods down below.

Method 1

### 1. Using FILTER Function

Here, we have the dataset for students and their marks from different subjects. We will be learning how to remove blank rows by using the FILTER function.

The Filter function takes in a range and a bunch of conditions and returns the filtered version of the range depending on the conditions of the argument. Which is perfect for what we are trying to achieve. However if we can remove the blank row by filtering, we can easily recreate the dataset with this formula in Excel.

Follow the instructions below to see how we can do that.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• To use this method create a separate table to remove blank rows.
• Now copy the following formula in the newly created table.

=FILTER(B6:B16,(B6:B16<>โโ)*(C6:C16<>โโ)*(D6:D16<>โโ)*(E6:E16<>โโ))

• After that Press Enter to get the results.

• Do the same for the rest of the columns the formulas will be respectively.
• For The History column:

=FILTER(C6:C16,(B6:B16<>โโ)*(C6:C16<>โโ)*(D6:D16<>โโ)*(E6:E16<>โโ))

• After that Formula for the Science column.

=FILTER(D6:D16,(B6:B16<>โโ)*(C6:C16<>โโ)*(D6:D16<>โโ)*(E6:E16<>โโ))

• After that for the Biology Column.

=FILTER(E6:E16,(B6:B16<>โโ)*(C6:C16<>โโ)*(D6:D16<>โโ)*(E6:E16<>โโ))

• Finally the end result will be like this.

๐ Read More: 9 Ways to Delete Unused Rows in Excel

Method 2

### 2. Utilizing COUNTBLANK Function

In this section, we will be using the COUNTBLANK function to count the blank rows and then delete them. The function takes in a range as an argument and returns the number of blank cells in the range. So we are going to use that to our advantage.

Follow the instructions below for more details.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• Firstly, we need to create a new column to detect the blank cells using the COUNTBLANK.
• Copy the following formula in the new column.

=COUNTBLANK(B6:E6)

• Copy down the formula along the column.

• After that select the whole table including headers. Select the Filter tool from the Data Tab of the ribbon.

• Youโll see dropdowns like this.

• Now select the dropdown of the newly created column Blank Cells in Row. After that untick the 4 option as we donโt want the rows with blank cells which have the value 4.

• So hereโs what we are gonna see.

Method 3

### 3. Combining IF, AND, and ISBLANK Functions

Here we will use a combination of 3 functions which are IF, AND and ISBLANK to remove blank rows by a formula.

Individually, the IF function takes in three arguments- a condition as first, a second to be printed when the condition is TRUE, and another one in case the condition is FALSE. The AND function returns TRUE when all of the arguments it is taking in are TRUE. Otherwise, it returns a FALSE. Finally the ISBLANK just checks if a cell or a value it is taking in as an argument is blank or not and returns a boolean value depending on it.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• Firstly, create a new column for the application of the formula.
• Copy the following formula in the newly created column.

=IF(AND(ISBLANK(B6),ISBLANK(C6),ISBLANK(D6),ISBLANK(E6)),โAvailableโ,โNot-Availableโ)

๐จ ย  Formula Breakdown

๐ย  ISBLANK(B6)

The ISBLANK function finds if the cell is blank or not returns TRUE if Blank and FALSE if not.

๐ย  AND(ISBLANK(B6),ISBLANK(C6),ISBLANK(D6),ISBLANK(E6))

This part sets the conditions as a single condition..

๐ย  IF(AND(ISBLANK(B6),ISBLANK(C6),ISBLANK(D6),ISBLANK(E6)),โAvailableโ,โNot-Availableโ)

Here this part says that if the condition AND(โฆ.) is TRUE then return Available and if not return Not-Available

• Finallyย Now we can filter out the Blank Cells column like the previous approach and only show the data with Not-Available Blank cells.

Method 4

### 4. Recreate Datasets with COUNTA, INDEX, and ROW Functions

In this method, we have to recreate the table by inserting the Headers only and the dataset will be filled by the formula. We are using ย IF, ROW, COUNTA, INDEX and SMALL functions for creating the formula.

Individually, the IF function takes in three arguments- a condition as first, a second to be printed when the condition is TRUE, and another one in case the condition is FALSE. The AND function returns TRUE when all of the arguments it is taking in are TRUE. Otherwise, it returns a FALSE. The COUNTA function tallies the number of cells holding any sort of data.ย When applied to a table or range, the INDEX function retrieves either the actual value or a reference to that value. The SMALL function determines and returns the k lowest value in a given dataset. Finally the ROW function counts the row number.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• First, select the first cell of the newly created table and insert the following formula.

=IF(ROWS(B\$6:B6)>COUNTA(B:B),โโ,INDEX(B:B,SMALL(IF(B\$6:B\$16<>โโ,ROW(B\$6:B\$16)),ROWS(B\$6:B6))))

๐จ ย  Formula Breakdown

๐ย  ROW(B\$6:B\$16)

This part returns the individual row numbers of the array.

๐ย  ROWS(B\$6:B6)

This returns the number of rows between the array B\$6:B6.

๐ย  INDEX(B:B,SMALL(IF(B\$6:B\$16<>โโ,ROW(B\$6:B\$16)),ROWS(B\$6:B6)))

This part examines the 6th row for empty cells. Because there arenโt any empty cells in the 6th row, the first value of the 6th row is provided, that is Rick.

๐ย  IF(ROWS(B\$6:B6)>COUNTA(B:B),โโ,INDEX(B:B,SMALL(IF(B\$6:B\$16<>โโ,ROW(B\$6:B\$16)),ROWS(B\$6:B6))))

So the whole formula actually scans to see if there are any rows in the database that are blank and delivers those rows in which there are no blank cells.

• Press Enter to get the result.

• After that use the fill tool to copy the formula along the row by dragging it to the right side of the table.

• The results will be like this.

• After that do the same along the column.

• As you can see, the final result has no blank cells.

Method 5

### 5. Recreate Using IFERROR, INDEX, and ROW Functions

In this method, we are going to recreate the table by inserting the Headers only and the dataset will be filled by the formula. We are using IFERROR, ROW,ย INDEX and SMALL functions for creating the formula. If a formula equates to an error, the IFERROR function delivers another value you declare; alternatively, it provides the formulaโs output. When applied to a table or range, the INDEX function retrieves either the actual value or a reference to that value. The SMALL function determines and returns the k lowest value in a given dataset. Finally the ROW function counts the row number.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• First, select the first cell of the newly created table and insert the following formula.

=IFERROR(INDEX(B:B,SMALL(IF(B\$6:B\$16<>โโ,ROW(B\$6:B\$16)),ROWS(B\$6:B6))), โโ)

๐จ ย  Formula Breakdown

๐ย  ROW(B\$6:B\$16)

This part returns the individual row numbers of the array.

๐ย  ROWS(B\$6:B6)

This returns the number of rows between the array B\$6:B6.

๐ย  SMALL(IF(B\$6:B\$16<>โโ,ROW(B\$6:B\$16)),ROWS(B\$6:B6))), โโ)

The SMALL function determines the least cell location in the datasheetโs 6th row.

๐ย  IFERROR(INDEX(B:B,SMALL(IF(B\$6:B\$16<>โโ,ROW(B\$6:B\$16)),ROWS(B\$6:B6))), โโ)

This part identifies any empty cells in the 6th row. If not, the entire row is delivered.

• Press Enter to get the result.

• After that use the fill tool to copy the formula along the row by dragging it to the right side of the table.

• The results will be like this.

• After that do the same along the column.

• In conclusion you can see, the final result has no blank cells.

## How to Delete Rows in Excel Without Formula

Say we have a row that we donโt want anymore so we need to delete it. Follow the instructions to have it done.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• First, select a cell of the row you want to delete. If you want to delete multiple rows then select by pressing Ctrl for multiple selection. After that, right click on it and select the Delete option.

• Select Entire row option to delete the whole row. After that, press OK to continue.

• The results are shown below as you can see the rows of the selected cells are gone.

## How to Delete Rows That Go on Forever in Excel

Here, you will learn how to delete infinite numbers of rows. Say we have to delete the blank cells that are uncountable in a dataset. Follow the instructions below.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• First select the whole dataset. After that go to the Find & Select tool from the Home tab of the ribbon and select the Go To Special.

• After that select the Blanks option and press OK.

• Now all the cells that have blank cells are selected. Now right-click on any of them and select the Delete.

• Select the Entire row option to delete the whole row. After that, press OK to continue.

• The results are shown below as you can see all the rows of the selected cells are gone.

## ๐ Important Notes

`๐๏ธ`ย  Use shortcuts to reduce the time for instance F4 for absolute value

`๐๏ธ`ย  While writing formulas carefully see the suggestions and donโt forget the commas and parentheses.

`๐๏ธ`ย  Be careful while defining criteria to be accurate as the dataset.

`๐๏ธ`ย  While copying any formula try pasting it in the formula bar instead of the cell itself.

`๐`ย  The article demonstrated different functions to build up formulas for removing blank rows in Excel.

`๐`ย  In the first section, we showed how to use FILTER function to remove blank rows in an Excel dataset.

`๐`ย  Then, we demonstrated how to use the COUNTBLANK and a combination of IF, AND, and ISBLANK function to detect the rows having blank cells and remove the corresponding rows in Excel.

`๐`ย  After that we used a method where we recreated the dataset using a combination of functions and created two formulas.

`๐`ย  Finally, we properly learned how to delete rows without formula and rows that go on forever.