Sorting on Conditional Formatting crashes Excel

Question

Have a spreadsheet with 300K rows.  Set conditional formatting for duplicates on one column.  Trying to sort on the one column.  Going through the data/sort menu.  Takes a long, long time for the sort value to even come up.  After clicking sort by cell color, descending and then ok, wait.  After an hour I check task manager and see excel not responding.  On office 360.  Have noticed this problem for some time.  Have read through similar issues and did not see any solution.

Answer 1

Hi Eric,

Well received the file you shared via PM. I did a test on my side with conditional formatting, although Excel didn’t crash, conditional formatting is also very slow to apply.

[Image deleted]

I set color formatting for the column H. But because the data has 300,000 rows, Excel needs to process them line by line. And it would take a lot of time. The processing time depends on the configuration of your computer. As the amount of data is too large, it generally causes computer crashes.

We could know from this article that “using a lot of conditional formats can significantly slow down calculation. If the cell is displayed, every conditional format formula is evaluated at each calculation and when the display of the cell that contains the conditional format is refreshed.”

Because the data in this workbook is too large, it is not recommended that you use conditional formats in this workbook. We suggest you can split this workbook into several workbooks or using Microsoft Access to process such huge data.

I'm very sorry for the unpleasant experience of using Excel for conditional formatting. This is also a limitation of Excel. In this case, you can submit feedback to Excel development team directly, they read all feedback, and may consider your suggestion.

Highly appreciate your understanding.

Merry Christmas!

Best regards,

Madoc

Answer 2

Hi Eric,

Thanks for your updates. But it seems the image didn't upload correctly. To upload an image, you can click on the Insert image button.

Appreciate your understanding.

Best regards,

Madoc

Answer 3

Hi Eric,

Sorry for the inconvenience caused by this issue.

To troubleshoot this issue, there’re several steps you can try:

1. Update Office to the latest version. With Office updated, some issues would get resolved. So it’s a good idea to keep Office up to date.
2. Start Excel in safe mode to exclude the impact of third-party add-ins. Then check the result.

If the issue persists, we’d like to collect some information to further check the scenario:

1. Which version of Office do you have? After opening Excel, click on File > Account, please take a screenshot of the Product Information part.
2. Does this issue happen in other Excel workbooks? Or just this certain workbook?
3. If there’s any error message, please share a screenshot of it with us.

Best regards,

Madoc

Answer 4

 Please see responses below in CAPS.

To troubleshoot this issue, there’re several steps you can try:

1. Update Office to the latest version. With Office updated, some issues would get resolved. So it’s a good idea to keep Office up to date.  WE ARE ON OFFICE 360.  WE HAVE NO CONTROL OVER THE VERSION.  PLEASE SEE BELOW REGARDING LICENSE INFORMATION.
2. Start Excel in safe mode to exclude the impact of third-party add-ins. Then check the result.  PROBLEM PERSISTS.  EXCEL HANGS AT DATA SORT DIALOGUE BOX.  WAITED SEVERAL MINUTES.

If the issue persists, we’d like to collect some information to further check the scenario:

1. Which version of Office do you have? After opening Excel, click on File > Account, please take a screenshot of the Product Information part.  SEE BELOW.
2. Does this issue happen in other Excel workbooks? Or just this certain workbook?  YES.  THIS ISSUE IS CONSISTENT.  HAPPENS WITH EVERY WORKBOOK.
3. If there’s any error message, please share a screenshot of it with us.  JUST THE NORMAL EXCEL NOT RESPONDING ERROR.

Best regards,

Madoc

Answer 5

Hi Eric,

Sorry for the inconvenience caused by this issue.

To troubleshoot this issue, there’re several steps you can try:

1. Update Office to the latest version. With Office updated, some issues would get resolved. So it’s a good idea to keep Office up to date.
2. Start Excel in safe mode to exclude the impact of third-party add-ins. Then check the result.

If the issue persists, we’d like to collect some information to further check the scenario:

1. Which version of Office do you have? After opening Excel, click on File > Account, please take a screenshot of the Product Information part.
2. Does this issue happen in other Excel workbooks? Or just this certain workbook?
3. If there’s any error message, please share a screenshot of it with us.

Best regards,

Madoc