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.

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.

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

• After that Formula for the Science column.

• After that for the Biology Column.

• Finally the end result will be like this.

📕 Read More: 9 Ways to Delete Unused Rows in Excel

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.

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

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.

• Copy down the formula along the column for all the results.

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

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.

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

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.

Follow the steps below.

⬇️⬇️ STEPS ⬇️⬇️

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

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

📝 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

• 5 Ways to Delete Every nth Row in Excel