Share via

Excel Slow to Filter After Calculation

Anonymous
2020-02-11T08:48:13+00:00

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?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

12 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-05-07T07:05:17+00:00

    Hi George

    Sorry to go back on this one.

    Can you clarify: earlier in the thread you indicated "we’d like to suggest you put these hyperlinks into different sheets instead of putting into one sheet. It may improve the data processing capability of Excel and shorten response time."

    However on the last post you suggested that it was better to have all the data on one sheet rather than have linked sheets.

    I may be misunderstanding, but aren't these two suggestions contradictory?

    Which one is the way to go?

    Thanks

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2020-05-08T12:05:10+00:00

    Hi Pom,

    Thanks for posting back.

    I have checked my reply again. Try to use simple direct cell references that work on closed workbooks should be the right reply. Sorry for my mistake. I have updated my replies. Thanks for your reminds.

    Regards,

    George

    0 comments