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.

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

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**

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

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

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

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.

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