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.