Pivot table has bloted file size - how do I bring it down

[Anuj]

I have created an Excel 2007 which has 5 pivot tables with a common data
source in another sheet within the same file. The data source is of only 500
rows or so with about 15 columns (i.e. not particularly large).

However while specifying the data source at the time of pivot table creation
I have inadvertently specified the source range extending to Row 16000 or so
in all the 5 pivot tables

This resulted in making the file size huge - about 9 MB.

However now even after modifying the data source range to only 500 rows -
the file size has not reduced.

Is there anyway I can fix this.

Thanks for your help.

 

[Roger Govier]

Hi

That seems rather a large file size, relative to the extra rows.
On your source sheet, try selecting the row below your last row of
actual date, and press Control+Shift+Down arrow to select all rows to
the bottom of the sheet, then delete these rows.
You might just as well do the same thing for columns beyond your last
used column, with Control+Shift+Right arrow.

Now save the file. This should reduce the used range to just the area of
your data, and it will grow as you add more rows of data.

You could also convert your data to a Table.
With your cursor in a any cell of your data, Insert tab>Table
This will produce a table which will grow dynamically for you and will
include any formulae (if required) automatically as each new row is
added. It will take the default name of Table1, but you can change that
name if you wish.

For each of your 5 PT's, point them to Table1 for their data source.

 

[Anuj]

Thanks Roger - The second solution suggested by you seems to have worked.

In addition while implementing your suggestion I noticed that the data
columns were also on auto filter which I removed. _ That also could have
helped.

Anyway I am now down to a more manageable file size... Thanks

Anuj