Top 10 List of Performance Issues in Excel Workbooks


By Laura Holzwarth

Performance issues are some of our biggest call generators. Users get frustrated with Excel when they upgrade to later versions. Performance issues often boil down to spreadsheets that have been around a long time and the design. Here are some of the common problem areas:

Slow Opening: Opening an Excel file the first time 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.  

210162 – Workbook loads slowly the first time that it is opened in Excel
http://support.microsoft.com/kb/210162/en-us

925893 – External links may be updated in Excel 2007 when you open a workbook that was last saved in an earlier version of Excel
http://support.microsoft.com/kb/925893/en-us

Updated Add-ins: Check your third party add-in companies to make sure you are using the latest version of the add-in.  All too often the code in the add-ins has changed and the newer add-in
will run much better.

Formulas: 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.

For instance if you have a VLOOKUP that looked like this: 

=VLOOKUP(A1,$D:$M,2,FALSE)

In Excel 2003 and prior it was referencing 655,560 cells (10 columns x 65,536 rows). With the new bigger grid, this same formula is referencing over 10 million cells.  (10 columns x 1,048,576 rows = 10,485,760).

Shapes: When copy and pasting data from various locations, such as websites and other applications, hidden objects can be copied into the Excel file.  These objects can cause performance issues in the preadsheet.
To determine if you have shapes in the file turn on Selection Pane. 

On the Home Ribbon choose, Find and Select, Selection Pane to display a list of all shapes in the worksheet. You can pick and choose to delete the shapes by selecting the shapes and deleting
them.  If you want to delete all shapes on the sheet, press Ctrl-G, choose Special, and then choose Objects. This will highlight all shapes and then you can delete them.

Defined Names: 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 these tools to see hidden defined names that we can’t see in the Excel interface.

http://www.jkp-ads.com/officemarketplacenm-en.asp

Styles: Old Excel files may collect styles and make the file grow.  For more information see http://support.microsoft.com/kb/213904 and clean out the file of excess styles use the following tool.

 Style Cleaner Tool

Excess file format cleaner:  If files have been formatted by highlighting entire columns the file will grow in size rapidly.  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 there http://xsformatcleaner.codeplex.com/ for more information see the following KB

How to reset the last cell in Excel

http://support.microsoft.com/kb/244435

Personal.xls: If you are using a personal macro template located in the XLStart Folder, you will want to make sure that you open it in the latest version of Excel, do a full calculation, and save it as a personal.xlsx. You will only have to do this once.  If you don’t do this, it calculates each time you open Excel, slowing down the open process.

XLSB extension:  If you have large files that take a long time to open (after their first calculate and save –number 1 in this list), try saving the file with an .xlsb extension.  Saving with this binary format will make the
file smaller and the load times faster.  See the following article for more information about the new file formats: http://blogs.office.com/b/microsoft-excel/archive/2006/07/20/all-about-file-formats.aspx.

Macros and VBA: If you have a macro that worked until Excel 2010, check out the following link for the Object Model changes: http://msdn.microsoft.com/en-us/library/ee836187.aspx

There certainly could be more issues that can affect performance issues in Excel, but these are the most common. If you are looking for more information about how to improve performance in Excel, see the
following MSDN article: http://msdn.microsoft.com/en-us/library/aa730921(v=office.12).aspx

 

 

UPDATE:  We just released the two articles that describes the performance issues and provides some workarounds to try in your Excel files.

KB 3066990: Memory usage in the 32-bit edition of Excel 2013

KB 3070372: How to clean up an Excel 2013 workbook so that it uses less memory

Comments (18)

  1. Anonymous says:

    You may resolve any excel file issue with the help of fix broken xls

    http://www.fixdamagedxlsfile.xlsfix.com

  2. jerryz51 says:

    Thanks for sharing this. I was having trouble copying and pasting. Copying would take 15-20 seconds to copy (spinning wheel displayed). This was occurring on spreadsheets where I had copied over html from my online banking. Using Find&Select/SelectionPane, I was able to find hundreds of hidden shapes. Selecting Ctrl+G/Special/Objects allowed me to select them and delete them. Problem solved. It is now a snap to copy & paste.

    Again, thanks for the hints and tips.

    –jerry

  3. jerryz51 says:

    Thanks for sharing this. I was having trouble copying and pasting. Copying would take 15-20 seconds to copy (spinning wheel displayed). This was occurring on spreadsheets where I had copied over html from my online banking. Using Find&Select/SelectionPane, I was able to find hundreds of hidden shapes. Selecting Ctrl+G/Special/Objects allowed me to select them and delete them. Problem solved. It is now a snap to copy & paste.

    Again, thanks for the hints and tips.

    –jerry

  4. Carl Malmquist says:

    If something works in a previous version of Excel and is not working in a later version, don't blame the person who designed the workbook.

    Excel 2010 has been overteched.  I like a lot of the new features, but not at the expense of limiting the ability of Excel to power through big workbooks.

  5. matheu says:

    thanks for this shareing ….I sove my problem.

    literaturereview.info/dissertation-literature-review.htm

  6. JH says:

    very odd — .xls files opened in Compatability mode w/ XL-2010 look fine on screen, but print some of their information (#s)as greek &/or arabic alphabet, instead of (arabic) numbers.  

    Any ideas, anyone?

  7. Dale Walker says:

    Double-clicking on a spreadsheet used to open a new instance of Excel, now the spreadsheet opens in the open instance if one exists. Our main performance issue is that opening 4 or 5 spreadsheets brings that Excel instance to it's knees. If we open a new Excel instance then load the spreadsheet so that we have separate instances, this fixes the problem but is much more cumbersome than opening a single directory and double-clicking on the spreadsheets. I can't believe that Microsoft chose to not allow default behavior to be changed to "Open in separate instance."

  8. Elizabeth Mallard says:

    Two issues – with large spreadsheets, suddenly approx 15 rows with have their heights changed to 50……….also when working in a more complex spreadsheet, the cursor will suddenly not reference one cell, but 3-5……….

    both a quite annoying issues         neither are resident to just one computer on a network

  9. S. Layto says:

    Spreadsheet will not go beyond line 39.  Help!

  10. Isabelle says:

    After working with excel – got a vertical blue line on right side- on all screens now, my Machad this happen years ago on windows xp. What to do to get rid of this line?

  11. Harshad says:

    Every time I edit a Excel worksheet single cell, it goes on displaying a whirling circle making my head spinning faster than that….I find worstel in excel…

  12. Harshad says:

    Every time I edit a Excel worksheet single cell, it goes on displaying a whirling circle making my head spinning faster than that….I find worstel in excel…

  13. Audrey says:

    OK, I’ve used MS Excel for 20+ years. Now I can’t figure out how to do the simplest spreadsheets, multiplying row data by column data. What the hell are you guys doing?!

  14. Rehat says:

    I am finding that my PMT function is not calculating correctly. How do I fix such an error?

  15. Andrea Kolton says:

    I recently started getting this message when attempting to open Excel files "There was a problem sending the command to the program". I’ll either click "OK" or close the window and the document will open, but not with the existing name…but as this "XI0000036
    [Read-Only] [Compatibility Mode]

  16. DM/Diddy says:

    My frustration is in the way some shapes are handled in 2010 compared with XP. I have a few arc shape with three points and four control nodes which are manipulated with VBA. Used to be changing these shapes was almost instantaneous. Now it takes almost
    six seconds to change four of these shapes. That is a total of 28 calls to TheShape.Nodes.SetPosition! Six seconds!!!

    Yes, ScreenUpdating = False. Sigh.

  17. Enid Anderson says:

    I still use Office 2003. I have a problem with Excel as many times when I enter data and save it the next time I open the file the data has disappeared. Why is this happening?

  18. Anonymous says:

    I found this post that may help
    http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2012/12/18/top-10-list-of-performance-issues-in-excel-workbooks.aspx

    The reason for slow performance is indeed likely due to VB scripts, macros or