January 7, 2022
We have a large excel file that is very slow to update/save and when we link to it from other files, those files, although those files are not large, they then are also horrendously slow and lock-up a lot. I made a copy of the massive file as a dummy file and removed all formatting/formulas/links/pivot tables and only reduced the size from 395k KB to 293k KB. It's still very slow.
I did another test by saving it as a binary .xlsb and it reduced the size to 87k KB! My question is if we're going to have issues saving it as a binary type? On an online search, it seems that we'd only have issues if other programs tried to link to the file. Really the only thing we link to this file is other excel files (.xlsx). Are there other downsides to the binary file type? Thanks!
July 16, 2010
Welcome to our forum! If the file size is small and it's still slow then it sounds like there might be something corrupting the file. I'd be inclined to rebuild it.
I'd also recommend using Power Query to connect to the file from other files rather than using formulas, which are a bit of a no-no because they're easily broken. That said, there's no real downside to saving it as a .xlsb other than there's no macro security with .xlsb files, but you don't have macros in the file anyway.
The only other possible issue is sometimes Power Query has trouble connecting to .xslb files.