Office 365 Excel Conditional Formatting issue - Duplicate Check

Question

Hi,

I have been trying to use the Conditional Formatting Highlight Duplicates check in the 365 and previous version of office with little success.

I have only 2 columns of values approx 45K rows in each column.

The duplicate values are highlighted very quickly, everything grinds to a halt as soon as I click the filter dropdown to filter for non highlighted values.

It can hang up my machine for over 10 minutes every time or crashes it completely. This happens on different laptops with different versions of office.

Very frustrating.

Can anyone please advise how to resolve this issue.

Answer 1

Hi Steve,

Based on your description, I need to confirm if you try the following steps to filter it:

1. Select the number.
2. Click Home>select Conditional Formatting>click Highlight Cells Rules>select Duplicate Values…
3. Select the title>custom a sort by cell color.
4. Sort it.

If the issue still exists, could you please provide the following information?

1. The Excel version. (Open Excel>select File>click Account)
2. Check if the issue occurs when you create a new file.
3. The affected file. As we need it to reproduce the issue.

To protect your privacy, I've sent you a private message to collect the information. You can access it via private message。

Thanks,

Qing

Answer 2

Hi Steve,

Did the issue still exist? Do you have any updates?

Thanks,

Qing

Answer 3

Hi,

The issue still exists and I have had no response so far.

Regards,

Steve

Answer 4

Hi Steve,

You are welcome to post back when you are free.

Thanks,

Qing

Answer 5

Excel 2010/2013/2016 with Power Query.

Faster if you filter with PQ.

http://www.mediafire.com/file/1kcatzjdr1hsx1c/12_05_17.xlsx

http://www.mediafire.com/file/t3y5wazgawmench/12_05_17.pdf