Excel freezes when filters are cleared - likely due to conditional formatting?

[MyOnion]

Hello!

I have this excel workbook (on PC) that suddenly freezes when I tried to clear the filter (not removing the filter). Oddly, it only affects this particular sheet which consist of less than 200 line items with simple formulas (IF, IFERROR, OR, vLOOKUP).

I suspect that there is some conflict between conditional formatting and freeze panes since removing freeze pane (conditional formatting is active) does not freezes the workbook when the filter is cleared (the red x). The excel does not freeze too when the the conditional formatting is removed (but freeze pane is still active).

I have tried recreating the workbook somewhat from scratch but the issue still persist. Though the issue can be somewhat avoided if I remove the freeze panes, it would be better if both features are present.

Anyone experienced this or have any idea how to solve this issue?

[dosydos]

When you clear filter, or even filter the data and you say the file freezes, look at the bottom right corner of your excel window. which I circled in red below.
Does it say something like "Calculating Threads".

If so then yes it is probably due to all your formulas having to recalculate at once. This will also be severely affected by the MS office 32-bit vs 64-bit version you are running.

[6StringJazzer]

This issue is almost certainly specific to your file, and not something that is a common issue. What conditional formatting rules are you using? If they use volatile functions or array formulas, it might take a long time for the to recalculate.

The best thing is to attach your file and tell us how to reproduce this condition. See yellow banner at the top of the page.

[maniacs205]

I have similar issue. Does someone knows how to fix ? https://prnt.sc/y1ea-ghLf8dl

[stromark]

I have been having the same problem as well, I've copied data only to another workbook, removing all the calculations and formatting
and still have excel freeze up when I remove a filter.

The question is what causes this, overall size of the file, number of cells of the workbook, number of other tabs taking up memory.

My current file has 263361 rows x 34 columns for 8,954,274 cells.

When do you take a large excel file and move it to a database, or, is there a workaround for excel freezing up when filtered data is unfiltered?

[FDibbins]

I have been having the same problem as well, I've copied data only to another workbook, removing all the calculations and formatting
and still have excel freeze up when I remove a filter.

The question is what causes this, overall size of the file, number of cells of the workbook, number of other tabs taking up memory.

My current file has 263361 rows x 34 columns for 8,954,274 cells.

When do you take a large excel file and move it to a database, or, is there a workaround for excel freezing up when filtered data is unfiltered?

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

[stromark]

Got it Admin, thanks