question

KT-5419 avatar image
0 Votes"
KT-5419 asked 61759770 answered

Excel auto-filtering is slow response

It takes about 10 to 20 seconds to perform auto-filtering on an Excel sheet with 7500 rows and 80 columns.

It's a simple filtering process that just checks what appears in the list of filters.

There are enough specifications for the personal computer.
The CPU and memory usage rates during processing are also 15% and 43%, respectively, which is a margin.

Is there any way to improve the performance of auto-filtering?

office-excel-itpro
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

61759770 avatar image
0 Votes"
61759770 answered

Thank you very much for your question.

Sorry for the delay in the reaction.

When I looked at this phenomenon, I found that the cell's comments were due.

If there are a large number of cell comments in the sheet, the rendering process after the filter will be slow.

I would like to stop the investigation once with this.

Once again, thank you for your answer.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ITSystems-1624 avatar image
0 Votes"
ITSystems-1624 answered ITSystems-1624 published

try disabling hardware acceleration and enabling it and see which one gives better performance, if you have a decent GPU enabling it should help.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

erinding-msft avatar image
0 Votes"
erinding-msft answered KT-5419 commented

@KT-5419

You could try the following methods.
1. Clear Formats.
Select All (Ctrl+A), go to Home>Editing>Clear>Clear Formats, then save file and re-apply filter.
50354-1.png

2. Turn off your automatic calculations when filtering.
Go to Formulas>Calculation>Calculation Options, then select Manual.
50355-2.png

More information, you may refer to "Excel performance: Improving calculation performance".


If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


1.png (10.3 KiB)
2.png (38.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Dear erinding-msft, thank you for your response.

The suggested method has improved performance, but unfortunately the format cannot be cleared.

I have noticed it while trying various things.

Looking at the status bar of Excel, it seems that the filter processing itself is finished soon, but most of the time is spent on the subsequent drawing process.

It would be nice if the drawing performance could simply be improved, but if you have any ideas, it would be helpful if you could reply.

1 Vote 1 ·