Hi,

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

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

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

Many thanks!


--
andrewc
------------------------------------------------------------------------
andrewc's Profile: http://www.excelforum.com/member.php...o&userid=19613
View this thread: http://www.excelforum.com/showthread...hreadid=557292

To refresh all the pivot tables (and any queries) in the workbook, open
the External Data toolbar (ViewToolbars), 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 wrote:
Hi,

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

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

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

Many thanks!




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thanks Debra!


--
andrewc
------------------------------------------------------------------------
andrewc's Profile: http://www.excelforum.com/member.php...o&userid=19613
View this thread: http://www.excelforum.com/showthread...hreadid=557292