Symptoms
When you open or use Microsoft Excel workbooks, Excel crashes, hangs, freezes, or stops responding. In this situation, you receive the following error message:
Excel is not responding
Cause
Excel files can live a long time, being upgraded from version to version, and they may also travel from one user to another. Sometimes the user who's inherited the file doesn't know what's included in it. Taking an inventory of the file and removing items that are no longer needed will lengthen the life of the file.
Problems with Excel files crashing or hanging may be caused by several things. Most of the time the problem involves formatting and calculations. What follows is a list of possible resolutions.
Problems with Excel files crashing or hanging may be caused by several things. Most of the time the problem involves formatting and calculations. What follows is a list of possible resolutions.
Resolution
Try the following methods in the order in which they are presented. If a method doesn't help, go on to the next one.
Formatting considerations
Formatting is a common cause of Excel files growing too large and not working correctly. Try the following solutions to formatting issues.
Method 1: Eliminate excessive formatting
Formatting entire columns or rows with color or borders may cause problems. You may also experience this problem when copying or importing data from webpages or databases. To clean up excessive formatting, use the Excess Format cleaner found at http://xsformatcleaner.codeplex.com.
When there's excessive formatting, Excel sometimes recognizes the used range as including far more cells than those that actually include data.
If this doesn't work, go on to Method 2.
When there's excessive formatting, Excel sometimes recognizes the used range as including far more cells than those that actually include data.
If this doesn't work, go on to Method 2.
Method 2: Remove unused styles
Styles are used to standardize the different formats used throughout a workbook. When cells are copied from workbook to another, the styles are also copied. These styles make the file grow and may eventually trigger a "Too many different cell formats" error in Excel when you save to older file versions.
The following utilities have been created to clean Excel files of unused styles;
Excel 2007, 2010, or 2013 (xlsx, xlsm): Style Cleaner tool
Excel 2003 and earlier (xls): Remove styles
If this doesn't help, go on to Method 3.
The following utilities have been created to clean Excel files of unused styles;
Excel 2007, 2010, or 2013 (xlsx, xlsm): Style Cleaner tool
Excel 2003 and earlier (xls): Remove styles
If this doesn't help, go on to Method 3.
Method 3: Conditional formatting:
Conditional formatting can make a file grow in size. This behavior occurs when there's corruption in the conditional formatting. Test for corruption by removing the conditional formatting.
In Excel 2007, 2010, or 2013:
In Excel 2007, 2010, or 2013:
- On the Home Ribbon, click Conditional Formatting.
- Clear rules from whole worksheet.
- Follow steps 1 and 2 for each worksheet in the file.
- Save the file with a different name.
If removing conditional formatting works, you can open the original file, narrow down the sheet, remove conditional formatting, and then reapply it.
If this doesn't work, go on to Method 4.
Method 4: Shapes
Adding lots of shapes to a spreadsheet takes up lots of memory. A shape is defined as any object that sits on top of the Excel grid. Some examples include charts, drawing shapes, comments, clipart, SmartArt, pictures, and WordArt. Frequently, these objects are copied from webpages, or other worksheets and are hidden or sitting on top of one another. Typically, the user is unaware that they are even there.
To check for shapes In Excel 2007, 2010, or 2013:
To check for shapes In Excel 2007, 2010, or 2013:
- On the Home Ribbon, click Find and Select, and then click Selection Pane.
- Select The Shapes on this Sheet.
- Remove any unwanted shapes that appear in this list. The eye icon indicates that the shape is visible.
- Do this for each sheet:
If cleaning up the list of shapes doesn't work, you'll have to look at issues that don't involve formatting.
Calculation considerations
In addition to formatting, Excel calculations may also cause the file to crash or hang.
Method 1: Open in the latest version of Excel
Opening an Excel file the first time in a new version of Excel may be slow if you have a large number of calculations. Excel must recalculate the file and verify the values in the workbook. For more information, see the following:
Workbook loads slowly the first time it's opened in Excel
External links may be updated in Excel 2007 when you open a workbook that was last saved in an earlier version of Excel
If Excel has recalculated the file completely and then saved it, but it continues to open slowly, go on to Method 2.
Workbook loads slowly the first time it's opened in Excel
External links may be updated in Excel 2007 when you open a workbook that was last saved in an earlier version of Excel
If Excel has recalculated the file completely and then saved it, but it continues to open slowly, go on to Method 2.
Method 2: Understanding the big grid and the formulas that reference entire columns
Formulas that reference entire columns may trigger poor performance in .xlsx files. Such formulas grow the grid size from 65,536 to 1,048,576 rows and 256 (IV) to 16,384 (XFD) columns. A popular way to create formulas (although not a best practice) is to reference entire columns. If you reference just one column, this implicates only 65,536 cells. With the new grid, more than one million cells are involved.
For example, assume that you have a VLOOKUP that looks like this:
For example, assume that you have a VLOOKUP that looks like this:
=VLOOKUP(A1,$D:$M,2,FALSE)
In Excel 2003 and earlier, referencing an entire row involves only 655,560 cells (10 columns x 65,536 rows). But with the new bigger grid, this same formula references nearly 10.5 million cells. (10 columns x 1,048,576 rows = 10,485,760).
To resolve this issue, reconstruct your formulas to reference only the cells that are necessary to perform your formulas.
Notes
- Check your defined names to make sure you don't have other formulas referencing entire columns or rows.
- This scenario will also occur if you use entire rows.
If you are still experiencing this issue, go on to Method 3.
Method 3: Array formulas
Array formulas are powerful, but they must be used correctly. Make sure you don't add more cells in your array than you require. When a cell in your array has to calculate, it calculates all cells that are referenced in that formula.
For more information about how arrays work, see Excel 2010 Performance: Tips for optimizing performance obstructions.
If you have updated your array formulas, and are still experiencing the problem, go on to Method 4.
For more information about how arrays work, see Excel 2010 Performance: Tips for optimizing performance obstructions.
If you have updated your array formulas, and are still experiencing the problem, go on to Method 4.
Method 4: Defined names
Defined names are used to reference cells and formulas throughout the workbook in order to add a "friendly" name to your formulas. Make sure that you check for any defined names that link to other workbooks or temporary Internet files. Typically, these links are unnecessary and delay the opening of the Excel file.
You can use the Defined Name Manager tool to see hidden defined names that aren't otherwise visible in the Excel interface. This tool lets you view and delete the defined names you don't need.
If this doesn't resolve your problems, go on to Method 5.
You can use the Defined Name Manager tool to see hidden defined names that aren't otherwise visible in the Excel interface. This tool lets you view and delete the defined names you don't need.
If this doesn't resolve your problems, go on to Method 5.
Method 5: Links and hyperlinks
A key Excel function is its ability to bring in live data from other spreadsheets. However, Excel doesn't limit the number of Excel workbooks that can be linked, although excessive link numbers may cause issues. Take an inventory of the file that's triggering crash and hang problems and the external files that it links to. Test the file without the links to determine whether the issue is in this file or in one of the linked files.
These are the most common crash and hang issues in Excel files. If you are still experiencing these problems, consider opening a support ticket with Microsoft.
These are the most common crash and hang issues in Excel files. If you are still experiencing these problems, consider opening a support ticket with Microsoft.
More Information
If you are experiencing the issue that's described in the "Symptoms" section but it's not specific to a file, see How to troubleshoot crashing and "not responding" Issues with Excel.