Pivot Table crashing Excel 2010

skitout · May 2, 2011

[FONT=&quot]Hi

Am hoping there are some solid Pivot Table (PT) users out there who could give me a hand on this...

The problems:

1) The main problem: Refreshing some PT's makes Excel stop or crash

2) Additionally, changing the filters in these PT's also makes Excel stop or crash. Other PT's don't crash Excel when refreshed, but do crash Excel when the filter is changed... Both sets of Pivot Tables have the same filters... [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Further, there are other Pivot Tables in the workbook with the same filters that do NOT crash Excel when either refreshing the PT, or changing its filter![/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Moreover, for those PT's which can be refreshed without crashing Excel, but crash it when changing the filter, when we refresh them first we don't experience problems changing the filter!

And a final problem is that the PT's which exhibit 'problematic' behavior can change given different data sets.

So I am wondering about whether Pivot Tables are the best way to meet my goal, which is to dynamically regenerate statistics given a user inputting various periods (w/ start and stop end dates) out of a large times series of price data.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]General details: my workbook file is currently about 50Mo. There are 52 sheets, organized around a
main data sheet (raw data + data calculated on this sheet).

There are 112 pivot tables, all linked to the same (internal) source.

Problem 1 occurs with and without the option "Save source data with file"
of the PT.

For the PT with both problems, I use 2 Values and 2 to 4 "rows" with filter on 2 of these rows. I use the "Equals" subtype of the "Labels Filter".

Anybody encounter the same kind of problem or have an idea about the origin of this? Solution / suggestions appreciated! I also can't find much out there about

Thanks much in advance,[/FONT]
[FONT=&quot]S.
[/FONT]
<input id="gwProxy" type="hidden"><input *******="if(typeof(jsCall)=='function'){jsCall();}else{setTimeout('jsCall()',500);}" id="jsProxy" type="hidden">

foustus82 · Feb 7, 2014

Although I don't have near the data as mentioned above, I too am having this issue. On the Pivot Chart, I can manually filter data but it causes excel 2010 to crash. It seems that using the slicers instead of the manual filters has provided better stability.

vgr · Feb 9, 2014

Spit the sheets up, use MS access or any other database to store your data sheets or well you could get urself the best computer money can buy...

mole999 · Feb 9, 2014

when the pivots refresh do they grow in size, i usually leave 10 columns between each and 50 to 200 rows (or more) as soon as they overlap they error

vgr · Feb 9, 2014

Pivots grow in size? I'm not quite sure what you meant but a pivot basically populates data. In doing so, it does some number crunching(calculation) which is why it takes it a while to show u on ur screen.
P.S. - in case your source data doesn't change often, you could just copy the pivot and paste an image of it instead of creating 'n' number of pivot tables. there should be no problem when images overlap!

mole999 · Feb 9, 2014

vgr said:
Pivots grow in size? I'm not quite sure what you meant but a pivot basically populates data. In doing so, it does some number crunching(calculation) which is why it takes it a while to show u on ur screen.
P.S. - in case your source data doesn't change often, you could just copy the pivot and paste an image of it instead of creating 'n' number of pivot tables. there should be no problem when images overlap!

If you add say twenty rows of data to a pivot, and the pivot below is only 10 rows away it will fail, try creating a pivot that overlaps an existing pivot and you will see.

foustus82 · Feb 12, 2014

mole999 said:
when the pivots refresh do they grow in size, i usually leave 10 columns between each and 50 to 200 rows (or more) as soon as they overlap they error

No, the columns are stable (in their base form). But, when using the pivot chart to filter data (which naturally changes the pivot table's size), that's when it crashes. I had the pivot chart underneath the pivot table, but even at the pivot table's maximum size, it didn't impose on the chart.

So, I've hidden the filter labels on the chart and just use slicers. Don't know if this is "thee" solution, but so far, I haven't had any crashes (even though I'm essentially doing the same thing). This may be one of those areas where excel is like the little kid that won't eat the spaghetti because it doesn't look like mom's...even though it is all the same.

Pivot Table crashing Excel 2010

skitout

New Member

Excel Facts

foustus82

Board Regular

vgr

New Member

mole999

Well-known Member

vgr

New Member

mole999

Well-known Member

foustus82

Board Regular

Similar threads

Forum statistics

Share this page

Pivot Table crashing Excel 2010

skitout

New Member

Excel Facts

foustus82

Board Regular

vgr

New Member

mole999

Well-known Member

vgr

New Member

mole999

Well-known Member

foustus82

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock