excel freeze when clearing all filters, if i clear each column filter individually it don't freeze

[bmmezo33]

good morning
excel freeze when clearing all filters ( freeze for a minute or 2 minutes then resume working ) , during the freezing time the calculating percentage don't show, it shows after the freeze is resumed. if i clear each column filter individually it don't freeze, that happens in most of my sheets. even if small ones.
that is the google drive link for my main file: Loading Google Sheets

 

[Alex Blakenburg]

It doesn't take that long on my machine.
I think one of your main issues with clearing all the filters at once will be on the sheets that have quite a lot of SubTotal formulas in the top row that Excel is having to recalculate when you release all the filters.

In terms of fixing overall performance.
• You have quite a lot of whole column lookups. This means that your lookups are having to look at over 1M rows eventhough it might only contain 32 k rows.
• A few sheets have lots of fragmented conditional formatting (instead of covering the whole range they each cover only a bit of the range)
Note: refreshing conditional formatting will also slow down the spreadsheet.
• Sheet BOQ STUDY has links to an external workbook, if you are not using it, get rid of it.

 

[bmmezo33]

It doesn't take that long on my machine.
I think one of your main issues with clearing all the filters at once will be on the sheets that have quite a lot of SubTotal formulas in the top row that Excel is having to recalculate when you release all the filters.

In terms of fixing overall performance.
• You have quite a lot of whole column lookups. This means that your lookups are having to look at over 1M rows eventhough it might only contain 32 k rows.
• A few sheets have lots of fragmented conditional formatting (instead of covering the whole range they each cover only a bit of the range)
Note: refreshing conditional formatting will also slow down the spreadsheet.
• Sheet BOQ STUDY has links to an external workbook, if you are not using it, get rid of it.

unfortunately the problem is not with calculating time, because the calculating percentage always start after the freeze, and i have been working with this sheet for almost a year and it have never done that, all of a sudden it starting this freezing issue, so the size of the sheet or the conditional formating or the links to the external workbook are not the issue, as they haven't been an issue for entire year ......and i can't disregard any of the tabs in this sheet, i am using them all.

also this issue happens also in much smaller sheets with even one tab and no functions at all, it is driving me crazy.

 

[Alex Blakenburg]

Try disabling any add-ins you have running and see if that helps.
If that doesn't work you might want to try a quick repair first and if that doesn't help a full repair.

There are a series of options including the above suggested here by Vija Verma
My excel runs slow and gets "hung up"

 

[sam89665]

This is the same issue being discussed here: Excel Window Freezes after Clearing All Filters

It's linked to freeze panes - if you turn off freeze panes then the issues goes away.

Does anyone have a solution other than not using Freeze Panes or using VBA to clear all the filters? Thanks