Pivot Tables and Array Formulae - Advantages & Disadvantages
Posted by SJC on January 31, 2001 12:06 PM
Given the choice of using pivot tables or array formulae to accomplish the same task, which method do you think is better?
From my experience, using array formulae slows the spreadsheet down considerably. Pivot tables seem to speed up calculation. However, when I use pivot tables to accomplish the same task, the file size of the workbook increases three-fold.
Anyone had this experience and/or able to share some insights on this? Thanks!
Posted by Mark W. on January 31, 2001 12:28 PM
Use of a PivotTable doesn't have to appreciably
increase the size of your workbook. You can
turn off the option to save data with the table
layout. If you have multiple PivotTables you can
have them based on the same data set.
Posted by SJC on January 31, 2001 12:54 PM
Thanks Mark! What effects will this have on the pivot table the next time I open the file? What does turning off the option do or not do to the file? Will I have to do anything the next time I open the file if I want to make sure my data is refreshed (besides using the "refresh on open" option)?
Posted by Mark W. on January 31, 2001 1:31 PM
Right-click the "Save data with table layout" label
on the PivotTable Options dialog and perform a
What's This? inquiry. I believe that will answer
your questions.
Posted by SJC on January 31, 2001 3:57 PM
Thanks, Mark!
Posted by Dave Hawley on February 01, 2001 3:06 AM
I would chose a Pivot Table 9 times out of ten. Array formulas have limited real use, often anyone of the Database formulas will acomplish the same without slowing down re-calculation.
One method to overcome a big jump in file size is to have one Workbook for your data and another for your Pivot Table.
Just my 2 cets worth
Dave
OzGrid Business Applications
