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:
- 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.
- 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:
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.
- 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.
- 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.
- Styles:Old Excel files may collect Styles or Conditional Formatting and make the file grow. You need to clear unwanted formatting.
- 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.
- 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 -).
- 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.