Delete all non-formatted cells - Excel

Question

I want to delete all cells which ARE NOTred in Excel.

I have used conditional formatting to highlight all email addresses in a spreadsheet, I know I can sort each column on cell colour, but this spreadsheet has 41 rows, and I will be building hundreds over the coming months.

Basically only want to keep red cells.

Thanks

Answer 1

Hello Chris@_216. I would go to the Data Tab > Turn of Filtering > Use the drop down for the column that has Conditional Formatting applied, and select that color. One screenshot attached.

Answer 2

How about filter to select all cells which do not contain email addresses then delete the visible rows only?

Filter on "does not contain" @

Gord

Answer 3

The issue is that each column has a different number of email addresses in (all email addresses are red).

I can sort each column individually as below, but it takes a lot of time. If I filter Column A, it loses email addresses from the rest of the columns.

If not, can I copy and past search results, I would search for cells containing "@" then copy and paste them.

Thank you

Answer 4

You can keep the conditional formatting turned on if you want, but now the issue is the emails go across instead of down. I would add a helper column in Excel. In my example in the screenshot, it is column F. The formula to find email addresses in a row is =COUNTIF(B2:C2,"*"&"@"&"*") I'm looking for the @ symbol in that formula.

So now you would filter and look for >=1