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. That article showed how to use the Find command, to get a list of cells that contain a specific word. Then, delete the rows for those cells.
It’s a handy trick, but won’t work to select cells that are colored with conditional formatting.
Use a Filter
Instead, you could use a filter to select the highlighted cells, and then delete the filtered 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.
A named table has filters in the heading row by default, but if those have been turned off, you can quickly turn them back on:
- Click any cell in the named table
- On the Ribbon’s Data tab, click the Filter button.
Delete Filtered Rows
Assuming that your list is in a named Excel Table, follow these steps to select the highlighted cells, and delete those rows.
- Make a backup copy of your file first — just to be safe.
- Click the arrow in the heading for the column where you applied the conditional formatting
- In the drop down, click Filter by Color, and select the color that you used
- Select the colored cells, and on the Ribbon’s Home tab, click the arrow under the Delete command
- Click on Delete Table Rows.
Check the Results
As soon as you delete the rows, clear the filter
- Click the filter arrow in the column heading, and click the Clear Filter command
- Then, check that all the other rows are still okay, and the colored cells have been deleted
If the list doesn’t look right, click the Undo button a couple of times, or press Ctrl + Z to undo the deletion.
_________________________
Thank you, Really helpful technique!
I want to clear the cells that applied a conditional formatting color to but I don’t want to delete the cells. I just want to clear the cell of any text. The sheet is very large though so it would take a very long time to manually go and clear each cell.
Same. while for me, I have a very large dataset and I want to isolate ONLY the cells with conditional formatting and delete the rest. Filter function keep loading till no end. btw my data is about 1.2M.
Any suggestion will be very appreciated.
It is amazing how simple techniques save us so much time. Thanks for this 🙂