How to Delete Multiple Rows in Excel Using Formula (5 Methods)

This article gives a brief description of how to delete multiple rows in Excel using formula. There are several different ways to eliminate rows by using formula or commands. If an Excel document contains a huge set of data, it will be easy to terminate various unwanted rows using formula in that case.

We will be applying 5 methods on an Excel document which contains some information about different sports cars. To describe the processes we have taken 4 columns these are Car Name, Company Name, Price, and Max Speed (Mph).

Sample dataset of How to Delete Multiple Rows in Excel Using Formulas


Download Practice Workbook


5 Methods to Delete Multiple Rows in Excel Using Formula

1. Using Formula of IF Function to Delete Multiple Rows in Excel 

We can check the desired condition using the IF function. Suppose, you want to buy a car that has a maximum speed above or equal to 230 miles per hour and you don’t want other cars on the screen. You can select these cars following the steps described below.

Steps:

  • Type this formula in cell F5.

=IF(E5>=230,"Yes","No")

Here, the IF function checks the condition in which the value of the E5 cell is greater than or equal to 230 mph or not. It returns Yes when the cell value meets the condition. Otherwise, it returns No.

  • Then hit ENTER and you will see the output in it.

Using If Function to Delete Multiple Rows in Excel

  • Now, use the Fill Handle to AutoFill the rest of the cells.
  • Cars that have a maximum speed above or equal to 230 mph are marked Yes, the others are marked as No.

Here, I will apply a Filter to delete multiple rows.

  • Now, select Range B4:F14.

 deleting multiple rows using if function

  • Open Home tab >> select Sort and Filter
  • You also can use CTRL + SHIFT + L or Open Data tab >> from Sort and Filter >> select Filter.

  •  Click on the marked arrow (in the following figure), unmark Yes, and then press ENTER or click Ok. You will see the information of cars which have a maximum speed less than 230 mph.

delete multiple rows using if function

  • The following figure will show up.

  • You need to select the range B5 to F12. Then press CTRL + –, You will see a dialog box that will show you a warning message.
  • Then, click OK.

using if function to delete multiple rows

After that, all the selected rows will be deleted.

Now to see the hidden rows, remove the Filter from the dataset.

  • You can use CTRL + SHIFT + L or from Data tab >> deselect Filter.

how to delete multiple rows using if functoin

Now the rows which contained information of cars with a maximum speed of less than 230 mph are deleted.

Read More: How to Delete Multiple Rows in Excel (3 Methods)


2. Applying Formula of Excel AND Function to Delete Multiple Rows

The Excel AND function requires different conditions to be met simultaneously. If any of the conditions don’t match, it returns FALSE. Otherwise, it returns TRUE.

Suppose you want to buy a car that can run at a maximum speed of more than 220 mph but it will cost you less than or equal to 250 thousand dollars. You can make a list of them following the process given below.

  • Type the following formula in cell F5.
=AND(D5<=250000,E5>220)

deleting multiple rows using formula

Here, the AND function checks the condition given in cells D5 and E5 simultaneously. If any of them doesn’t match the condition, it returns FALSE, else it returns TRUE.

  • Then hit ENTER and you will see the output in it.

Deleting multiple rows using and function

  • Now, use the Fill Handle to AutoFill the rest of the cells.
  • Cars that can run at a maximum speed of more than 220 mph and cost less than or equal to 250 thousand dollars are marked TRUE, the others are marked as FALSE.

Deleting multiple rows using and function

  • Now, select Range B4:F14.
  • Open Data tab >> from Sort and Filter >> select Filter. This process was shown in the previous method 1.
  • Click on the marked arrow (in the following figure), unmark TRUE, and then press ENTER or click Ok. You will see the information of cars that don’t have a maximum speed of more than 220 mph and cost more than 250 thousand bucks.

  • The following figure will show up. You need to select the range B5 to F14 in that figure. Then press CTRL + –, You will see a dialog box that will show you a warning message.
  • Then, click OK.

Deleting multiple rows using and function

  • This will delete all the selected rows. Now to see the hidden rows, remove the Filter from the dataset.
  • You can use CTRL + SHIFT + L or from Data tab >> deselect Filter. You can find the process in the previous method 1.

Now this action deletes the rows which contained cars having a maximum speed less than or equal to 220 mph and price more than 250 thousand dollars.

Read More: How to Use Macro to Delete Rows Based on Criteria in Excel (3 Ways) 


3. Inserting Formula of OR Function to Delete Multiple Rows in Excel

The Excel OR function checks if any of the arguments is true. If all the arguments do not match the condition, it returns FALSE. Otherwise, it returns TRUE.
Suppose you want to buy a car that can run at a maximum speed of more than 220 mph or it will cost you less than or equal to 230 thousand dollars. You can make a list of them following the process given below.

  • Type the following formula in cell F5
=OR(D6<=230000,E6>220)

Deleting multiple rows using or function

Here, the OR function checks the arguments in cells D6 and E6 simultaneously. If none of them doesn’t match the condition, it returns FALSE, else it returns TRUE.

  • Then hit ENTER and you will see the output in it.

Deleting multiple rows using or function

  • Now, use the Fill Handle to AutoFill the rest of the cells like previous methods.
  • This operation marks the cars that can run at a maximum speed of more than 220 mph or cost less than or equal to 230 thousand dollars as TRUE. It also marks other cars as FALSE.

  • Now, select the Range B5:F15
  • Open Data tab >> from Sort and Filter >> select Filter. This process was shown in the previous method 1.
  • Click on the marked arrow (in the following figure), unmark TRUE, and then press ENTER or click Ok. You will see the information on cars that don’t have a maximum speed of more than 220 mph and cost more than 230 thousand bucks. You can find the steps in the previous method. So I will go straight to the result.

Deleting multiple rows using or function

  • You need to select the range B8 to F13 in that figure. Then press CTRL + –, You will see a dialog box that will show you a warning message.
  • Then, click OK.

  • After that, all the selected rows will be deleted. Now to see the hidden rows, remove the Filter from the dataset.
  • You can use CTRL + SHIFT + L or from Data tab >> deselect Filter. The process was shown in the previous method 1. So only the result will be shown here.

Deleting multiple rows using or function

Now this command terminates the rows containing cars having a maximum speed less than or equal to 220 mph and price more than 230 thousand dollars.

Read More: How to Delete Rows in Excel without Affecting Formulas (2 Quick Ways)


Similar Readings:


4. Utilizing EXACT Function to Delete Multiple Rows Using Formula

The Excel EXACT function checks two strings if they are the same. If they don’t match, it returns FALSE. Otherwise, it returns TRUE. The EXACT function is case sensitive.

Suppose you want to buy a car manufactured by Porsche. You can make a list of them following these steps:

  • Type the following formula in cell F5
=EXACT(“Porsche”,C5)

 deleting multiple rows using formula

Here, the EXACT function compares the string cell C5 with Porsche. If they match, it returns TRUE, else it returns FALSE.

  • Then hit ENTER and you will see the output in it.

Deleting multiple rows using exact function

  • Now, use the Fill Handle to AutoFill the rest of the cells like previous methods.
  • Cars which are manufactured by Porsche are marked TRUE, the others are marked as FALSE.

Deleting multiple rows using exact function

  • Now, select Range B4:F14.
  • Open Data tab >> from Sort and Filter >> select Filter. This process was shown in the previous method 1.
  • Click on the marked arrow, unmark TRUE and then press ENTER or click Ok. You will see information about cars that are not manufactured by Porsche. You can find the steps in the previous method. So I will go straight to the result.

  • You need to select the range B5 to F14 in that figure. Then press CTRL + –, You will see a dialog box that will show you a warning message.
  • Then, click OK.

Deleting multiple rows using exact function

  • After that, all the selected rows will be deleted. Now to see the hidden rows, remove the Filter from the dataset.
  • You can use CTRL + SHIFT + L or from the Data tab >> deselect Filter. The process was shown in the previous method. So only the result will be shown here.

 delete multiple rows using formula in Excel

Now the command removes the rows that contained cars that Porsche did not manufacture.

Read More: How to Delete Multiple Rows in Excel Using Formula (5 Methods)


5. Using Condition in VBA Code to Delete Multiple Rows

You can also use Visual Basic Application (VBA) code to delete multiple rows by using the conditional formula in VBA. Excel VBA is the programming language of Microsoft for Excel. You can make codes in VBA and execute them using Excel.
Suppose, you don’t want to buy a car which has a price of more than 250 thousand bucks. So you want them out of your table in Excel. Let’s see the process.
To write code in VBA, first, open the Developer tab and then select Visual Basic.

Delete multiple rows using Formula in Excel VBA

Then, it will open a new window of Microsoft Visual Basic for Applications.
Now, open Insert >> select Module.

Deleting multiple rows using VBA

  • Type the following code in the VBA Module.
Sub Del_Row()
For x = 1 To Selection.Rows.Count
For y = 1 To Selection.Rows.Count
If Selection.Cells(y, 3) > 250000 Then
Rows(y + 4).EntireRow.Delete
End If
Next y
Next x
End Sub

Here, We’ve created a Sub procedure Del_Row()  x and y are variables to count rows. We used a nested For loop to detect a cell of column no 3 if it is greater than 250000 for that we used the IF statement. Then used the EntireRow property to select that row and deleted the row using the Delete method.
For example, when y = 1, it will select the cell which is in row number 1 and column number 3 of the table you select. In this case, it will take the D5 cell to consider. Then it will check all the cells in column 3 and delete the rows which contain more than 250000 dollars.

  • Save the code and go back to your worksheet.
  • Now select the range B5:E14 
  • Open Developer tab >> select Macros.

Deleting multiple rows using VBA

  • You will see the following dialog box, select the Macro name Del_Row.
  • Then click Run.

how to delete multiple rows using formula

  • Now, you will see the rows containing cars which have prices less than or equal to 250 thousand dollars only. This command deletes the rows that had cars with prices more than this amount.

Deleting multiple rows using VBA

Read More: VBA to Delete Every Other Row in Excel (6 Criteria)


Things to Remember

  • Do not use the DELETE button on your keyboard to delete rows after filtering. Follow the steps given in this document.
  • Select the range according to your code while using VBA. Otherwise, it will show you unexpected results and do the process from the start as you can’t undo it while using VBA.

Practice Section

Here I have given the dataset of the Sports Car Information. You can practice on your own using the following methods I have given in this document. You can download it from the link given at the beginning of this document.

how to delete multiple rows in excel using formula


Conclusion

This document will show you five different methods to delete multiple rows in Excel using formula. There are some easy ways to do it manually. But if you have a lot of information like 100 or 200 or more rows, then it will be tiresome to manually delete the rows you don’t want. Based on your condition, you can narrow down the information you want following these methods. If you have any other ideas in your mind or have any questions, please leave them in the comment box.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo