Data-heavy XLSM. Adding and removing a filter creates a lot of lag. Clearing print area solves this.. How to stop this happening?

[RockandGrohl]

Hi guys,

We have several data files that grow each week and they are starting to get out of hand.

One such file is 13,376 rows long by 35 columns wide, and most fields are filled with data and formatted in a block of colour (without conditional formatting).

There are no live formulas, or pivot tables, or anything really. As far as I can see it's just a large block of data with a couple extra tabs with a very small amount of different information (just values held in some other cells)

Nevertheless, this sheet is now extremely sluggish - We are running on quad core, 8 thread PC's with SSD's and 16GB RAM, running Office 2010 64bit, and clicking on other cells gives a 1 second lag. Using the scroll wheel on the mouse is impossible and the sheet is generally just a chore to navigate around.


I found that when the sheet is laggy, I can go to page layout > Print Areas > Clear Print areas and this immediately makes the sheet a perfectly fine to use. It's snappy and responsive.

If I then apply a filter on some data, then clear the filter the sheet is extremely laggy and sluggish again. Once clearing the print area it's super quick again.


Can I stop this happening every time we use filters? We use them a lot for these sheets. Thanks.

 

[Marc L]

Hi,​

​

try to copy the data - values only - to a brand new workbook then save it as binary format .xlsb then try filtering …​

If the issue is still the same maybe it's time to think about a database software rather than Excel.​

​

​

 

[RoryA]

Which view are you using?

 

[RockandGrohl]

Which view are you using?

Normal I believe

Hi,​

​

try to copy the data - values only - to a brand new workbook then save it as binary format .xlsb then try filtering …​

If the issue is still the same maybe it's time to think about a database software rather than Excel.​

​

​

We actually did this before and it worked, but for a different sluggishness problem.

Unfortunately we won't be getting any new software any time soon. Excel 4 lyf.