Excel/Excel 97-2003 and Excel 2007
As most people, I have files that I created in previous formats with plenty of cells and formulas. Now I am using Office 2007 and accessing these files. The backward compatibility (compatibility mode) is there, but it just takes so long for me to do any operation. You name it. For example a simple hiding or unhiding columns takes about 4 or 5 seconds before the little icon stops spinning and the columns operation is completed. It's pretty much the same thing when I work with any cell or cells.
FYI, the file is about 43MB in size. I use about ten tabs and I don't know how many lines, formulas etc. there are. Plenty.
My Operating System is Windows 7. I have an HP with RAM of 4.00GB.
CPU is Intel Duo E8500 @ 3.16GHz, 64 bit. And my hard drive has 878GB free of 915GB total.
Just as a test, I tried doing a Save As in Excel Macro-Enabled Workbook and Excel Binary Workbook, in addition to the current Excel 97-2003 Workbook. Then in each file I tried various operations with no improvement on speed. So is there an answer to my problem? Thanks,
With a file size of 43 MB I am not surprised about the slow speeds you are experiencing. Your system certainly has enough resources to be able to handle very large workbooks, but I suspect that Windows is not putting the entire process in RAM and is being slowed by virtual memory swapping. You can check this via the Task Manager. I believe Windows places an artificial limit on the amount of RAM that it allocates to an individual process. I don't remember how to change the system settings on this, but I believe there is a way to up the limit. I think you can find out how to do this with a Google search, or perhaps even a Windows help search. I suggest you increase it to somewhere around 2 GB since you have 4 GB installed.
I am not sure how much improvement can be achieved by the above approach. It appears to me that because of your file size you are likely reaching a point where you might want to consider re-structuring your problem. I cannot recommend a specific way to do this without knowing more about your workbook, but there are often design changes that can improve efficiency by reducing the complexity of the workbook. For example, thousands of cells containing complex formulas can sometimes be replaced by a few dozen lines of VBA, and the resulting reduction in memory requirements and more efficient calculations by eliminating unnecessary redundancies can cause Excel to speed up overall.
I hope you find this helpful.
Another (more extreme) solution may be to change applications. For example, if you are using Excel as a database tool, there are much more efficient database managers than Excel (e.g., Oracle, and even MS Access), and these applications are designed to work efficiently with huge databases.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here