0

I have a fairly large workbook at around 45Mb that consists of about 30000 rows and columns to CA. I can't open it in 32-bit Excel any longer which now means I can't run it on my laptop. Without removing all the formulae I wanted to try and reduce the file size. I did some reading around and it was suggested that removing tables (converting to Data Range) and [@[SomeColumnName]] style functions (to regular cell references) would make it smaller. It actually had dramatically the opposite result; it would seem that using tables and a data model help to keep the file size down. Some research on here actually held up my own conclusions but I'm not really sure what goes on behind the scenes to keep the file size down? Is anyone able to explain it in fairly simple terms? Am I always going to be better off using a table rather than a data range?

nathanjw
  • 832
  • 2
  • 13
  • 23

1 Answers1

2

Power Pivot is an Excel add-in that utilizes a columnstore database engine for OLAP workloads. There is significant compression possible with this engine.

That being said, you'd minimize the benefits of compression with a table that wide and short. The compression works best on tall, narrow tables with low-cardinality columns.

It would likely be a significant effort to rewrite your data into a model fit for Power Pivot.

If you're not going to restructure, then you're not going to reduce the size of your data. Most likely the problem is at run-time calculating all those formulas, and not strictly with the size of the workbook.

What problem are you trying to solve with this workbook?

greggyb
  • 3,728
  • 1
  • 11
  • 32
  • greggyb - it's not letting me use the @ to mention you. It's a modeling exercise to do with cost of PTO and holidays. Hmm, I sense some reading on OLAP in my near future. I was surprised just how much it did compress or rather, how much it grew when I converted to a data range from a table. It went from about 45 Mb up near 60Mb. I originally created the dataset from other worksheets using Power Query then used Power Pivot to create pivot tables etc. I didn't know if creating a data model would make the file bigger or smaller. – nathanjw Nov 13 '15 at 18:29
  • Based on your original post it does not sound at all like you've used Power Pivot, as you wouldn't use [@[]] style references or be able to convert a Power Pivot table into a range. Are you able to share a sample of your workbook? – greggyb Nov 13 '15 at 18:35
  • Sorry, I might have muddled my last reply. I was able to load the table into the PP model even with the [@[]] style notation. It's entirely possible that I could have done a lot more of the calculations within PQ but I'm new to using it. However, I also created a copy of the workbook without using PP with the table converted to a data range. I didn't attempt to convert the PP table. – nathanjw Nov 13 '15 at 18:42
  • Load directly from Power Query to Power Pivot and you'll save a good chunk of workbook size since you won't be duplicating the data. A table that wide, though will not compress well in Power Pivot as I described above. I think you're at a point where you really need to think about restructuring your conceptual data model to make your life easier. – greggyb Nov 13 '15 at 18:48