Hi there

I currently have a 6 month old Dell Vostro 470 with Intel Core i7 quad processor 3.4ghz processor and 8gb SDram, 2tb hard drive and 1gb graphics card.  I run Office 2010, but 99% of my work is in Excel and specifically Pivot Tables.

I run large Excel spread sheets typically size around 200-300mb built around a data sheet that may have up the 1m row limit and 200 columns.  A quick glance shows that I have 1000mb files (what is that 1gb?, 1m kb if I have that right) which seem to open quite quickly, less than a minute, but on all my large files I do have issues around crashes, being able to refresh the pivot. 

Typically I may have 20 worksheets in a workbook  all relating to the same source data sheet, which may be in the same workbook or in a separate workbook on the same pc.

I have two queries.  Where best to go for advice on the best PC spec for my type of work (if not here)?  and specific advice realting to a purchase.  I am contemplating a dell Optiflex and wondering if a 32gb SD RAM memory will give worthwhile extra performance against the 8gb that I have now and whether Dell has better processors that might suit me better.

I do find that on current Vostro 470 system with my 8gb quad processor, an Excel spread sheet will stop working when it exceeds a little more than 1.5gb memory according to task manager (How does that relate to 8gb of SDRAM?). 

Often I have a number of workbooks open to enable the pivots to refresh. Usually it just says insufficient resource.  I have found it useful to have the Pivot source data as a CSV file to reduce size, but I still find myself constantly having to adapt some spread sheets so that if the pivot source data file is only 500,000 rows then sometimes I have to limit the Pivot source to that number (rather then 1m) which is difficult if the size increases next time I need to refresh the pivots.

So I have these issues around the ability to update (refresh) Pivot tables, the size of workbooks I can open and to a degree the speed that my pc will calculate, even though I often have to work with worksheets that do not recalculate automatically ie I turn off automatic updates, so that it works efficiently.

2 KEY QUESTIONS:

•          Where best to go for advice on how to manage large data sheets/ pivot tables?
•          What spec of pc to go for optimum efficiency of my Excel/ pivot table needs?

I SPENT JUST LESS THAN 1,000 with Dell on my last Dell Vostro 470 (including Office 2010). 

•          Should I go for a Dell Optiflex and pay extra 400 to get 32gb SDRAM and could I spend even more to improve my work efficiency?
  
•          How  fast are the fastest Processors and can I improve much on what I have with my Dell Vostro 470
•          Are there any other PC's out there I should be considering?
•          I sometimes get messages like insufficient resources to display correctly.  Do I need a better graphics card?

Sorry this posting is so long.  If you have been, thanks for reading
Not quite sure which forum to go for, so advice especially appreciated.

Jesper

Sounds like you are running out of 32-bit memory.

I suggest you switch to Win 7 64 and 64-bit Excel 2010. With 32-bit Excel you are limited to a max of 2 GB memory, (and memory fragmentation means you often can't get more than about 1.5GBs).

64-bit Excel 2010 and 64-bit Win 7 will enable you to use the 8GB you already have.

My thanks for that.  I would be very pleased if there was a solution.

I am in fact running 64bit Win7 Professional but my pre-installed Office 2010 is 32bit.  Does that sound strange?   I really don't know much about 32bit and 64 bit.

Would I have to purchase an entirely new copy of 64bit to get the memory benefits of which you speak? I'm assuming that if so I would would have to buy Office 64bit, rather than just buy a copy of Excel 2010 or 2013 64 bit?

Many thanks for you help.

As a secondary question, it seems on investigation, that my processor is one of the best around.  Could you advise? 

OS Name    Microsoft Windows 7 Professional
Version    6.1.7601 Service Pack 1 Build 7601
Other OS Description     Not Available
OS Manufacturer    Microsoft Corporation
System Name    DELL470
System Manufacturer    Dell Inc.
System Model    Vostro 470
System Type    x64-based PC
Processor    Intel(R) Core(TM) i7-3770 CPU @ 3.40GHz, 3401 Mhz, 4 Core(s), 8 Logical Processor(s)
BIOS Version/Date    Dell Inc. A06, 25/05/2012
SMBIOS Version    2.7
Windows Directory    C:\Windows
System Directory    C:\Windows\system32
Boot Device    \Device\HarddiskVolume2
Locale    United Kingdom
Hardware Abstraction Layer    Version = "6.1.7601.17514"
User Name    Dell470\DellPG
Time Zone    GMT Standard Time
Installed Physical Memory (RAM)    8.00 GB
Total Physical Memory    7.96 GB
Available Physical Memory    5.09 GB
Total Virtual Memory    15.9 GB
Available Virtual Memory    12.7 GB
Page File Space    7.96 GB
Page File    C:\pagefile.sys

Many thanks, Jesper

You would need to re-install Office 2010 and choose the option of installing 64-bit (32-bit is the default).

I don't know if you can do that with a pre-installed version of Office without re-purchasing Office 2010

Your CPU looks fast enough

Hi Charles

Thanks for your interest.  I have also found the Excel memory limits section on your Decisionmodels site an interesting read.  I work a lot with sales data eg 2,000 stores x every (skU) line within specified categories x 2 years weekly sales.  I then have maybe 20 pivot tables, which I have copied one sheet to another so hopefully keeping pivot cahe to a minimum

I find it makes only a relatively small difference having the datasheet in a separate workbook, but I have a huge number of look up and calulating formulae that are both extemely long and often are set to a big range eg ideally i like my pivot table to go to source row 1,000,000  even if I have only 500k rows, in case I get to 1m next time I update.

I do try good maintenance but one is always trying to push the boundaries of what is possible.

I do use an ASAP utilities add-in which is extememly useful in so many ways and is good for resetting last cell. 

Which in itself is a problem ASAP and other add-ins that I find useful) do not do a version that works with 64bit Excel. I wish I could experiment and see if the benefits that I might find would outweigh the problems but without buying a new pc and next time making sure that I have an office disk I am not sure what5 I can do.

One question more, if I have kept the Office Extractor files, a process I think I went through, would the Options between 64 and 32 bit be within them? Anyway thanks for your help, I will mark your last response as the answer!    

Cheers, Jesper

There is a thread here on installing 64-bit office with Dell: I have not tried it myself.

http://en.community.dell.com/support-forums/software-os/f/3526/t/19406521.aspx

If you want to speed up Lookups you could try my SpeedTools MEMLOOKUP or AVLOOKUP functions to see how much they help.

You can download a 30-day trial of SpeedTools Beta 3 and find out more about SpeedTools here

http://www.decisionmodels.com/fastexcelv3speedtools.htm

My very best thanks for both links very useful!!

I think though I may have to stay with 32bit until ASAP go to 64bit,  cheers Jesper :)

Hi Guys

I have also posted this question as new thread, but thought as you had been so helpful it was worth re-posting here.

On your best advice, I bought a new Dell i7, pretty well the best I could buy, win7pro 64bit, 2tb hard drive, 1tb supplementary, 16gb ram and loaded Excel 64 bit.  I can now open much bigger files (I guess the biggest I have so far used is 1gb), but now I have just bought a colleague a 2nd pc with the same spec, I can imagine our files sizes will just grow until they don't work. 

By the way, we work mostly with a large data table on one worksheeet and lots of pivot tables on other tabs

My question is this.  I previously found that if I opened task manager I could see that there was a 'limit' of 1.5-2gb.

Since going Excel 64 bit, my limit is much higher.  Today I got to 5.7gb which I think is my highest.

But often I use task manager to end process before it gets to that level as often it has slowed so much and if I exit and start again it is quicker.

But is there a limit?  I don't really see the point of having 16gb of memory, with excel pretty well the only program in use and only reaching 6gb.

Advice or pointers most welcome.

Best regards and thanks, Jesper

My understanding is

32bit can address 2 to the power of 32 = 4,294,967,296 bits

64bit can address 2 to the power of 64 =  1.8446744074e+19

here's an informative thread http://stackoverflow.com/questions/8869563/how-much-memory-can-be-accessed-by-a-32-bit-machine