Excel slow filter issue

%3CLINGO-SUB%20id%3D%22lingo-sub-125919%22%20slang%3D%22en-US%22%3EExcel%20slow%20filter%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-125919%22%20slang%3D%22en-US%22%3E%3CP%3EHI%2C%20i'm%20using%20office365%20for%20business%20at%2064bit%20on%20a%2064bit%20OS%20-%20Updated%20at%20the%20last%20security%20fix.%3C%2FP%3E%3CP%3EI%20have%20a%20workbook%20with%2010%20sheets%2C%20one%20of%20them%20have%2050.000%20rows%20and%20115%20column.%3C%2FP%3E%3CP%3EThe%20relative%20SHEET1.BIN%20has%20a%20weight%20of%20250mega.%3CBR%20%2F%3EThe%20workbook%20is%20about%2050-60mega.%3C%2FP%3E%3CP%3EThe%20sheet1%20doesn't%20reaches%20at%20the%20excel%20xlsx%20limit%20(above%201.000.000%20rows%20and%20255%20column)%20with%20the%20scroll%20bar.%3C%2FP%3E%3CP%3EThe%20workbook%20use%20formulas%20that%20refer%20to%20the%20other%20sheets%20of%20the%20same%20workbook.%20It%20doesn't%20have%20any%20type%20of%20external%20relation.%3C%2FP%3E%3CP%3EIn%20the%20sheet1%20we%20can%20find%20formulas%20like%20VLOOKUP%2C%20SUMIF%2C%20and%20SUMIFS%20nothing%20really%20complex%2C%20complicated.%3CBR%20%2F%3EThere%20are%20no%20circular%20references.%3C%2FP%3E%3CP%3EAS%20I%20said%20the%20sheet1%20is%20very%20slow%20it%20takes%201%20minute%20or%20more%20to%20set%20any%20filter.%20It's%20slow%20with%20calculation%20too.%3C%2FP%3E%3CP%3EThe%20opening%20of%20the%20filter%20men%C3%B9%20is%20immediate%2C%20but%20when%20i%20select%20one%20element%20or%20more%20in%20any%20filter%20it%20takes%20too%20much%20time.%3C%2FP%3E%3CP%3EI%20tried%20in%20every%20possible%20way%2C%20by%20deactivating%20auto%20calculation%20without%20getting%20any%20results.%3CBR%20%2F%3EI'm%20using%20xlsb%20format%20but%20nothing%20change%20if%20i%20use%20other%20format%20(xlsb%2Cxlsm%20etc)%3C%2FP%3E%3CP%3EI%20think%20that%20the%20problem%20is%20in%20the%20SHEET1.bin%20that%20is%20very%20huge%20(250mega)%3C%2FP%3E%3CP%3EWhat%20solutions%20i%20can%20apply%20to%20make%20it%20more%20useable%3F%3C%2FP%3E%3CP%3EI%20can't%20split%20it%20and%20it%20doesn't%20have%20any%20type%20of%20conditional%20formatting%20and%20no%20multimedia%20contenents%3C%2FP%3E%3CP%3Eif%20i%20scroll%20with%20the%20mouse%20o%20keyboard%20pag.up%2Fpag.down%20is%20fast%20as%20normal.%3C%2FP%3E%3CP%3EI%20tried%20to%20use%20OFFICE%202013%20pro%20not%20365%20but%20nothing%20has%20changed.%3CBR%20%2F%3EI%20tried%20to%20use%20different%20version%20of%20windows%2010%20instead%20of%208.1%20but%20nothing%20has%20changed.%3C%2FP%3E%3CP%3EBy%20editing%20the%20sheet1.bin%20with%20an%20hex%20editor%20what%20i%20can%20get%3F%3CBR%20%2F%3EI%20don't%20understand%20the%20code%20that%20the%20hex%20editor%20display...%3C%2FP%3E%3CP%3EI%20tried%20using%20excel%20in%20safe%20mode%20but%20nothing%20has%20changed.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-125919%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-125928%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20slow%20filter%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-125928%22%20slang%3D%22en-US%22%3ETo%20be%20honest%2C%20a%2050%2C000%20row%20by%20155%20column%20table%20is%20quite%20large%20for%20Excel.%20Especially%20if%20it%20also%20contains%20VLOOKUP%20formulas%20and%20SUMIFS.%20You%20are%20stretching%20the%20limits%20of%20what%20is%20feasible%20to%20do%20with%20Excel%20just%20using%20formulas.%20Pivot%20tables%20would%20perform%20a%20lot%20beter%20and%20if%20those%20VLOOKUPS%20are%20pointing%20to%20other%20tables%2C%20you%20can%20combine%20those%20tables%20into%20the%20same%20pivottable%20by%20setting%20a%20relationship%20between%20them.%3CBR%20%2F%3EUnder%20the%20hood%2C%20you%20would%20then%20be%20using%20PowerPivot%20to%20summarise%20that%20data.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2148803%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20slow%20filter%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2148803%22%20slang%3D%22en-US%22%3EShall%20we%20assume%20it%20is%20the%20correct%20answer%3F%3C%2FLINGO-BODY%3E
New Contributor

HI, i'm using office365 for business at 64bit on a 64bit OS - Updated at the last security fix.

I have a workbook with 10 sheets, one of them have 50.000 rows and 115 column.

The relative SHEET1.BIN has a weight of 250mega.
The workbook is about 50-60mega.

The sheet1 doesn't reaches at the excel xlsx limit (above 1.000.000 rows and 255 column) with the scroll bar.

The workbook use formulas that refer to the other sheets of the same workbook. It doesn't have any type of external relation.

In the sheet1 we can find formulas like VLOOKUP, SUMIF, and SUMIFS nothing really complex, complicated.
There are no circular references.

AS I said the sheet1 is very slow it takes 1 minute or more to set any filter. It's slow with calculation too.

The opening of the filter menù is immediate, but when i select one element or more in any filter it takes too much time.

I tried in every possible way, by deactivating auto calculation without getting any results.
I'm using xlsb format but nothing change if i use other format (xlsb,xlsm etc)

I think that the problem is in the SHEET1.bin that is very huge (250mega)

What solutions i can apply to make it more useable?

I can't split it and it doesn't have any type of conditional formatting and no multimedia contenents

if i scroll with the mouse o keyboard pag.up/pag.down is fast as normal.

I tried to use OFFICE 2013 pro not 365 but nothing has changed.
I tried to use different version of windows 10 instead of 8.1 but nothing has changed.

By editing the sheet1.bin with an hex editor what i can get?
I don't understand the code that the hex editor display...

I tried using excel in safe mode but nothing has changed.


Thank you

2 Replies
To be honest, a 50,000 row by 155 column table is quite large for Excel. Especially if it also contains VLOOKUP formulas and SUMIFS. You are stretching the limits of what is feasible to do with Excel just using formulas. Pivot tables would perform a lot beter and if those VLOOKUPS are pointing to other tables, you can combine those tables into the same pivottable by setting a relationship between them.
Under the hood, you would then be using PowerPivot to summarise that data.
Shall we assume it is the correct answer?