There. I spilled the not-so-big secret. Excel files from Excel 2007 and above (.xlsx) are automatically compressed. A feature which, in all my years of using Excel, I never knew about.
I once received a large excel file from finance for analysis. Normally I would convert said file to CSV (comma separated values) as the latter:
- …is just data, no formatting. Exactly what I need for a data extract and nothing more.
- …tends to be more malleable across multiple applications.
- …and because of #s 1 and 2, tends to have a smaller file size.
So imagine my surprise when, upon converting to CSV, my 29 MB file ballooned to 115 MB.
Usually it’s the other way around. With all the formatting and formulas removed, the file size usually shrinks.
But apparently this is no longer the case when you have a lot of data. Once you go over a certain point, the amount of data you use matters more than the formatting.
Fortunately .xlsx is compatible with Power BI, which is where I was going to plug the data into anyway. I let the file type stay as is.
Makes for a convincing argument for the utilizing the Microsoft suite, eh?
(And in case your answer is no, let me argue that even technology research group Gartner agrees with me by crowning Microsoft king in business intelligence and analytics platforms.)