How to Delete Multiple Rows in Excel Using Formula?

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

We will be applying 5 methods on an Excel document that 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).

How to Delete Multiple Rows in Excel Using Formula: 5 Methods

1. Using a 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.

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 as Yes, the others are marked as No.

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

Now, select Range B4:F14.

Open the 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 the Data tab >> deselect Filter.

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

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

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

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 on 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 the Data tab >> deselect Filter. You can find the process in the previous method 1.

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

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

The OR function checks if any of the arguments are 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)

Here, the OR function checks the arguments in cells D6 and E6 simultaneously. If none of them match the condition, it returns FALSE, otherwise, 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 the 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.

4. Utilizing EXACT Function to Delete Multiple Rows Using Formula

The 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 by following these steps:

Type the following formula in cell F5

=EXACT(“Porsche”,C5)

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.

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 that 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 that contain more than 250000 dollars.

Save the code and go back to your worksheet.
Now select the range B5:E14
Open the 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 that 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.

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.

Download Practice Workbook

Delete Multiple Rows Using Formula.xlsm

Conclusion

This document will show you five different methods to delete multiple rows in Excel using formulas. 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.

<< Go Back to Delete Rows | Rows in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!