Getting big performance issues: limits for local data?

[pete234]

I have about 150k records (20 fields each) in 8 tables with maybe 12 simple formula.
I have maybe 15 relationships all tied to the same 2 tables (fiscal and employee).
I have one table nearing 100k lines and it is getting very difficult to manipulate without "out of memory" or "error reading file" and never saving the document. Sometimes if a filter is on, it can take 10 minutes to update if I change a row.
I only have 2 actual pivots in the file and today,trying to make a 3rd was very difficult with constant out of memory.
The file itself is only up to 8mb so I don't think that is it.
I keep looking for a "compact and compress" button like Access but I guess it's already supposed to do that.
Anyone know of a way to optimize it?

 

[crazypabs]

If you press [Ctrl]+[End] on each sheet are you finding the cell that you are taken too is way outside of the current data range?

In your formulae, do you use lookups that refer to the entire column of data rather than a specific set of rows?

Matt

 

[Derek Brown]

Have a look at:
http://support.microsoft.com/kb/244435
You may find the download add-in (see 'Method 2') useful.

 

[pete234]

Thanks guys I'll check this tomorrow. My formula are all on the power pivot sheets, so just DAX and fairly simple. But they do naturally refer to the whole column I believe. I realize that when it gets bad my memory usage goes over 50% and this is on a newer machine with 6 gb of ram. But usually just booting windows it's at 35% usage (corporate network overhead I think) however I think Excel may have or used to have it's own memory limits? Today was better at least.