I'm running into the same problems as you. To me, it appears that Microsoft has possibly made a major gaffe on developing Excel 2007. I tried many of the same things you did like:
1. Converting the file to a .xlsx file and closing and reopening it.
2. Changing to manual calculation.
3. Copying the data to a new workbook.
The same size of files worked fine in Excel 2003. And they run super slow when I enter data in Excel 2007. When I copied just one worksheet to another workbook it seemed to work fast. But I've tried doing that again and it doesn't seem to work now.
There is one constant that I have seen though. And that is that it goes slow when you enter a number. If you enter text it will enter it super fast. I decided to test this theory by formatting my cells as text and entered in a number. Walla! It entered them fast.
Even if you turn the calculation off Excel acts like it wants to calculate in Excel 2007 and I think it must somehow be trying to evaluate basically every cell in the worksheet instead of just the UsedRange. Excel 2007 now has like a million some rows and tons more columns than it used to in Excel 2003. And that could add to the lag time. Somebody didn't program Excel 2007 correctly I'm thinking. MAJOR gaffe on Microsoft's part and they need to add an update to fix it and FAST.
Bob Flanagan wrote:
Slow Excel 2007 calculation in a workbook and one solution
06-Jun-07
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
Previous Posts In This Thread:
Slow 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
Interesting work, Bob, but I don't buy that, especially the "most likely" part.
Interesting work, Bob, but I don't buy that, especially the "most likely"
part.
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!
--
Jim
Jim, I agree.
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
Excel 2007 very slow, "not responding"
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.
Submitted via EggHeadCafe - Software Developer Portal of Choice
SharePoint - Managing Unused or Archive sites automatically
http://www.eggheadcafe.com/tutorial...5b-a2a8deb60cad/sharepoint--managing-unu.aspx