Pivot Table Questions

[andrewc]

Hi,

I have a number of separate worksheets in the same workbook, eac
containing a few pivots tables that refer to data in one larg
worksheet. I have two questions please:

(1) Each month I will need to refresh each of the dozens of pivo
tables to take account of new data in the large worksheet referred t
above. How do I refresh all of the pivot tables at once rather tha
doing it on a sheet-by-sheet or table-by-table basis?

(2) Using pivot tables seems to swell the size of the original dat
file a great deal. What is the best way to minimise the size effect o
using pivot tables?
(other than, of course, using fewer!)

Many thanks

 

[Debra Dalgleish]

To refresh all the pivot tables (and any queries) in the workbook, open
the External Data toolbar (View>Toolbars), and click the Refresh All button.

To minimize the size, create one pivot table from the source data, and
create subsequent pivot tables from the first pivot table, so they all
use the same pivot cache. There are instructions here for changing the
pivot cache:

http://www.contextures.com/xlPivot11.html

Also, you can right-click on a pivot table, and choose Table Options.
Remove the check mark from 'Save data with table layout, and click OK.

 

[andrewc]

Thanks Debra!