Spreadsheet design,which is more efficient: Table + Pivot table or Sheet + Formulas

Hi everyone,

Would you share your experience or best practice in designing a spreadsheet especially when it comes to large spreadsheets.
I found that although using tables to store data and pivot table for reports is cleaner and more organized, the file size get bloated fast even with small amount of data.
However, on other other hand, when I store data on a normal sheet and use formula to create reports is more efficient in terms of file size and speed.
Am I doing something wrong?

I also thinking of putting all the data in Access and just Excel for reporting, but that is extra step.

Any thoughts?

Thank you for looking into this.

Large spreadsheets, i always go with a datarange and a PivotTable. I dont use Tables as they are often more overhead than I need, and I just use a dynamic named range to always grab all the data in the pivot.

Try making 16,000 SUMIFS (ESPECIALLY IF YOU WANT DYNAMIC RANGES!!!!) or one pivottable. you will quickly see that using the native pivots are MUCH less overhead and MUCH faster and much smaller. Also keep in mind that a pivot cache can be used for multiple pivot tables and if you want to get really tidy, you can remove the actual data but leave the cache to make the file much smaller.


With large data I use PowerPivot to do the processing and the work, then drop the processed data into a standalone report.