Oct 24 2022 04:35 PM
I have observed it to be universally true than an Excel file with a table of static data is smaller than a file where some columns are calculated -- even when the calculation is as elementary as a structured reference in the second example below. Any thoughts on this?
If I inspect the second listobject table with VBA, I see that [Keyword] has the property of being a calculated column, and I always assumed this simplicity and uniformity would lead to more economic storage of data, but with some loss of performance on the front end for large calculated sets. My conclusion is, I am better to fully calculate table data on the backend with Power Query than I am to do so dynamically on the front-end table, even though it may be more transparent and flexible to do in the final table. The increase in size seems to be greater 10% for a file with calculated columns.
FullCode | Keyword |
FORM WEAV1234 ABCD | WEAV1234 |
FORM ADAM1234 EFGH | ADAM1234 |
FullCode | Keyword |
FORM WEAV1234 ABCD | =mid(@[FullCode],5,8) |
FORM ADAM1234 EFGH | =mid(@[FullCode],5,8) |
Oct 25 2022 03:22 AM
If a cell contains a formula, Excel has to store both the formula and its current value in the workbook file. So yes, a workbook with formulas will always be larger than the equivalent workbook with only static values.
But does an increase in file size really matter?
Oct 25 2022 07:07 AM
Oct 25 2022 12:53 PM
You can check what Excel stores by changing the extension of the workbook file to .zip, then looking at the contents of the zip file.
It turns out that even if the range is NOT a table, Excel still stores a formula only one if it is repeated throughout a column. So that doesn't affect the file size.
But the table does add extra information to the file, so in fact a table with column formula contributes to a larger file size than the equivalent ordinary range.