Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

excel 2010 slow performance

Posted on 2014-01-20
4
Medium Priority
?
536 Views
Last Modified: 2016-11-23
Hi,
I have a core i5 PC dell e6420 latitude. 4Gb ram
excel is fine, but there is one excel file which seems to get hung on lookup formula when dragged to all 33000 rows. the lookup data is in the same workbook.
I use 32 bit version of Ms office on a win 7 x64 bit OS.
the file size is 8MB but it consists of various other columns which has formulas.
the row size is 33000 with 8 formula based columns.
Excel 2010.


Any suggestions to try for eliminating this excel freeze.
0
Comment
Question by:kerry_dsouza
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 9

Expert Comment

by:BigPapaGotti
ID: 39794069
Are you trying to drag this formula to all of the other rows? If so, have you tried only dragging to a few hundred rows to see if it is any better? Also how long do you wait until forcing Excel to close? I'm guessing with that large of a file, that many calculations and that many rows it may still be calculating the data and copying the formula to the other cells.
0
 
LVL 19

Accepted Solution

by:
helpfinder earned 1500 total points
ID: 39794114
you can check if the number of formulas is a show stopper. Make a copy of that excel (I do no recommend to test anything in production and maybe the only one version of the file). Select all cells in the sheet and make copy (ctrl+C) and paste special>values (do this for all sheets). This makes all formulas disappear. Now do your calculation as you need and check if the performance is better - if so them probably number or complexity of formula(s) was a problem, if the speed issue persists you know it has nothing to do woth included formulas
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39815961
Hi there,

33k formulas isn't huge, but really it depends on what you're telling Excel to calculate. Not all formulas are created equal, and not all lookup functions are written efficiently. With all lookup formulas, the range you're looking at to find the value (you're searching for) is always best to have sorted. Another difference is whether you're looking for an exact or approximate match, although this depends on what type of lookup function you're using.

Take a look at Charles Williams website (creator of Fast Excel) for some good optimization tips:
http://decisionmodels.com/optspeed.htm

I'm afraid if you need anything else you'll have to be more specific about what you have, or even post a sample workbook.

HTH

Regards,
Zack Barresse
0
 
LVL 10

Expert Comment

by:Scott Thomson
ID: 39887759
There is most likely no one who can solve this off the bat and i think we need a few more details.

1. have you looked at task manager to see what sort of processor/ram is being used during this lookup function?
2. Has this always been the case as soon as the function was created for this file or has it gradually gotten worse? or did it just one day turn into a slow dog?
3. Are you able to test the file on another machine of equal/better/worse performance to see if its simply the function going through the motions compared to the actual power of the machine.?
4. Are you using any kind of kvirus scanner on the machine? does it change when you disable it.?

If you can answer the results of these questions we can possibly help you frther
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will provide some simple productivity hacks that will help you use Google to specifically show results from any web site (Experts-Exchange.com in my example), with minimal effort in Chrome and Firefox. I've seen a common theme a…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question