Excel Slow to Filter After Calculation

Question

I have (what I consider to be) a large excel spreadsheet with macros, data tables (some of which up to column DP wide), linked sheets etc - it is 8+MB in size.

Just recently it has really slowed up if I try to filter / unfilter the contents of one of the sheets where the majority of data is kept.

I have switched on manual calculation - it is okay for speed if I don't perform a (manual) calculation, however if I manually calculate then filter - it is slow.

Originally I had a the sheet on auto calculation, with a macro to temporarily go manual, then revert to auto.  However as I have explained above, this does not help because even the more extreme default to manual does not work for the first filter after a calculation.

This slow down seems to have followed me putting a large number of (I think necessary) countifs, aggregates, searches, etc on another sheet which link to the main data sheet. I understand that these functions are causing Excel to slow, put I am particularly interested to know why it is slow to filter even if the calculation is set to manual.  And I would like to know if there is a way to "really" stop the calculation (which I presume is why it is slow) when I filter.

Any ideas?

Answer 1

Hi pom15595，

Thanks for using Microsoft product and posting in the community.

For” I would like to know if there is a way to "really" stop the calculation when I filter”, there are two way to stop auto calculation, one is selecting manual calculation, the other way is using VBA.

For “why it is slow to filter even if the calculation is set to manual”, you may need to change more features in Excel.

1.Open Excel>click File>Options>Formulas>Calculation options>select Manual(you can also disable” Recalculate workbook before saving”)

2.If you have enabled iterative calculation, you may change its values.

3.Check whether multi-threaded calculation is enabled(click File>Options >Advanced>Formulas, click “Enable multi-threaded calculation” and click” Use all processors on this computer” )

Here is the reference may help you: Change formula recalculation, iteration, or precision in Excel

I have some suggestions for you to speed up Excel auto filter:

1.If there are empty cells towards the bottom of the sheet that excel thinks they have values so the filter tries to work on the large data set even though most of the rows are actually empty.

To test this, you can select cell A1 and hit CTRL + End. If this is the reason, you can copy only the cells that actually have values and paste them in a new sheet or workbook.  This will get rid of all the false blank cells.

2.If there are lots of formulas in the table causing excel to have to recalculate all the values when you apply the filter.

To fix these problems basically looking to decrease the amount of data and calculations. You can do this by copying only the cells with data into a new sheet and getting rid of all formulas in the table.

Please let us know if you need further assistance.

Best regards,

George

Answer 2

George

Many thanks for your reply.

Let me ask this question - if I have excel set to manually calculate, when I filter will excel still perform a calculation?  And it is does, how can I stop this?

I have already tried stopping auto calculate by selecting on the ribbon and by VBA.  I have also tried your suggestion about reducing the amount of data that is filtered.

My troubleshooting strongly suggests to me that the issue is the formulae on another sheet that link to the table that is being filtered.

What I don't understand is if excel is on manual calculation why do these formulae care / recalculate?

Answer 3

Hi pom15595，

Thanks for posting back. Sorry for my late reply.

According to your description, I think the issue may be caused by the formula on another sheet which link to your main sheet as you said. I think it only appears in the specific sheet.

Here is the suggestion for you to help us to narrow down the issue.

Your system version and Office version(you can go to Excel>File>Account, take the screenshot of Product Information and don’t click About Excel)

If you create a new sheet and copy your data without these links, will the filter feature work as normally(when the option is Automatic or Manual)?

Could you add the link one by one and test the step above?

If you have any concerns please feel free to post back, we are always willing to help you.

Best Regards,

George

Answer 4

George

If it is due to formula on another sheet, then why is this an issue with calculation on manual?

And furthermore, as per my original post why it is quick if I open on manual and don't recalculate, but is slow once a calculation is performed ie if I manually calculate once then it is slow forever afterwards?

Answer 5

Hi pom15595,

Thanks for posting back and sorry for my late reply.

For “If it is due to formula on another sheet, then why is this an issue with calculation on manual?”, I would like to explain to you that the factors may affect the calculation speed of the spreadsheet include not only the formula, but also the content of the spreadsheet, the setting of Excel, the version of the client, the computer hardware, etc. As you still have issue when it is set to manually calculate, I would like to collect more information to narrow down the issue.

Based on the information you provided, we have known the speed is better after you set to manually calculation. However, the slow performance seems back after a manual recalculation. But we still want to confirm further in details.

1. What’s the trigger steps. Recalculate only, or recalculate and filter?
2. For “if I manually calculate once then it is slow forever afterwards?”, I would like to confirm that after the spreadsheet is slow, do all the actions become slow(such as copy the value, insert the data) or just the filter feature cost more time or some special features cost more time?
3. How slow it is?
4. If you have convenient environment, could you please copy the same spreadsheet to another computer and check whether the issue persists?
5. If it is convenient for you, would you mind to send the spreadsheet to me via the Private message? It will be more helpful for us to troubleshooting the issue. If it is inconvenient for you to send the same spreadsheet for its sensitive and confidential information, would you mind to send a test spreadsheet(has the same formulas, test values and marked PII)?

If you have any concerns please feel free to post back, we are always willing to help you.

Best regards,

George