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.

_________________________

You may also like...

5 Responses

  1. MF says:

    We may use Go to -> Special -> Conditional Formats
    to delete cells/rows with conditional formats. :)
    Cheers,

  2. Khushnood Viccaji says:

    Hi Debra, my comment is about using the Find cells command to select and delete rows containing the matching cells.

    Instead of using the Find command, I apply an Advanced Filter feature to filter records containing the specific text.

    Once the records are filtered, it’s just a matter of selecting the visible cells / rows and deleting them.

    Also, in case your table is quite large (say 50000+ records), then it can take a lot of time to delete the rows and update the screen.
    In such a case, after selecting the visible cells as above, I clear the filter *without de-selecting the cells*, and then delete the rows. It’s much faster this way.

  3. Jigs Gaton says:

    I think Khuswood’s suggestion is vital to this tip, and I sure wish I had read the comments before trying. In Windows 10 with Office 2013 a large list took forever, and there is no indication on what is going on… just looks like a hang!

  4. Anita X says:

    I tried this multiple times and Excel crashes. Once the Excel recovers, the first row containing the conditional formatting is still there.

Leave a Reply

Your email address will not be published. Required fields are marked *