Performance Issues Of Excel Workbooks And Their Solution

Performance Issues Of Excel Workbooks And Their Solution

Performance issues are always a big challenge. 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:

  1. 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.
  2. 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.
  3. 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,$E:$O,3,0)

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).Always mention required range in Formula to avoid slow performance.

  1. 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 spreadsheet.
    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.

  1. Defined Range 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 got to Formulas->Name Manager(Ctrl + F3) and delete unwanted Range Names.
  2. Styles:Old Excel files may collect Styles or Conditional Formatting and make the file grow. You need to clear unwanted formatting.
  3. 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.
  4. 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.
  5. Conditional Formatting:While applying conditional formatting, user selects unwanted cells which increases the file size. To avoid this, you can go to each and every sheets then select all blank rows and columns and delete it (Ctrl -).
  6. Pivot Table: While creating any pivot table reports users select entire sheet or column range without any data which ultimately affects file size and performance. To avoid this, you can go to source data option of pivot table under analyse tab and adjust it properly.

 

 

This Post Has 8 Comments

    1. Prof RASHID RIZWI

      Thank you for your comment. Please keep visiting our website for more such informative articles.

  1. Nila

    Thank you for the auspicious writeup. It in fact was a amusement account it.
    Look advanced to far added agreeable from you!

    However, how could we communicate?

  2. Flor

    I just like the helpful information you supply in your articles.

    I will bookmark your weblog and test once more here frequently.
    I am quite sure I will be informed lots of new
    stuff proper here! Best of luck for the next!

  3. Georgina

    wonderful post, very informative. I’m wondering why the other specialists of this sector don’t realize this.

    You must continue your writing. I am sure, you’ve a great readers’ base
    already!

  4. Kassie

    Very good information. Lucky me I came across your site by chance (stumbleupon).

    I have saved as a favorite for later!

  5. Shana

    It’s going to be finish of mine day, but before end I
    am reading this fantastic piece of writing to improve my experience.

  6. Noble

    Can I simply just say what a comfort to uncover someone that actually knows what they’re discussing
    online. You definitely realize how to bring an issue to light and make it important.
    More people should look at this and understand this side of your story.

    I can’t believe you’re not more popular because you certainly possess the gift.

Leave a Reply