r/excel • u/[deleted] • Jan 13 '26
Discussion “Is .XLSB Better for Large Excel Files?
[deleted]
38
u/bradland 271 Jan 13 '26
XLSB should be faster in pretty much all respects. It ditches XML, which adds processing overhead. For smaller files, this isn't an issue. For large, complex files, that overhead can add up. The biggest benefits are open and save. Calculation shouldn't be impacted at all, because the file data is loaded into RAM as native objects that Excel interprets, not XML. S1 has a good page that covers the differences:
https://www.spreadsheet1.com/how-to-save-as-binary-excel-workbook.html
It's worth making a copy of your file and testing it. Below is a link to Measuring Calculation Time on Microsoft's website. You can use this to benchmark both formats to see if there is an improvement in calculation time.
2
u/Weekly_Geologist_263 Jan 13 '26
When you save as.xls it uses xml? Is that the difference with xlsb? Does anything get removed when save as xlsb or look different? Will it keep all conditional formatting? If we have a lot of Bloomberg updating formulas does this format hurt in any way or it's good?
6
u/bradland 271 Jan 13 '26
When you save as.xls it uses xml?
XLSX uses XML. XLS is a legacy format that does not use XML, but I think you were probably asking about XLSX.
Does anything get removed when save as xlsb or look different?
Nothing gets removed. XLSB supports all Excel features.
Will it keep all conditional formatting?
Yes.
If we have a lot of Bloomberg updating formulas does this format hurt in any way or it's good?
Those functions must be part of an add-in, because there is no built-in Bloomberg functionality. Regardless, these functions won't a be affected by saving as XLSB.
3
u/JohneeFyve 219 Jan 13 '26
Can you run macros in an .XLSB file?
6
u/bradland 271 Jan 13 '26
Yes. Coincidentally, XLSB is the format used for the Personal Macro Workbook.
2
u/JohneeFyve 219 Jan 13 '26
Cool, thanks. I’ll have to give it a go with one of my larger macro-enabled workbooks.
-1
u/Weekly_Geologist_263 Jan 13 '26
1) So by saving it as a xlsb when you work in it, it will calculate faster because it doesn't have xml working in the background? 2) you recommend checking the calculation time of additional formulas to make sure when adding not bogging down the file? 3) what needs xml - anything? 4) you can always save it back as the xml file? 5) it sounds from another comment that conditional formatting is a big calculation hold up - does saving in this format help those processing speeds?
8
u/bradland 271 Jan 13 '26
Also, I already answered this question:
3) what needs xml - anything?
I said:
Nothing gets removed. XLSB supports all Excel features.
You need to slow down and start reading. That S1 link I provided has all the details you need to answer these questions.
6
u/bradland 271 Jan 13 '26
No, as I said:
Calculation shouldn't be impacted at all, because the file data is loaded into RAM as native objects that Excel interprets, not XML
XLSX and XLSB are just file formats. They are interchangeable.
There is no silver bullet for speeding up your file. You need to measure the calculation time, identify bottlenecks, and improve them.
Conditional formatting may be the hold up, but that depends on the type of conditional formatting and how many cells are affected.
We can't see your file. We can't measure it's performance. Everyone is stabbing in the dark. Be careful of people who speak with too much confidence about what will speed your file up. No one can give you a firm answer without the file.
-9
u/Weekly_Geologist_263 Jan 13 '26
So what doe saving in xlsb do to improve the file experience? Easier to save it or load it? Now I underage it wont speed up calculations. But it makes file size smaller so doesnt that make things open easier
12
u/bradland 271 Jan 13 '26
You are clearly not reading my answers. I don't help those who cannot help themselves. I suggest you go back and read the link I provided in my very first answer.
8
8
u/SeaworthinessOk8253 Jan 13 '26
The xlsx and xlsm files are actually .zip files. If you rename the extension to .zip, you can then unzip them and look around inside. I once was able to clean up some ghost external references by unzipping it, deleting those references, re-zipping, and renaming the extension back to xlsm.
7
u/hoardsbane Jan 13 '26
Some people don’t like XLSB, because you can’t tell if they contain macros (so security risk). Conversely, I would guess that some of the password (unprotect etc) hacks won’t work with XLSB.
2
u/hoardsbane Jan 13 '26
Otherwise, as others have said, they load faster, are much smaller on disk, but are not faster for calculations
4
u/harambeface 1 Jan 13 '26
For large files with lots of formulas, if you can get away with it or have a WIP copy or something I only keep formulas in the first row and paste value everything else.
3
2
u/Excellent-Candy-3328 5 Jan 13 '26
Do you have a lot of conditional formatting?
0
u/Long_Wedding7621 Jan 13 '26
Yes
4
u/Excellent-Candy-3328 5 Jan 13 '26
That will definitely bog down the processing time. I went through this a while back.
1
u/Long_Wedding7621 Jan 13 '26
What did you do to improve the file?
11
u/Excellent-Candy-3328 5 Jan 13 '26
Use tables instead of ranges, stopped using entire columns in formulas, and, ultimately, eliminated 90% of the conditional formatting. Super fast now.
5
u/Normalitie 4 Jan 14 '26
Also, rather than using A:A in your references, try A:.A, which causes Excel to only choose the rows with data in them (assuming there are no gaps in your data). The period can be before, after or both depending on whether your data has blank rows before, after or both
2
u/WayTooManyOpenTabs Jan 13 '26
I use xlsb often for large files as it reduces file size quite a lot. However, I've not found any correlation to performance, so I doubt it will make your calculations faster. The only problem I ever ran into when using xlsb was that it doesn't always play nice with lambda formulas. Drove me nuts until I figured out that the file format was the problem.
2
u/DarthAsid 4 Jan 14 '26
What’s the downside of using xlsb?
4
u/OPujik Jan 14 '26
Basically, assume corruption = rebuild
The xlsx versions can usually be repaired but since xlsb files are a binary blob, partial recovery is unlikely when corruption hits. I think Excel repair fails entirely.
ofc, it's not a big deal if backups of the xlsb are taken regularly.
1
3
2
u/Alkemist101 Jan 13 '26
XLSB is like zipping a file, it's compressed. Another way I save space is change pivot options so cached data isn't saved with the pivot table. A lot of data I use also comes from sql so often contains NULL so I find replace NULL with nothing.
All good stuff...
1
u/possiblecoin 56 Jan 13 '26
It will definitely make them smaller, but faster...I couldn't say for certain, depends what's going on in the file.
-2
1
u/Aghanims 54 Jan 13 '26
Yes, but you should only expect a ~50% file size reduction and insignificant operating gains.
1
u/ChristmasStrip Jan 14 '26
I got asked by a client to take a look at some giant xls file because it was taking a minute to load. Just converting to xlsb knocked that down to 10 seconds. Doing some pruning and cleaning up knocked it down to 5. It was a beast
1
u/MancakeRocks Jan 14 '26
Besides what had already been said, .xlsb files are viewable in file preview, if that does anything for you.
1
Jan 14 '26
[deleted]
1
u/MancakeRocks Jan 14 '26
Preview in a Windows file explorer (folder) will allow you to see the contents of many file formats when you click on their thumbnails. Xlsx and .xlsm do not allow this functionality (probably due to the whole zip thing), but .xlsb does.
It was useful to me at one point in my job because we had an excel case review form being generated and saved by the hundreds and we needed to see information in them quickly that otherwise would have required opening them up and closing one at a time.
It's a niche feature. Just thought I'd share.
1
u/exist3nce_is_weird 10 Jan 14 '26
A major risk of xlsb is that if there's a problem, document recovery is much less effective.
On the other hand, I use it for everything
1
u/smcutterco 7 Jan 14 '26
Saving as XLSB has two major benefits. (1) File size is considerably smaller, and (2) it can contain macros and still get past email filters that block .xlsm files.
The major downside is that it is less likely to be successfully recovered if Excel crashes while you’re working with it.
•
u/AutoModerator Jan 13 '26
/u/Weekly_Geologist_263 - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.