Jeff's Lunchbreak: Excel 2010 - Fixing a Slow Solver, XP 64

Excel & Windows XP 64 I use Excel a lot at work, and I use Solver pretty extensively for some calculations that almost have to be solved iteratively. One of those spreadsheets has grown pretty big, to the point where in Excel 2002 (Excel XP), it was taking around 35 seconds to solve a particular scenario.

When Excel 2007 came out, I thought I'd give it a try, but solver took forever to run. So, I decided to hold off on upgrading.

When Excel 2010 came out, I downloaded the beta version of it to give it a try. Again, solver took forever. The scenario that took 35 seconds to run in 2002 took 8 minutes and 7 seconds to run in 2010 - 13.9 times longer.

Well, this time I did a little more digging, and saw an option that I suspected might be giving it a problem. Under Options -> Advanced -> Formulas -> Enable multi-threaded calculation, I unchecked that checkbox. I ran solver again. Lo and behold it was down to 46 seconds - still slower than in 2002, but at least now it was something I could live with.

I went back and checked on a colleague's computer with Excel 2007, and even though I didn't time it this time around, disabling the multi-threaded calculation made a huge difference.

So, to anyone who's having a problem with Solver being mind numbingly slow, this may help you out.

As another side note for Office 2010, Microsoft apparently decided that they didn't want to support it for XP 64. For the beta version, at least, this isn't a problem. Just run the compatibility wizard (Start -> Run -> hcp://system/compatctr/compatmode.htm), and set the installation program to run under 'Microsoft Windows XP'.

Added 2010-10-01 Well, I've been using the officially released version of Excel 2010, and Solver has been working okay. ~~One more thing to add - after you disable multi-threading in the options, exit out of Excel and start it back up again for the option change to take effect.~~

Added 2010-10-26 I've been working on a project where I've been having to use Solver quite a bit, and it's more buggy than I originally thought. First of all, there's a second option that needs to be disabled. In Options, under the Advanced tab, in the General category, look for 'Enable multi-threaded processing' (right below the 'Web Options...' button). Uncheck that option.

With those two options unchecked, Excel will run fine for a while. However, it periodically re-enables the 'Enable multi-threaded processing' (not calculating) on its own. Worse, it sometimes seems to re-enable it without showing the check box as checked. So, whenever you're getting ready to start a project for the day, go back and double check that 'Enable multi-threaded processing' is disabled. If it shows to be disabled but Excel still seems to be running slow, enable it, OK out of the options, then go back in and disable it. This is a pain, and very irritating that the program messes with options that I've already set, but at least it's been working for me for the past few weeks.

Comments

Have you checked out Google documents? They have a spreadsheet like capability, although I don't know if it's developed enough for what you need. They have been updating and improving Google docs like mad. A buddy who's a Google fanboy even more than I am thinks that Google docs is a huge blow to Microsoft Office. He says there isn't a reason anymore to ever buy an office product. Cloud computing may be what you need.

Have you also considered OpenOffice? I can't say from personal experience that it would work better, but it would be worth trying.

In a similar but completely unrelated way this may help as well. I switched one of my computers to Ubuntu and had some fantastic results with it in spite of the difficulty in using it. I was burning converting and compressing my DVD collection into mp4s so that I would have a digital copy of them for my entertainment server taking more than 3 hours per DVD on a high end server running windows. Then I switched to a computer with a 1/4 of the stats running Ubuntu and I completed it in only 35 mins using the same software. It wasn't entirely because of Ubuntu being awesome, but that was a huge part of it. It seems that Ubuntu really takes advantage of having multiple processors and is much better at resource management. Even while being in the middle of converting a dvd I was only using 800mb of RAM total, while just having Windows 7 on, not doing anything uses more than a gig. It's not nearly as hard as it used to be to convert to Linux because of the development of virtualization. As long as your hardware supports vt for 64bit than you can virtualize and save any os as a virtual computer. I personally like VMware, but a friend of mine really likes VirtualBox. Even Windows 7 has the option to virtualize windows XP, although I think that it's only the Ultimate version that does. There are still user friendliness issues in Ubuntu, it's not as polished as Win7, but once you get it running the right way for you it's awesome. All of the above might be solutions for you, especially since I know that you are a technically savvy kind of guy. Of course 45 secs isn't to long and might not be worth all the work of switching os. I'd at least try the first two you might be pleasantly surprised.

Posted by: Eric Jones | May 22, 2010 10:36 PM

Using Windows 7 and Excel 2010, I struggled with a very slow Excel, with long delays after each mouse click or keystroke, until one day I noticed that some little purple triangles were slowly populating the upper left corner of rows having formulas. The light went on. I went to File|Options|Formulas, and unchecked "Enable Background Error Checking". That fixed it.

Posted by: Peter Petit | November 11, 2010 1:39 PM

Thanks for this post. It helped me resolve an issue today with Excel 2010 on Windows 7.

Posted by: Bill | August 14, 2012 11:12 AM

I am running Excel 2010 under Windows 7 on a new PC.

In spite of implementing all of Jeff's suggestions, the identical file still runs 5x slower than in Excel 2007.

Are there any other 'fixes' available?

Thanks
Leslie

Posted by: Leslie Glasser | September 20, 2012 9:41 PM

Yes - there is another fix. Remove all extraneous Add-Ins!

I have done this and my very large spreadsheet is now faster than in Excel 2007.

Thanks for all the useful suggestions

Posted by: Leslie Glasser | September 22, 2012 4:11 AM

Hello aare using Wordpress for your sitte platform? I'm new to thee blog world but I'm trying to get
started and create my own. Do you need any coding
expertise to make your own blog? Any help would be greatly appreciated!

Posted by: Think Bold Group Review | February 11, 2014 8:39 PM

І have been exρloring fօr a bit for any higɦ-quɑlity articles or weblоg posts iin this sort off space .

Explօring in Yahߋo I eventually stumbled upon thіs website.
Reading this info So i am satisfued to convey that I've an incгedibly excellent uncanny feeliոg I
came upon just what I needed. I such a lot undoubtedloy will make certain tօ do not fail tto remember tҺiѕ web site and provides it a glance regularly.

Posted by: Stephanie | February 13, 2014 3:26 AM

Everything is very open with a really clear
clarification of the challenges. It was truly informative.

Your website is useful. Thank you for sharing!

Posted by: Daily Binary Profits | February 24, 2014 9:00 PM

Having read this I thought it was extremely enlightening.

I appreciate you finding the time and energy to put this
information together. I once again find myself
spending a lot of ime both reading and leaving comments. But so what, it
was still worthwhile!

Posted by: 2014 Only Review | February 24, 2014 9:03 PM

ɦello there aand thannk you foг your info – I Һave dеfinitely picked
սp something new fгom гight here. I Ԁid however
expertise ɑ fеw technical points uѕing tɦis web site,
as Ӏ experienced tօ reload tҺe web site ɑ lot of times
previous tto І coսld get it to load correctly.
Ι Һad been wondering if yoour web host іs OK?
Not that ӏ am complaining, but slow loading instances times ѡill somеtimes affect youhr placement
іn google ɑnd caո damage your quality sccore if ads ɑnd marketing ԝith Adwords.

Ԝell I am adding thіs RSS toο my email aոd cߋuld lօοk ߋut
for а lot more of yоur reapective interesting content.
Ensure tҺat yyou update tɦis aǥain very soon.

Posted by: Jack Carter Profit Partners Reviews | February 26, 2014 8:26 AM

Grea article, just what I needed.

Posted by: The Six figure Funnel Formula course | March 1, 2014 6:49 PM

Heya this is kind of of off topic but I was wanting to
know if blogs use WYSIWYG editors or if you have to manually code with HTML.
I'm starting a blog soon but have no coding skills so I
wanted to get advice from someone with experience. Any
help would be enormously appreciated!

Posted by: Full Money System Review | March 3, 2014 10:49 AM

What's up, its pleasant article concerning media print, we all be familiar with media is a wonderful source of facts.

Posted by: Full Money System Testimonial | March 3, 2014 1:07 PM

Howdy, i read your blog occasionally and i own a similar one
and i was just wondering if you get a lot of spam comments?
If so how do you reduce it, any plugin or anything you
can advise? I get so much lately it's driving me crazy
so any help is very much appreciated.

Posted by: Nighthawk System Review | March 8, 2014 12:53 AM

Great post.

Posted by: Auto quick income Review | March 11, 2014 5:08 AM

My partner and I stumbled over here from a different web page
and thought I may as well check things out. I like what I see so now i
am following you. Look forward to looking over your web page yet again.

Posted by: The Auto quick income Download | March 12, 2014 5:35 AM

Salutations ! Très utile dans ce cas particulier message !
Ce sont les petits changements qui produisent les grands
. Merci beaucoup

Posted by: acheter vues youtube | March 12, 2014 11:50 PM

buy lipitor 80mg generic buy atorvastatin 20mg without prescription atorvastatin 40mg pills

Posted by: Pwiowm | March 12, 2024 12:13 PM

brand ciprofloxacin 500mg - cephalexin 125mg canada augmentin 375mg cost

Posted by: Vtzgsc | March 13, 2024 10:17 PM

cheap cipro 500mg - where to buy myambutol without a prescription buy augmentin paypal

Posted by: Aytyxi | March 14, 2024 11:22 PM

order ciplox 500 mg for sale - order amoxicillin 500mg generic
order erythromycin pill

Posted by: Gkvwrq | March 17, 2024 1:13 AM

order flagyl 200mg - where to buy azithromycin without a prescription buy zithromax 250mg for sale