 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2008Slow Excel 2007 calculation in a workbook and one solution
Problem: In Excel 2007 an xls workbook takes 2-4 seconds to recalculte whenever a cell is changed. The workbook works fine in Excel 2003. No external links no macros no range names File well under 1 meg, with very few formulas - mostly data and sums No conditional formatting A few IF formulas, about 10-20 About 30 sum functions Just one worksheet Changing to manual recalc did not help Saving as xlsx did not solve Copying and pasting cells to a xlsx workbook did not solve Clearing all formats did not solve Converting all cells to values did not solve Copying the worksheet to a new workbook did not solve Deleting all cells did not solve!!!! Doing an Edit, Clear All did not solve!!!!! Solution that worked: Created a new xlsx workbook Selected the cells and did a copy, paste special formulas to the new workbook Changing cells in the new workbook resulted in no delay. The above works if there is just one sheet in a workbook. I'm not certain how one would solve if there were multiple worksheets. Any thoughts? Most likely there is some corruption in the XLS workbook, but none that affects performance in 2003, just in 2007. Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel >>Most likely there is some corruption in the XLS workbook Interesting work, Bob, but I don't buy that, especially the "most likely" part. >>Saving as xlsx did not solve I doubt "corruption" would survive that, after all, what would the XML equivalent of corruption be?<g> How about we say we don't know the cause but MS damn sure should!  SignatureJim | Problem: | [quoted text clipped - 35 lines] | http://www.add-ins.com | Productivity add-ins and downloadable books on VB macros for Excel Jim, I agree. After several hours of frustration, I jumped to conclusions as to the cause. I'm gong to keep trying to find the cause - after being away from the problem for a days, I have some more thoughts on how to pinpoint the problem. Bob >>>Most likely there is some corruption in the XLS workbook > [quoted text clipped - 48 lines] > | http://www.add-ins.com > | Productivity add-ins and downloadable books on VB macros for Excel I suddenly ran into the same problem with a fairly basic workbook, where it was taking 20 to 30 seconds for my spreadsheet to update with even the simplest of changes. Often, I would receive a "not responding" message. After spending far too much time searching the web for a solution and troubleshooting my workbook, I found the problem. I removed the "freeze panes" option on my 6 worksheets. Instant speed and no more of the "not responding" messages. I'm certainly no expert, but it seems to me that this problem could be related to the expanded row and column capability in Excel 2007. If this is the case, quite the gaffe by Microsoft...makes the freeze pane option unusable. I have kept up to date with Office updates, and tried removing them one at a time, but it didn't help. Hope this works for others, and that MS picks up on this soon.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.