Excel Table Data and file size: structured references vs static data

New Contributor

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.

 

FullCodeKeyword
FORM WEAV1234 ABCDWEAV1234
FORM ADAM1234 EFGHADAM1234

 

FullCodeKeyword
FORM WEAV1234 ABCD=mid(@[FullCode],5,8)
FORM ADAM1234 EFGH=mid(@[FullCode],5,8)

 

3 Replies

@weav8060 

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?

Ok, thanks, I trust your first statement is true for tables. Let me say it differently -- I thought tables were different, where a consistent columnar formula throughout is recognized as such (as evidence in VBA) and becomes a property of the table, not the cell, and computed not stored for each cell.

@weav8060 

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.