|
pivot table
Got a workbook dragging in data from a database on to one worksheet.
Got another worksheet (in the same workbook) doing a pivot table on the data dragged in on another worksheet. There are many pivot tables on this data. Workbook is so big now that I can refresh the pivot table (which has alot of necessary rows and columns) but the workbook will not save. A memory error occurs and I am forced to lose all work up until the previous save. Can you offer any advise? Thanks, -- ---------------------------- Regards, Richard Sterling Senior Software Engineer Tel : +44 (0)1707 392200 ext 4815 Avaya ECS Ltd, United Kingdom |
pivot table
In your post, you mention that you have 'many pivot tables on this data'.
Keep in mind that when you create a Pivot Table, Excel creates a hidden cached copy of the source data and bases the Pivot Table on that. So if you have a table with 10 columns and 40,000 rows....Excel will make a copy of that table for each Pivot Table based on that table, bloating the size of your workbook..... Unless...you opt to base your Pivot Table on 'Another Pivot Table Report' on Step 1 of the Pivot Table Wizard. That will base the Pivot Table on the same cached copy of the data as the original Pivot Table....NOT on the data displayed in the first Pivot Table (as might easily be inferred by the selection text). Consequently, no additional data cache will be created and the file size should drop dramatically. Does that help? ’’’’’’’’’’ Regards, Ron "rmsterling" wrote: Got a workbook dragging in data from a database on to one worksheet. Got another worksheet (in the same workbook) doing a pivot table on the data dragged in on another worksheet. There are many pivot tables on this data. Workbook is so big now that I can refresh the pivot table (which has alot of necessary rows and columns) but the workbook will not save. A memory error occurs and I am forced to lose all work up until the previous save. Can you offer any advise? Thanks, -- ---------------------------- Regards, Richard Sterling Senior Software Engineer Tel : +44 (0)1707 392200 ext 4815 Avaya ECS Ltd, United Kingdom |
pivot table
Ron,
I already have what you stated setup as Excel asks this question when you create subsequent pivot tables from the same data source. Any other suggestions please? Regards, -- ---------------------------- Regards, Richard Sterling Senior Software Engineer Tel : +44 (0)1707 392200 ext 4815 Avaya ECS Ltd, United Kingdom "Ron Coderre" wrote: In your post, you mention that you have 'many pivot tables on this data'. Keep in mind that when you create a Pivot Table, Excel creates a hidden cached copy of the source data and bases the Pivot Table on that. So if you have a table with 10 columns and 40,000 rows....Excel will make a copy of that table for each Pivot Table based on that table, bloating the size of your workbook..... Unless...you opt to base your Pivot Table on 'Another Pivot Table Report' on Step 1 of the Pivot Table Wizard. That will base the Pivot Table on the same cached copy of the data as the original Pivot Table....NOT on the data displayed in the first Pivot Table (as might easily be inferred by the selection text). Consequently, no additional data cache will be created and the file size should drop dramatically. Does that help? ’’’’’’’’’’ Regards, Ron "rmsterling" wrote: Got a workbook dragging in data from a database on to one worksheet. Got another worksheet (in the same workbook) doing a pivot table on the data dragged in on another worksheet. There are many pivot tables on this data. Workbook is so big now that I can refresh the pivot table (which has alot of necessary rows and columns) but the workbook will not save. A memory error occurs and I am forced to lose all work up until the previous save. Can you offer any advise? Thanks, -- ---------------------------- Regards, Richard Sterling Senior Software Engineer Tel : +44 (0)1707 392200 ext 4815 Avaya ECS Ltd, United Kingdom |
pivot table
Have you tried this?:
It might help to separate your application into two workbooks, one for the database pull and one for the Pivot Tables. At least you wouldn't have 2 copies of the same data in the same workbook (one in a worksheet and one cached). Does that help? If not, then what's consuming so much space? ’’’’’’’’’’ Regards, Ron "rmsterling" wrote: Ron, I already have what you stated setup as Excel asks this question when you create subsequent pivot tables from the same data source. Any other suggestions please? Regards, -- ---------------------------- Regards, Richard Sterling Senior Software Engineer Tel : +44 (0)1707 392200 ext 4815 Avaya ECS Ltd, United Kingdom "Ron Coderre" wrote: In your post, you mention that you have 'many pivot tables on this data'. Keep in mind that when you create a Pivot Table, Excel creates a hidden cached copy of the source data and bases the Pivot Table on that. So if you have a table with 10 columns and 40,000 rows....Excel will make a copy of that table for each Pivot Table based on that table, bloating the size of your workbook..... Unless...you opt to base your Pivot Table on 'Another Pivot Table Report' on Step 1 of the Pivot Table Wizard. That will base the Pivot Table on the same cached copy of the data as the original Pivot Table....NOT on the data displayed in the first Pivot Table (as might easily be inferred by the selection text). Consequently, no additional data cache will be created and the file size should drop dramatically. Does that help? ’’’’’’’’’’ Regards, Ron "rmsterling" wrote: Got a workbook dragging in data from a database on to one worksheet. Got another worksheet (in the same workbook) doing a pivot table on the data dragged in on another worksheet. There are many pivot tables on this data. Workbook is so big now that I can refresh the pivot table (which has alot of necessary rows and columns) but the workbook will not save. A memory error occurs and I am forced to lose all work up until the previous save. Can you offer any advise? Thanks, -- ---------------------------- Regards, Richard Sterling Senior Software Engineer Tel : +44 (0)1707 392200 ext 4815 Avaya ECS Ltd, United Kingdom |
pivot table
Ron,
That's not a quick solution to implement for me as many cell calculations work off this data too. I will try this solution as a last resort option. Do you have a general advise on how to reduce memory issues in Excel. e.g. Things that can be turned OFF, etc? It doesn't matter if it slows the performance down as it's the end result that matters and not the speed of how you get there. Thanks, Regards, -- ---------------------------- Regards, Richard Sterling Senior Software Engineer Tel : +44 (0)1707 392200 ext 4815 Avaya ECS Ltd, United Kingdom "Ron Coderre" wrote: Have you tried this?: It might help to separate your application into two workbooks, one for the database pull and one for the Pivot Tables. At least you wouldn't have 2 copies of the same data in the same workbook (one in a worksheet and one cached). Does that help? If not, then what's consuming so much space? ’’’’’’’’’’ Regards, Ron "rmsterling" wrote: Ron, I already have what you stated setup as Excel asks this question when you create subsequent pivot tables from the same data source. Any other suggestions please? Regards, -- ---------------------------- Regards, Richard Sterling Senior Software Engineer Tel : +44 (0)1707 392200 ext 4815 Avaya ECS Ltd, United Kingdom "Ron Coderre" wrote: In your post, you mention that you have 'many pivot tables on this data'. Keep in mind that when you create a Pivot Table, Excel creates a hidden cached copy of the source data and bases the Pivot Table on that. So if you have a table with 10 columns and 40,000 rows....Excel will make a copy of that table for each Pivot Table based on that table, bloating the size of your workbook..... Unless...you opt to base your Pivot Table on 'Another Pivot Table Report' on Step 1 of the Pivot Table Wizard. That will base the Pivot Table on the same cached copy of the data as the original Pivot Table....NOT on the data displayed in the first Pivot Table (as might easily be inferred by the selection text). Consequently, no additional data cache will be created and the file size should drop dramatically. Does that help? ’’’’’’’’’’ Regards, Ron "rmsterling" wrote: Got a workbook dragging in data from a database on to one worksheet. Got another worksheet (in the same workbook) doing a pivot table on the data dragged in on another worksheet. There are many pivot tables on this data. Workbook is so big now that I can refresh the pivot table (which has alot of necessary rows and columns) but the workbook will not save. A memory error occurs and I am forced to lose all work up until the previous save. Can you offer any advise? Thanks, -- ---------------------------- Regards, Richard Sterling Senior Software Engineer Tel : +44 (0)1707 392200 ext 4815 Avaya ECS Ltd, United Kingdom |
pivot table
Well, if you have many tens of thousands of complex formulas (typically
running down columns) that not only hog space but slow down performance, I sometimes resort to this where practical: ’Make sure the top formula in a column can be copied down that column and return correct results. ’Select all but the top row of formulas. ’EditCopy / Paste Special Values That will replace all (except the top row) of formulas with their values. Typically, that reduces workbook size and speeds up recalc times) After refreshing the data table, you'll need to copy/paste the top row of formulas back down the columns. After they're done calculating...repeat the above steps. If you do this often, you could automate those steps using VBA. I admit it's not especially pretty, but sometimes you've got to do whatever works. Good luck. Let me know how you fare. ’’’’’’’’’’ Regards, Ron "rmsterling" wrote: Ron, That's not a quick solution to implement for me as many cell calculations work off this data too. I will try this solution as a last resort option. Do you have a general advise on how to reduce memory issues in Excel. e.g. Things that can be turned OFF, etc? It doesn't matter if it slows the performance down as it's the end result that matters and not the speed of how you get there. Thanks, Regards, -- ---------------------------- Regards, Richard Sterling Senior Software Engineer Tel : +44 (0)1707 392200 ext 4815 Avaya ECS Ltd, United Kingdom "Ron Coderre" wrote: Have you tried this?: It might help to separate your application into two workbooks, one for the database pull and one for the Pivot Tables. At least you wouldn't have 2 copies of the same data in the same workbook (one in a worksheet and one cached). Does that help? If not, then what's consuming so much space? ’’’’’’’’’’ Regards, Ron "rmsterling" wrote: Ron, I already have what you stated setup as Excel asks this question when you create subsequent pivot tables from the same data source. Any other suggestions please? Regards, -- ---------------------------- Regards, Richard Sterling Senior Software Engineer Tel : +44 (0)1707 392200 ext 4815 Avaya ECS Ltd, United Kingdom "Ron Coderre" wrote: In your post, you mention that you have 'many pivot tables on this data'. Keep in mind that when you create a Pivot Table, Excel creates a hidden cached copy of the source data and bases the Pivot Table on that. So if you have a table with 10 columns and 40,000 rows....Excel will make a copy of that table for each Pivot Table based on that table, bloating the size of your workbook..... Unless...you opt to base your Pivot Table on 'Another Pivot Table Report' on Step 1 of the Pivot Table Wizard. That will base the Pivot Table on the same cached copy of the data as the original Pivot Table....NOT on the data displayed in the first Pivot Table (as might easily be inferred by the selection text). Consequently, no additional data cache will be created and the file size should drop dramatically. Does that help? ’’’’’’’’’’ Regards, Ron "rmsterling" wrote: Got a workbook dragging in data from a database on to one worksheet. Got another worksheet (in the same workbook) doing a pivot table on the data dragged in on another worksheet. There are many pivot tables on this data. Workbook is so big now that I can refresh the pivot table (which has alot of necessary rows and columns) but the workbook will not save. A memory error occurs and I am forced to lose all work up until the previous save. Can you offer any advise? Thanks, -- ---------------------------- Regards, Richard Sterling Senior Software Engineer Tel : +44 (0)1707 392200 ext 4815 Avaya ECS Ltd, United Kingdom |
| All times are GMT +1. The time now is 05:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com