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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,237
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,334
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Which view are you using?
 

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,140,999
Messages
5,703,645
Members
421,307
Latest member
morrden86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top