Click on the Filter icon in the Region header cell (this is a small downward-pointing triangle icon at the top-right of the cell).This will apply filters to all the headers cells in the dataset In the ‘Sort & Filter’ group, click on the Filter icon.Select any cell in the data set from which you want to delete the rows.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).īelow are the steps to delete rows based on the value (all Mid-West records): 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. 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). Let’s see two examples where you can filter the rows and delete them. Once you have the filtered data, you can delete all these rows (while the remaining rows remain intact).Įxcel filter is quite versatile and you can filter based on many criteria (such as text, numbers, dates, and colors) One of the fastest ways to delete rows that contain a specific value or fulfill a given condition is to filter these. Filter and Delete Rows Based On Cell Value (using VBA)įilter Rows based on Value/Condition and Then Delete it.Find and Select the Cells Based on Cell Value and Then Delete the Rows.Sort the Dataset and Then Delete the Rows.Delete Rows Based on a Numeric Condition.Delete Rows that contain a specific text.Filter Rows based on Value/Condition and Then Delete it.If the list doesn’t look right, click the Undo button a couple of times, or press Ctrl + Z to undo the deletion. Then, check that all the other rows are still okay, and the colored cells have been deleted.Click the filter arrow in the column heading, and click the Clear Filter command.Select the colored cells, and on the Ribbon’s Home tab, click the arrow under the Delete commandĪs soon as you delete the rows, clear the filter.In the drop down, click Filter by Color, and select the color that you used.Click the arrow in the heading for the column where you applied the conditional formatting.
Make a backup copy of your file first - just to be safe.On the Ribbon’s Data tab, click the Filter button.Īssuming that your list is in a named Excel Table, follow these steps to select the highlighted cells, and delete those rows.If you’re working with a list in Excel, it’s best to convert the list to a named Excel Table, unless you have a compelling reason that you can’t do that.Ī named table has filters in the heading row by default, but if those have been turned off, you can quickly turn them back on: Instead, you could use a filter to select the highlighted cells, and then delete the filtered rows. It’s a handy trick, but won’t work to select cells that are colored with conditional formatting.
That article showed how to use the Find command, to get a list of cells that contain a specific word.
If you’ve highlighted cells with conditional formatting, what’s a quick way to delete the rows those cells are in? Someone asked that question on one of my old blog posts last week.