Delete Rows With Conditional Formatting Color

Delete Rows With Conditional Formatting Color

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:

  1. Click any cell in the named table
  2. On the Ribbon’s Data tab, click the Filter button.

exceltablefilters01

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

filter by conditional format color

  • Select the colored cells, and on the Ribbon’s Home tab, click the arrow under the Delete command
  • Click on Delete Table Rows.

exceltablefilters03

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

exceltablefilters04

  • 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.

_________________________

4 thoughts on “Delete Rows With Conditional Formatting Color”

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

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

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.