Solved

Excel Opens Slowly

Posted on 2014-12-18
8
102 Views
Last Modified: 2015-01-12
We open very large excel documents over the network that take between 7 - 10 minutes to calculate formulas or save the document on average. I wouldnt think that updating the formulas would have anything to due with the network since its working in a temp file; however, I could be wrong. When the document opens with automatic calculation the document takes a while to load, if it has manual calculation opens right away. The desktop we're opening them on runs Windows 7 Professional with 8GB of memory with an Intel Core 2 duo E8500 3.17 GHz processor. The network is a 1Gb connection at the serve and a 100Mbps connection on the client so I'm assuming there is a small switch somewhere between. Our first solution was to run the doucments straight off the server; however, it seems to run slower. The server only has 2GB of memory and has a Intel Xeon E5310 1.60 GHz processor. The spreadsheets are about 20MB in size. We need to get these sheets running faster as they run our business. Any ideas would be greatly appreciated.
0
Comment
Question by:TechGuy_007
8 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40507311
Are you using User Defined Functions? Are there some huge calculations going on?

At the moment, there's not much to work on, other than there is a lot of calculating happening.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40507507
Do the documents open/calculate faster from locally-saved copies?  This would at least help determine if the network is an issue.

If there is no difference, you'll want to consider replacing any formulas that return record-dependent (i.e., un-changing) values into the actual values instead.  If you have unused columns with repetitive or blank data, remove those.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40507851
Do you have settings that cause workbooks to automatically calculate on open or on save?

If you do have user-defined functions, please post that code.  We might spot some performance bottlenecks and suggest alternatives.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:TechGuy_007
ID: 40507971
The user says that the OFFSET function seems to be causing some of the issue.

There are hundreds if not thousands of macros they use.  It is a financial company.  No large/complex calculations are occurring.

The file does seem to operate a bit faster when opened locally.  Strangely enough, the CPU usage while "Calculating" is around 50%.  On the server, all 4 cores are at 100%
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40508062
can the formulas be rewritten to not use the Offset() function?
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40508836
A very general tip for improving performance. When Excel recalculates it works from top left to bottom right, therefore it is best to structure your spreadsheet such that calculations won't have to be done repeatedly.

For example, if a formula in B2 relies on the value of Z26 it will calculate B2 based on the current value of Z26 and then when it gets to Z26 and that changes, the value of B2 will have to be recalculated; effectively starting the process over.

Thanks
Rob H
0
 
LVL 10

Expert Comment

by:broro183
ID: 40511240
hi,

There isn't much detail of the actual file design but, since any ideas are appreciated, I'll throw a few into the pot for use or consideration against a copy of the file:

- If you are using excel 2007 or newer, try saving the file using the "xlsb file format". It is a binary format & may cause quite a reduction file size.
- Change any formulae that contain full column references (eg "A:A") so that the formulae either refer to dynamic named ranges or Table range references. These ranges can be limited to the number of used rows within the file.
- Is there any setup code (other than UDF's, mentioned by Aikimark) that runs via AutoOpen or WorkbookOpen Event macros (please post it)?
- See Charles Williams' excellent website for lots of suggestions for optimising speed: http://www.decisionmodels.com/optspeed.htm
- To add to Aikimark's suggestion about removing Offset, an alternative approach to Offset is the use of Index:Index & Match.
- If vlookups are used, they could potentially be changed to Index/Match formulae: http://exceluser.com/formulas/why-index-match-is-better-than-vlookup.htm
- Remove as many volatile functions (eg Today(), Now(), Offset(...) etc) as possible & put repeatedly used functions into a single cell, which is then referenced by other formulae.

hth
Rob
0
 

Author Closing Comment

by:TechGuy_007
ID: 40544583
We also tested with a faster computer and it made a noticeable difference.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS excel check if Links exist 5 35
Rather Simple Formatting Question 6 24
Excel Charts: How is this Chart made? 8 28
Select Next Route by Time 4 19
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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