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.
Method 1: Eliminate excessive formatting
When there's excessive formatting, Excel sometimes recognizes the used range as including far more cells than those that actually include data. To reset the last used cell, you can run the utility that's described at How to reset the last cell in Excel.
If this doesn't work, go on to Method 2.
Method 2: Remove unused styles
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:
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
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.
Method 1: Open in the latest version of Excel
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
For example, assume that you have a VLOOKUP that looks like this:
- 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
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
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
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.
Article ID: 2735548 - Last Review: Feb 23, 2017 - Revision: 21