- The computer uses more memory when you open multiple Microsoft Excel 2013 workbooks, save Excel workbooks, or make calculations in Excel workbooks.
- You can no longer open as many Excel workbooks in the same instance as you could before you upgraded to Excel 2013/2016.
- When you insert columns in an Excel workbook, you receive an error about available memory.
- When you are working with an Excel spreadsheet, you receive the following error message:There isn't enough memory to complete this action.
Try using less data or closing other applications.
To increase memory availability, consider:
- Using a 64-bit version of Microsoft Excel.
- Adding memory to your device.
For more information about the changes that we made in Excel 2013, click the following article number to view the article in the Microsoft Knowledge Base:
Note Many Excel workbooks have several issues that can problems. After you eliminate these issues, your workbook will run more smoothly.
Formatting considerationsFormatting can cause Excel workbooks to become so large that they do not work correctly. Frequently, Excel hangs or crashes because of formatting issues.
Method 1: Eliminate excessive formattingExcessive formatting in an Excel workbook can cause the file to grow and can cause poor performance. Formatting would be considered excessive if you formatted whole columns or rows with color or borders. This problem also occurs when formatting requires data to be copied or imported from webpages or databases. To eliminate excess formatting, use the format cleaner add-in that is available at the following Microsoft website:
If you continue to experience issues after you eliminate excess formatting, move on to method 2.
Method 2: Remove unused stylesYou can use styles to standardize the formats that you use throughout workbooks. When cells are copied from one workbook to another, their styles are also copied. These styles continue to make the file grow and may eventually cause the "Too many different cell formats" error message in Excel when you save back to older file versions.
Many utilities are available that remove unused styles. As long as you are using an XML-based Excel workbook (that is, an .xlsx file or an. xlsm file), you can use the style cleaner tool. You can find this tool here.
If you continue to experience issues after you remove any unused styles, move on to method 3.
Method 3: Remove conditional formattingConditional formatting can cause the file to grow. This occurs when the conditional formatting in the file is corrupted. You can test the file by removing the conditional formatting. To do this, follow these steps:
- On the Home Ribbon, click Conditional Formatting.
- Clear rules from the whole worksheet.
- Follow steps 1 and 2 for each worksheet in the workbook.
- Save the workbook by using a different name.
If removing conditional formatting resolves the issue, you can open the original workbook, remove conditional formatting, and then reapply it.
If you continue to experience issues after you remove the conditional formatting, move on to method 4.
Method 4: Remove shapesAdding lots of shapes in a spreadsheet also requires lots of memory. A shape is defined as any object that sits on the Excel grid. Some examples are as follows:
- Drawing shapes
- Clip art
To check for shapes, follow these steps:
- On the Home Ribbon, click Find and Select, and then click Selection Pane.
- Click The Shapes on this Sheet. Shapes are displayed in the list.
- Remove any unwanted shapes. (The eye icon indicates whether the shape is visible.)
- Repeat steps 1 through 3 for each worksheet.
Calculation considerationsIn addition to formatting, calculations can also cause crashing and hanging in Excel.
Method 1: Open the workbook in the latest version of ExcelOpening an Excel workbook for the first time in a new version of Excel may take a long time if the workbook contains lots of calculations. To open the workbook for the first time, Excel has to recalculate the workbook and verify the values in the workbook. For more information, see the following Microsoft Knowledge Base articles:
925893 External links may be updated when you open a workbook that was last saved in an earlier version of Excel
If the file continues to open slowly after Excel recalculates the file completely and you save the file, move on to method 2.
Method 2: FormulasLook through your workbook and examine the kinds of formulas that you are using. Some formulas take lots of memory. These include the following array formulas:
Formulas that reference whole columns could cause poor performance in .xlsx files. The grid size grew from 65,536 rows to 1,048,576 rows and from 256 (IV) columns to 16,384 (XFD) columns. A popular way to create formulas, although not a best practice, was to reference whole columns. If you were referencing just one column in the old version, you were including only 65,536 cells. In the new version, you're referencing more than 1 million columns.
Assume that you have the following VLOOKUP:
In Excel 2003 and earlier versions, this VLOOKUP was referencing a whole row that included only 655,560 cells (10 columns x 65,536 rows). However, with the new, larger grid, the same formula references almost 10.5 million cells (10 columns x 1,048,576 rows = 10,485,760).
To resolve this issue, reconstruct your formulas to reference only those cells that are required for your formulas.
Note Check your defined names to make sure that you don't have other formulas that reference whole columns or rows.
Note This scenario will also occur if you use whole rows.
If you continue experience issues after you change your formulas to refer only to cells that are being used, move on to method 3.
Method 3: Calculating across workbooksLimit the formulas that are doing the calculations across workbooks. This is important for two reasons:
- You are trying to open the file over the network.
- Excel is trying to calculate large amounts of data.
If you continue to experience the issue after you change your formulas to refer only to cells instead of calculating across workbooks, move on to method 4.
Method 4: Volatile functionsLimit the use of the volatile functions in a workbook. You do not have to have hundreds of cells that use the TODAY or NOW function. If you have to have the current date and time in your spreadsheet, use the function one time, and then reference the function through a defined name of a link.
If you continue to experience the issue after you limit your volatile formulas, move on to method 5.
Method 5: Array formulasArray formulas are powerful. But they must be used correctly. It is important not to add more cells to your array than you must have. When a cell in your array has a formula that requires calculation, calculation occurs for all cells that are referenced in that formula.
For more information about how arrays work, please see the following Microsoft website:
If you continue to experience the issue after you update your array formulas, move on to method 6.
Method 6: Defined namesDefined names are used to reference cells and formulas throughout the workbook to add a "friendly name" to your formulas. You should check for any defined names that link to other workbooks or temporary Internet files. Typically, these links are unnecessary and slow down the opening of an Excel workbook.
You can use the Name Manager tool to view hidden defined names that you can’t see in the Excel interface. This tool enables you to view and delete the defined names that you don't need.
If Excel continues to crash and hang after you remove any unnecessary defined names, move on to method 7.
Method 7: Links and hyperlinksExcel's power is in its ability to bring in live data from other spreadsheets. Take an inventory of the file and the external files to which it is linking. Excel doesn't have a limit on how many Excel workbooks can be linked, although there are several issues that you can encounter. Test the file without the links to determine whether the issue is in this file or in one of the linked files.
Moving onThese are the most common issues that cause hanging and crashing in Excel. If you are still experiencing crashing and hanging in Excel, you should consider opening a support ticket with Microsoft.
ID Bài viết: 3070372 - Xem lại Lần cuối: 09-10-2015 - Bản sửa đổi: 1