قالب وردپرس درنا توس
Home / Tips and Tricks / Microsoft Excel: Why Your Spreadsheet Is So Slow

Microsoft Excel: Why Your Spreadsheet Is So Slow



As your Microsoft Excel spreadsheet slows down, you can't help but notice it. 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 related to the slowness of Excel.

Slow spreadsheets take longer to manage and, as always, time is money. We will show you how to tackle this problem.

When Excel Spreadsheets Get Too Big

Excel can make a very large spreadsheet, but the bigger it gets, the more memory it takes 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 contain up to 32,767 characters. I would not advise pushing these limits.

The number of records (rows), fields (columns), and formulas can slow performance significantly. Every time you add new records and then press the Enter key or use functions such as Sort, Format Cells or Insert / Delete Columns or Rows, Excel recalculates all those formulas. This can cause a delay of a few seconds or more between each process. Using a lot of graphic elements can also hinder performance.

One solution, and one that I highly recommend, is to keep your spreadsheets small and sleek, with fewer fields and, if necessary, fewer records. You can achieve this by creating multiple spreadsheets in one workbook, with links or three-dimensional formulas. You can also create relational database spreadsheets that connect your tables to unique, important fields.

Enable manual calculation and use F9

Another solution is to disable the option Automatic workbook calculation instead of the function key F9. If Manual Calculation is selected in the Calculation Options, Excel will stop calculating your formulas until you press F9.

1. Select File > Options > Formulas .

2. In the first section: Calculation options under Workbook calculation click the button Manual .

3. Select the Recalculate Workbook Before Saving check box to ensure that the spreadsheet calculation is always current. Or clear this check box if you plan to calculate the spreadsheet manually with the F9 key before exiting.

5. When you are finished, click OK .

  02 disable the automatic workbook calculation option PC World / JD Sartain

Disable the automatic workbook calculation option to help your slow spreadsheet.

Memory Limits for Excel

Users constantly ask me: why does my spreadsheet say, "Excel cannot complete this task with available resources. Do you choose less data or close other applications?" Similar errors are ' low system resources to display fully "or" There is not enough memory to complete this action. Try to use less data or close other applications "or just" Memory full "

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, just because your computer has 8 GB RAM does not mean you have that much available to work with.

  01 Excel Memory Error Message PC World / JD Sartain

Excel Memory Error Message [19659025] Excel has its own memory management and memory limits. The 32-bit version has a limit of 2 GB of virtual memory, while the 64-bit version offers up to 8 TB of virtual memory. Contrary to some rumors, those numbers include the software itself, plus any add-in programs you have installed.

And that's only in Excel. Other demands on your system's memory include the operating system, all other applications currently open on your computer, plus a dozen other hidden processes such as DLLs, drivers, and a long list of .exe (executable files) running in internal memory and / or in the background. Graphics, charts, formulas and functions such as spell check, sorting and printing also consume memory.

For the many users who still work with the 32-bit version of Excel, if your spreadsheets are smaller than 2 GB and you & # 39; If you are still receiving memory error messages, close all other running programs (including the Internet and your email program) to get additional working memory.

When it's time to switch from 32-bit to 64-bit Excel

If the performance and memory tips above both do not improve the performance of your system or reduce the number of memory errors, then it may be time to switch to the 64-bit version of Excel. This version does not limit your file sizes, but instead enforces only the available memory and system resources. This means that if your system has 8 GB of memory, Excel can access all that minus whatever the system uses.

  04 Install the Major Address Aware Update to Expand from 2 GB to 4 GB JD Sartain / PC World

Install the Large Address Aware Update to Eliminate the File Size Limit of Your 32-Bit Excel expandable from 2 GB to 4 GB.

If you are considering a change from Excel 32-bit to 64-bit Excel, please note the following: [19659002] 1. View the update of Large Address Aware. Microsoft rolled out this patch in June 2016 for Excel versions of 2013 and 2016. This update changes the 2 GB address space limit to 4 GB 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 address limit of 2 GB is increased to 3 GB.

2. Other files are affected when you install this update: For example, for 32-bit Excel with 32-bit Windows, you need to make a change to your startup file. Make sure to read Microsoft's documentation on the Large Address Aware update before installing or making changes.

3. 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 try this, Microsoft will display an error.

  03 cannot combine 64-bit 32-bit versions of Office JD Sartain / PC World

You cannot combine 64-bit and 32-bit versions of Office. [19659025] 4. To upgrade from your 32-bit version to the 64-bit version, you must uninstall and reinstall Office. The reverse is also true.

32-Bit vs 64-Bit Excel: Features You'll Lose

Despite the performance benefits of 64-bit Office, Microsoft actually recommends the 32-bit version of Office for most users, due to its greater compatibility with other applications (especially third-party add-ons). Also, some Office application features are not supported in the 64-bit operating system, such as:

1. The outdated versions of Equation Editor and Equation Builder are not supported

2. The Word Add-in Libraries are also not supported (many tens available for free or available online at minimal cost).

3. Some ActiveX controls and some VBA codes are not compatible.

4. Some database files in Microsoft Access have problems with the source code.

5. Outlook MAPI applications need to be recreated and

6. Graphics device interface (GDI) rendering may experience performance issues due to incompatibility between the 32-bit and 64-bit devices.

Note: When you buy something after clicking links in our articles, we can earn a small commission. Read our affiliate link policy for more information.

Source link