Pivot Table Calculated Item - Too Many Records

**kareni** · 06-02-2011, 07:49 PM

I'm trying to enter a variance column to compare data from April versus May. I figured out how to calculate the data by using Pivot Table Tools > Options > Formulas > Calculated Item. However, after I enter the formula, I get a pop-up error that says "There are too many records to complete this operation."

The pivot table is 185 rows and the source table is 473 rows. It doesn't seem like the table is so large that Excel wouldn't be able to handle calculating the data. Any ideas on what could be going wrong?

-K

**MarvinP** · 06-03-2011, 12:10 AM

Hi Kareni,

I wonder if you have a minimum amount of memory and a lot of shapes or other objects in your excel file. The number of rows seems small enough but other things can be using lots of memory and cloging things up. Do you have any VBA code? Do you have any Volitile formulas on other sheets or in the same sheet?

Can you try your work using a 2007 version of excel? That might give you a clue on memory issues.

**kareni** · 06-03-2011, 12:05 PM

I'm currently using 2007 Excel. There are many other sheets in the file but no VBA code. There is another pivot table but it's very small (13 rows). Is there a way to troubleshoot where the memory issue is coming from with 2007 Excel?

Thanks,
K

**MarvinP** · 06-03-2011, 12:15 PM

I'd try a few things to determine what is causing the problem.

I'd create the new pivot on a new sheet to see if that works instead of where you are trying to put it.
I'd try to copy the data, values only, to a new workbook and do your pivot on that data to see if it works.

Somewhere along the line you are hoping to find what works and run into a setting or property that is giving the error message. Something simple like trying to put both pivot tables in the same space may be the problem.