Conditional formatting making spreadsheet slow

Hi,

I do some data reporting and the raw data reports are not very good, so it requires some additional work in excel to reach our final numbers.

I am using Excel 2010, Win 7 64 bit.

My spreadsheet is one tab, no formulas, and around 20k rows. I have one column with incident numbers in it.

I would like to use conditional formatting to highlight duplicate values for the incident number. Here is where I run into problems. What I am trying to do is highlight the duplicates, then go to my date closed column, and delete all of the incidents that have blanks for the date closed.

I cannot use the "remove duplicates" function as there will be two identical incident numbers, one has the closed date and the other doesn't, and I need to delete the one without the date only.

In theory, I should be able to highlight duplicates on my spreadsheet, set filters to only show the red shaded cells, then use a filter to only select the blanks for date closed, then manually delete the records.

The problem I am running into is that after applying conditional formatting, it takes Excel 5+ minutes for the filter window to show up after clicking on it. If you select "show cells with red background" the program will then freeze up for minutes longer, sometimes crashing, sometimes eventually applying the correct filter.

If I remove the conditional formatting, applying and removing filters is nearly instantaneous. But without the conditional formatting, I can't remove the correct records.

Any ideas what is happening? I understand that going overboard with conditional formatting could affect performance, but this is a simple spreadsheet 3mb or smaller without formulas or multiple tabs and I don't see why this would cause it to hang so much.

Thanks for reading.

Can you sort on incident number?

Hi,

Sorting works perfectly with the conditional formatting in place, takes about a half second to sort all 20k rows. This further makes me wonder why filters would take 500x longer.

Is the problem excel really can't handle filters by color? I can't think of a way to accomplish this using sorting, there might be 300 records I would have to scroll through and delete one at a time.

Thanks

Thank you, I will try just that. If I am not able to figure it out myself, I will redact some confidential info from the file and upload here.

I appreciate everyone taking the time to read my problem.

Hello,

Thanks for all the help.

Adding a new helper column next to my incident number column with a countif statement is exactly what I needed. I can just filter for anything with a count >1, then manually filter for blank date closed fields (I'm sure I could have built this into the formula as you said, but it is so quick to do it manually I just skipped that part), and remove them manually.

I just needed a creative way to identify duplicate values without using the remove duplicates or highlight duplicates. I am not sure why the excel coding allows the countif statement to filter in a reasonable amount of time compared to conditional formatting, but I don't really care because you helped fix my issue!

I know no one here is paid to monitor forums and help answer excel forums, so I really appreciate anyone who took time to read and/or reply to my thread!