Excel files can live a long time, being upgraded from version to version and travel from one user to another user. Many times the user that have inherited the file don't know what's included in the file. Taking an inventory of the file and removing items out that are no longer needed will lengthin the life of the file.
Problems with Excel files crashing or hanging can be caused by several things most of the time it is narrowed down to formatting and calculations. The following is a list of possible resolutions.
Use the following methods in the order in which they are presented. If you try one of these methods and it does not help, go to the next method.
Formatting is a common cause of Excel files growing large and not working correctly. Below is the most common issue when files hang or crash because of formatting issues.
Method 1: Get rid of Excessive Formatting
Excessive formatting in an Excel file can cause the file to grow and cause performance hits. An example of excessive formatting is formatting entire columns or row with color or borders. We also occasionally see this problem when copying or importing data from web pages or
databases. To clean up excess formatting use the Excess Format cleaner found here http://xsformatcleaner.codeplex.com/
With excessive formatting Excel sometimes sees the used range as way more cells than where there is data. To reset the last used cell, a utility can be run that is found in the following KB, How to reset the last cell in Excel
If clearing out the Excess Formatting doesn't work, please move to method 2.
Method 2: Removing unused Styles
Styles are used to standardize the different formats used throughout workbooks. When cells are copied from workbook to another workbook the styles are copied as well. These styles then continue to make the file grow and may eventually get the error You receive a "Too many different cell formats" error message in Excel when saving back to older file versions
Here are two utilities have been created to clean the file of unused styles;
Excel 2007/2010/2013 (xlsx, xlsm): Style Cleaner Tool
Excel 2003 and older (xls): Remove Styles
If clearing out the unused styles doesn't work, please move to method 3.
Method 3: Conditional Formatting:
Conditional Formatting can has been seen to make the file grow in size. This happens when there is corruption in the conditional formatting in the file. Test the file by removing the conditional formatting.
In Excel 2007/2010/2013:
1. On the Home Ribbon > Conditional Formatting
2. Clear rules from entire worksheet
3. Follow above steps for each worksheet in the file.
4. Save the file with a different name
If removing conditional formatting works, you can open the orginal file and narrow down the sheet, remove conditional formatting and reapply it.
If taking out the Conditional Formatting doesn't work please move to method 4.
Method 4: Shapes
Adding lot's shapes in a spreadsheet also takes up loads of memory. A shape is defined as any object that sits on top of the grid of Excel, some examples include; charts, drawing shapes, comments, clipart, SmartArt, pictures, WordArt or other objects. Many times these objects are copied in from webpages, or other worksheets, and are hidden, or setting on-top of one another. Most times the user is unaware that they are even there.
To check for shapes;
In Excel 2007/2010/2013:
1. Home Ribbon > Find and Select > Selection Pane
2. The Shapes on this Sheet
3. Shapes will be displayed in the list. Remove any unwanted shapes, the eye icon indicates if the shape is visable.
4. Do this for each sheet.
If cleaning out the shapes doesn't work then we need to look at issues that are not with formatting of the file.
In addition to formatting, Excel calculations can also cause crashing and hanging with the Excel file.
Method 1: Opening 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 needs to recalculate the file and verify the values in the workbook. See KB’s below for more information. Workbook loads slowly the first time that it is opened in ExcelExternal links may be updated in Excel 2007 when you open a workbook that was last saved in an earlier version of Excel
Once Excel has recalulated the file completely and has been saved and continues to open slow, continue on to method 2.
Method 2: Understanding the Big Grid and how formulas that reference entire columns.
Formulas referencing entire columns might display poor performance in .xlsx files. The grid size has grown from 65,536 to 1,048,576 rows and 256 (IV) to 16,384 (XFD) columns. A popular (not a best practice) way to create formulas was to reference entire columns. If you are referencing just one column you were including only 65,536 cells, with the new grid it's over one million.
Consider this scenario:
If you have a VLOOKUP that looked like this:
In Excel 2003 and prior it was referencing and entire row only included 655,560 cells (10 columns x 65,536 rows) with the new bigger grid, this same formula is referencing 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 do your formulas. Note:
Check your defined names to make sure you don't have other formulas referencing entire columns or rows. Note:
This scenario will also happen if you use entire rows.
If you have changed your formulas to only refer to cells being used and are still having the issue move to method 4.
Method 3: Array Formulas
Array formulas are powerful, but they must be used correctly. It's important not to add more cells in your array than you need, when a cell in your array needs to calculate, it calculates all cells that are referenced in that formula.
For more information on how arrays work please see Excel 2010 Performance: Tips for Optimizing Performance Obstructions
If you have updated your array formulas, and are still having the problem move to method 5.
Method 4: Defined Names
Defined names are used to reference cells and formulas throughout the workbook to add a "friendly name" to your formulas. An area of concern check for any defined names linking to other workbooks or temporary internet files. Typically these links are unnecessary to the workbook, and slow down the opening of the Excel file.
You can use this tool, Defined Name Manager
to see hidden defined names that we can’t see in the Excel interface. This tool will allow you to view and delete the defined names you don't need.
If cleaning out the extra defined names doesn't stop the crashing and hanging, move to method 6.
Method 5: Links and Hyperlinks
Excels power is the ability to bring in live data from other spreadsheets. Take an inventory of the file and the external files that it's linking to. Excel doesn't have a limitation on how many Excel workbook can be linked together, although there are several scenarios you can run into. Test the file without the links to determine if the issue is in this file, or in one of the linked files.
These are the most common issues that we see with hanging and crashing with Excel files. If you are still having crashing and hanging in your file then you may need to open a support ticket with Microsoft.
If you are getting the above symptom but it's not specific to a file, please see the following article.