Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table has bloted file size - how do I bring it down
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table has bloted file size - how do I bring it down
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 tabTable 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. -- Regards Roger Govier "Anuj" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot table has bloted file size - how do I bring it down
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 "Roger Govier" wrote: 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 tabTable 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. -- Regards Roger Govier "Anuj" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Size | Charts and Charting in Excel | |||
Auto size a pivot table to a page size | Excel Worksheet Functions | |||
reduce size of pivot table file | Excel Discussion (Misc queries) | |||
pivot table cause file size to grow | Excel Discussion (Misc queries) | |||
Pivot table - size of file? | Excel Discussion (Misc queries) |