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.


📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.


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.

excel remove blank rows formula

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<>””))

Using the filter function to excel remove blank rows using formula

  • After that Press Enter to get the results.

Results for the Filter function to create a new table

  • 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<>””))

Using the filter function to excel remove blank rows using formula

  • After that Formula for the Science column.

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

Using the filter function to excel remove blank rows using formula

  • After that for the Biology Column.

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

Using the filter function to excel remove blank rows using formula

  • Finally the end result will be like this.

Creating a new table to using filter function excel remove blank rows using formula

📕 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)

Using COUNTBLANK function to excel remove blank rows using formula

  • Copy down the formula along the column.

Detecting the rows with blank cells excel remove blank rows using formula

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

Filter tool

  • You’ll see dropdowns like this.

Filtering the Table

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

Filtering out the cells with values only to excel remove blank rows using formula

  • So here’s what we are gonna see.

Filtered out dataset to excel remove blank rows using formula

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.

Follow the instructions below.

⬇️⬇️ 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

Formula to detect blank cells excel remove blank rows using formula

Detecting the blank cells to excel remove blank rows using formula

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

filtered data excel to remove blank rows using formula

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.

Follow the steps below.

⬇️⬇️ 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.

USing IF ROWS COUNTA INDEX ROW and SMALL function to excel remove blank rows using formula

  • Press Enter to get the result.

Recreating a table for excel remove blank rows formula

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

Using fill tool to copy along row for excel remove blank rows formula

  • The results will be like this.

Copying along row excel to remove blank rows formula

  • After that do the same along the column.

using fill tool to copy along column to excel remove blank rows formula

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

excel remove blank rows using formula

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, ROWINDEX 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.

Follow the steps below.

⬇️⬇️ 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.

excel remove blank rows formula using IFERROR INDEX SMALL IF ROW and ROWS functions

  • Press Enter to get the result.

Recreating a table for excel remove blank rows formula

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

Using fill tool to copy along row for excel remove blank rows formula

  • The results will be like this.

Copying along row excel to remove blank rows formula

  • After that do the same along the column.

using fill tool to copy along column to excel remove blank rows formula

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

excel remove blank rows using formula


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.

Deleting rows

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

Deleting Rows by selecting cells

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

Custom Deleted Blank cell rows result


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.

Go to special option

  • After that select the Blanks option and press OK.

Go to Special option

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

Selecting and Deleting rows

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

Deleting infinite number of rows

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

Blank cells reduced


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


📝 Takeaways from This Article

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


Conclusion

Firstly, I hope you were able to use the techniques I demonstrated in this remove blank rows with formula in Excel lesson. As you can see, there are a lot of options on how to do this. However, decide deliberately on the approach that best addresses your circumstance. Most importantly I advise repeating the steps if you become confused in any of the steps if you get stuck. However, practice on your own after taking a look at the Excel file in the practice workbook I’ve provided above because practice makes a man perfect. Above all, I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks. However, if you have any problem regarding Excel spit it in the comment box. The Excelden crew is always available to answer your questions. In conclusion, keep educating yourself by reading. For more Excel-related articles please visit our website Excelden.com.


Related Article

(Visited 117 times, 1 visits today)
Nashid

Nashid

Hello there. I am Nashid, an Engineer. I Love music, traveling, sports and gaming. I have recently grown interest in DATA SCIENCE and wish to build a career over it. Excel is one of the greatest modern digital creation of human. Life simply gets easier. My Articles are meant to lead you to that easier life with efficiency. If you need any kind of help regarding my articles even anything else try contacting me on social platforms of which I've provided. Keep learning. Wish me luck and Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo