Pivot Table data set size question

[MARKSTRO]

in Excel 2010 I am creating a pivot table with a data set of 18,000 rows and A-AE as columns for that data (currently a 5.8MB file).
This file grows each week as I add invoice data from a vendor, I expect it to grow beyond 50,000 rows in a month or two.

Recently I started getting the "cannot complete task with available resources. Choose less data or close other applications".

This error pops up after a reboot and the only application open is Excel. Resetting page memory to max seemed to help but it continues to error out when I attempt to move a row label to the column label.

I am using 19 fields in my pivot, successfully moved 2 from row to column labels after choosing them. When I attempt the third move I get the error. My IT dept says I have maxed out resources but we cannot find where.

I successfully created the same pivot table in another spreadsheet and was trying to create another copy so I could tweak it without messing with the original data. Now I cannot set it up as in the other sheet.

Can Excel 2010 handle a pivot table with this size data set? Can anyone suggest troubleshooting steps to help me?
Many thanks.

[JosephP]

that's not too big in terms of data although 19 fields is quite a lot for a summary table. have you tried turning off updating while you rearrange the pivot table? do you have subtotals displayed or any calculated fields or items in the table?

[MARKSTRO]

No calculations needed in the pivot, I'll try turning the update off while I rearrange the data, thanks for the tip.

[MARKSTRO]

Turning the calculations to manual did not work. I did find out a difference between the two spreadsheets, one was 5.8 mb and the other giving me problems was 33.6 mb, I cannot figure out where all that data was hidden in the file. Some corruption ocurred according to my IT dept. My pivot table changes still cause excel to not respond but will complete the task with the error message.

[MARKSTRO]

My IT dept repaired/reinstalled Excel 2010 and I continue to have problems with Excel not responding when I attempt to filter or clear filters from my data.
Stumped in San Diego.

[JosephP]

have you tried restricting the source data for the pivot to only the columns you actually need?

[MARKSTRO]

I need to correct my terminology as I reviewed my posts.
When I discover a problem in my pivot numbers, I go to the pivot data and filter that data to show what is on the pivot.
When I attempt to filter that data is when I get the "not responding or not enough resources" error messages.
I currently have 17,764 rows in columns A:AA, it used to be A:AE until I deleted 4 columns of uneeded data. I still get the "not responding" error when filtering, although the error clears and completes the filter/clear filter function.

What resource can I check what may be causing this error, eventually Excel will crash as I suspect this error compounds until I start getting the "not enough resources" error. Everything I have found so far is that Excel should be able to handle filtering many more rows of data than this over many more columns.

I have changed my print selection to a non-networked print as well, it seems our printers are queried for page formatting every time I change anything on the spreadsheet, I thought this may have been causing the problem, but it still exists when I tried that approach.

Many thanks for any suggestions.

[JosephP]

do you have any conditional formatting on the source data sheet or volatile formulas?

[MARKSTRO]

Good question, yes I do. a conditional format combined with VBA that highlights the row/column for the selected cell.
I have others which format a column of dollar amounts greater than $6, one which makes any zero value the same color as the background (to hide all the zeros) and one other to highlight another set of data.
Could the recalculating of these conditional formats when I filter the data be causing the errors??

[JosephP]

yes - conditional formats are volatile and if you have a lot of them they will slow things down and use up resource

[MARKSTRO]

What do you mean by volatile? As I add rows to the data it compounds the problem?

[JosephP]

I mean that it recalculated every time the workbook does even if none of the input cells have actually changed

check out the calculation pages at decisionmodels.com for more information on volatile formulas and actions that trigger calculation

[MARKSTRO]

Thx, will do