When your Microsoft Excel spreadsheet slows to a crawl, you can’t help but notice. It may take longer to open and save your files, longer for Excel to calculate your formulas, and longer for the screen to refresh after entering data, or sorting and formatting the cells. System memory is the other issue that relates to Excel’s slowness. Slow spreadsheets take longer to manage and, as always, time is money.
We’ll show you how to tackle this problem. When Excel spreadsheets get too big Excel is capable of creating a very big spreadsheet, but the bigger it gets, the more memory is needed to keep it open on your PC. In the current version of Excel, each spreadsheet has 1,048,576 rows and 16,384 columns (A1 through XFD1048576). Each cell can hold a maximum of 32,767 characters.
I would not advise pushing these limits. Mentioned in this article. The number of records (rows), fields (columns), and formulas can slow down performance considerably. Every time you add new records, then press the Enter key—or use features such as Sort, Format cells, or Insert/Delete Columns or Rows—Excel recalculates all those formulas. This can cause a lag time of several seconds or more between each process.
Using a lot of graphical elements can also hinder performance. One solution, and one that I highly recommend, is to keep your spreadsheets small and tight, with fewer fields and, if necessary, fewer records. You can accomplish this by creating multiple spreadsheets in a single workbook, with links or three-dimensional formulas. You could also create Relational Database spreadsheets that connect your tables with unique, key fields. Turn on Manual Calculation and use F9 Another solution is to turn off the Automatic Workbook Calculation option, instead using the Function key F9. When Manual Calculation is selected in the Calculation Options, Excel withholds calculating your formulas until you press F9.
Select File Options Formulas. In the first section: Calculation Options under Workbook Calculation, click the Manual button. Check the Recalculate Workbook Before Saving box if you want to ensure that the spreadsheet calculation is always current. Or uncheck this box if you plan to calculate the spreadsheet manually using the F9 key before exiting. When finished, click OK. PC World / JD Sartain Turn off the Automatic Workbook Calculation option to help your sluggish spreadsheet. Excel memory limits Users constantly ask me: Why does my spreadsheet say “Excel cannot complete this task with available resources. Choose less data or close other applications?” Similar errors include “Not enough System Resources to Display Completely,” or “There isn’t enough memory to complete this action.
Try using less data or closing other applications,” or just “Out of Memory.” Although memory does not affect Excel’s calculation or manipulation speed, the size of your database (number of columns and rows used) is affected by the amount of available RAM in your system. Remember, just because your computer has 8GB of RAM, that doesn’t mean you have that much available to work with. PC World / JD Sartain Excel's memory error message Excel has its own memory manager and memory limits. The 32-bit version has a limit of 2GB of virtual memory, while the 64-bit version offers up to 8TB of virtual memory. Contrary to some rumors, those numbers include the software itself, plus any add-in programs you have installed. And that’s just in Excel. Other demands on your system’s memory include the OS, all the other applications that are currently open on your computer, plus a dozen other hidden processes such as DLLs, drivers, and a long list of.exe (executables) that are running in resident memory and/or in the background. Graphics, charts, formulas, and features such as the spell checker, sorting, and printing also consume memory.
Opened excel and created a 4th file, saved to desktop and closed excel > Hello!!! OK – open both files, save both close – all good. Open all 4 do anything I can think of – close excel – all good. Shutdown, turn Wireless on again. Open and close and save files in any order – still all good.
For the many users still working with the 32-bit version of Excel, if your spreadsheets are less than 2GB and you’re still receiving memory error messages, try closing all other programs that are running (including the Internet and your email program) to gain additional working memory. When it's time to move from 32-bit to 64-bit Excel If the performance and memory tips above both fail to increase your system’s performance or reduce the number of memory errors, then maybe it’s time to switch to the 64-bit version of Excel. This version does not limit your file sizes, but instead enforces limits only by available memory and system resources. This means if your system has 8GB of memory, Excel can access all of that minus whatever the system uses. JD Sartain / PC World Install the Large Address Aware update to expand your 32-bit Excel's file size limit from 2GB to 4GB.
If you’re considering a change from Excel 32-bit to Excel 64-bit, here's what to keep in mind: 1. Check out the Large Address Aware update. Microsoft rolled out this patch in June 2016, for 2013 and 2016 Excel versions. This update alters the 2GB limit on address space to 4GB when installed for the 32-bit version of Excel in the 64-bit version of Windows. For 32-bit Excel running in 32-bit Windows, the 2GB address space limit is increased to 3GB.
Other files are affected when you install this update: For example, for 32-bit Excel with 32-bit Windows, you must make a change in your boot file. Be sure to read Microsoft’s documentation on the Large Address Aware update before you install anything or make any changes.
64-bit Office only works with 64-bit Windows. You cannot run the 32-bit and 64-bit versions of Office on the same computer. If you attempt this, Microsoft displays an error message. JD Sartain / PC World You cannot mix 64-bit and 32-bit versions of Office. If you want to upgrade from your 32-bit version to the 64-bit version, you must uninstall and then re-install Office. The reverse is also true. 64-bit Excel: Features you'll lose Despite the performance beneifts of 64-bit Office, Microsoft actually recommends the 32-bit version of Office for most users, because of its greater compatibility with other applications (particularly third-party add-ins). Also, some of Office’s application features are not supported in the 64-bit OS, such as: 1.
The legacy versions of Equation Editor and Equation Builder are not supported 2. The Word Add-in libraries are also not supported (many dozens available online for free or for a minimal cost).
Some ActiveX controls and some VBA codes are not compatible. Some database files in Microsoft Access have source code issues.
Outlook MAPI applications must be recreated, and 6. The Graphics Device Interface (GDI) rendering may have performance issues due to incompatibilities between the 32-bit and 64-bit devices.
When I had a similar problem, disabling dropbox integration (I never realized I had enabled it) made a huge difference in performance, especially for otherwise trivial things in Office/Excel like copy and paste (which had become unbearable). System Preferences-Security & Privacy-Privacy Tab-'Allow the apps below to control your computer' (dropbox was checked, I unchecked it). Not sure exactly what functionality this took away from dropbox, but it still syncs folders fine, which is all I used it for anyway. (I know this is thread is over a year old, but it's one of the first results in Google so I'm adding this for all the others who come along in hopes it may help them).
I believe there is an out of control process causing this that needs to be force quit to get performance back to normal. Here are my online findings on the topic:. The comments hint towards too many 'open ports'. Force quitting the excel process via activity monitor with the most ports seems to do the job. I suspect this is a registration connection issue. Office tries to constantly connect home and check that the product is registered.
If you are offline and then go online then the issue occurs. I'm guessing these open ports are in reference to internet connections to a registration server. I have Office 365 Excel 16.10 running on High Sierra (10.13.3).
Copying takes several seconds, sometimes cell comments are not viewable at all & often my previously working Hyperlinks to other files in the same folder don't work. Neither unfreezing pans/rows, adding Excel to System Preferences Security & Privacy Privacy Tab Accessibility nor using Normal View (my standard anyway) made any improvement in its performance. I didn't have Dropbox in my Privacy list.
Disconnecting from the internet improved the situation, however. Copying is a little quicker, although viewing comments and using Hyperlinks are still unreliable. I have to say that I don't consider this a practical solution, merely a workaround while waiting for Microsoft to fix the problem.