For example, consider the following examples: There are multiple ways to skin this data cat in Excel. The method you choose to delete the rows will depend on how your data is structured and what’s the cell value or condition based on which you want to delete these rows. In this tutorial, I will show you multiple ways to delete rows in Excel based on a cell value or a condition.
Filter Rows based on Value/Condition and Then Delete it
One of the fastest ways to delete rows that contain a specific value or fulfill a given condition is to filter these. Once you have the filtered data, you can delete all these rows (while the remaining rows remain intact). Excel filter is quite versatile and you can filter based on many criteria (such as text, numbers, dates, and colors) Let’s see two examples where you can filter the rows and delete them.
Delete Rows that contain a specific text
Suppose you have a data set as shown below and you want to delete all the rows where the region is Mid-west (in Column B).
While in this small dataset you can choose to do delete these rows manually, often your datasets are going to be huge where deleting rows manually won’t be an option. In that case, you can filter all the records where the region is Mid-West and then delete all these rows (while keeping the other rows intact). Below are the steps to delete rows based on the value (all Mid-West records): The above steps first filter the data based on a cell value (or can be other condition such as after/before a date or greater/less than a number). Once you have the records, you simply delete these. Some useful shortcuts to know to speed up the process: In the above example, I had only four distinct regions and I could manually select and deselect it from the Filter list (in steps 5 above). In case you have a lot of categories/regions, you can type the name in the field right above the box (that has these region names), and Excel will show you only those records that match entered text (as shown below). Once you have the text based on which you want to filter, hit the Enter key.
Note that when you delete a row, anything that you may have in other cells in these rows will be lost. One way to get around this is to create a copy of the data in another worksheet and delete the rows in the copied data. Once done, copy it back in place of the original data. Or You can use the methods shown later in this tutorial (using the Sort method or the Find All Method)
Delete Rows Based on a Numeric Condition
Just as I used the filter method to delete all the rows that contain the text Mid-West, you can also use a number condition (or a date condition). For example, suppose I have the below dataset and I want to delete all the rows where the sale value is less than 200. Below are the steps to do this: There are many number filters that you can use in Excel – such as less than/greater than, equal/does not equal, between, top 10, above or below average, etc. Just like the number filters, you can also filter the records based on the date. For example, if you want to remove all the records of the first quarter, you can do that by using the same steps above. When you’re working with Date filters, Excel automatically shows you relevant filters (as shown below).
While filtering is a great way to quickly delete rows based on a value or a condition, it has one drawback – it deletes the entire row. For example, in the below case, it would delete all the data which is to the right of the filtered dataset. What if I only want to delete records from the dataset, but want to keep the remaining data intact. You can’t do that with filtering, but you can do that with sorting.
Sort the Dataset and Then Delete the Rows
Although sorting is another way to delete rows based on value, but in most cases, you’re better off using the filter method covered above. This sorting technique is recommended only when you want to delete the cells with the values and not the entire rows. Suppose you have a dataset as shown below and you want to delete all the records where the region is Mid-west. Below are the steps to do this using sorting: The above steps would delete all the records where the region was Mid-West, but it doesn’t delete the entire row. So, if you have any data on the right or left of your dataset, it will remain unharmed. In the above example, I have sorted the data based on the cell value, but you can also use the same steps to sort based on numbers, dates, cell color or font color, etc.
Find and Select the Cells Based on Cell Value and Then Delete the Rows
Excel has a Find and Replace functionality that can be great when you want to find and select cells with a specific value. Once you have selected these cells, you can easily delete the rows. Suppose you have the dataset as shown below and you want to delete all the rows where the region is Mid-West. Below are the steps to do this: The above steps would delete all the cells where the region value is Mid-west.
Delete All Rows With a Blank Cell
In case you want to delete all the rows where there are blank cells, you can easily do this with an inbuilt functionality in Excel. It’s the Go-To Special Cells option – which allows you to quickly select all the blank cells. And once you have selected all the blank cells, deleting these is super simple. Suppose you have the dataset as shown below and I want to delete all the rows where I don’t have the sale value. Below are the steps to do this: The above steps would select all the cells that are blank in the dataset. Once you have the blank cells selected, right-click on any of the cells and click on Delete.
In the Delete dialog box, select the ‘Entire row’ option and click OK. This will delete all rows that have blank cells in it.
If you’re interested in learning more about this technique, I wrote a detailed tutorial on how to delete rows with blank cells. It includes the ‘Go To Special’ method as well as a VBA method to delete rows with blank cells.
Filter and Delete Rows Based On Cell Value (using VBA)
The last method that I am going to show you include a little bit of VBA. You can use this method if you often need to delete rows based on a specific value in a column. You can add the VBA code once and add it your Personal Macro workbook. This way it will be available for use in all of your Excel workbooks. This code works the same way as the Filter method covered above (except the fact that this does all the steps in the backend and save you some clicks). Suppose you have the dataset as shown below and you want to delete all the rows where the region is Mid-West. Below is the VBA code that will do this. The above code uses the VBA Autofilter method to first filter the rows based on the specified criteria (which is ‘Mid-West’), then select all the filtered rows and delete it. Note that I have used Offset in the above code to make sure my header row is not deleted. Before deleting the rows, it will show you a prompt as shown below. I find this useful as it allows me to double-check the filtered row before deleting. In case your data is in an Excel Table, use the below code to delete rows with a specific value in it: Since VBA considers Excel Table as a List object (and not a range), I had to change the code accordingly. Where to Put the VBA code? This code needs to be placed in the VB Editor backend in a module. Below are the steps that will show you how to do this: Once you have the code in the VB Editor, you can run the code by using any of the below methods (make sure you have the selected any cell in the dataset on which you want to run this code): You can read all about how to run the macro code in Excel in this article. Note: Since the workbook contains a VBA macro code, you need to save it in the macro-enabled format (xlsm). You may also like the following Excel tutorials:
How to Delete Every Other Row in Excel How to Delete a Pivot Table in Excel How to Delete Every Other Row in Excel How to Move Rows and Columns in Excel (The Best and Fastest Way) Highlight Rows Based on a Cell Value in Excel (Conditional Formatting) How to Insert Multiple Rows in Excel Number Rows in Excel How to Quickly Unhide COLUMNS in Excel Hide Zero Values in Excel
