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

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.

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.

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

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

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

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.

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.

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.

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

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

• 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

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.

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

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

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:

• How to Delete Hidden Rows in Excel (3 Methods)
• Excel Shortcut to Delete Rows (With Bonus Techniques)
• How to Remove Empty Rows in Excel (11 Methods)
• Delete an Excel Row If a Cell Contains Specific Values (3 Methods)
• How to Delete Rows in Excel That Go on Forever (4 Easy Ways)

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

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.

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

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

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

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.

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

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

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

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

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.

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

• How to Delete Empty Rows at Bottom in Excel (7 Ways)
• Filter Data and Delete Rows with Excel VBA (5 Examples)
• How to Delete Duplicate Rows in Excel with VBA (8 Effective Ways)
• Delete Unfiltered Rows in Excel Using VBA (4 ways)
• How to Delete Row Using Macro If Cell Contains 0 in Excel (4 Methods)