Optimize performance in Excel 2010

  • Article

If you're an Excel 2010 power user or just want some insight into how certain features can affect performance, you will enjoy this MSDN article:

Excel 2010 Performance: Tips for Optimizing Performance Obstructions

Here are some quick tips from the article to help you get started:

  • Save as typeUse the XLSB file format for slightly faster opening and saving over XLSX, as well as smaller file size. Saving as XLS can seem faster but loss of some optimizations in the newer formats can cause calculation to be slower, plus there is a compatibility check on each save.
  • Accumulation of temporary files can slow workbook opening and closing. If this seems to be an issue, you can go to Start and type %temp% in the Search box to open this folder and clear old temp files. For safety's sake, restart your computer before deleting any temp files since some software installations requiring a reboot might need some of these files.
  • Use PivotTables when possible.
  • Only include cells containing necessary data in ranges.
  • Avoid formulas that reference other formulas that occur after (to the right or below). In some cases, this can cause calculation to lag (or be deferred) until later formulas are resolved. Try to build a natural flow or progression for your formulas. Likewise, avoid circular references (formulas that reference themselves).
  • Avoid links between workbooks. These can be slow and may eventually fail (especially if they reference documents that require a network connection and you're working offline). If you need to use linked workbooks, try to have them all open (it's best if you open any linked ones first before the one doing the linking).
  • Minimize the used range. Sometimes empty rows and columns get logged as in use, even when they're empty. If you want to check how much of the workbook Excel is tracking, use Ctrl+End. This highlights the last cell that is in use. If there are extraneous rows and columns, make a backup copy of your document and then delete any you think you don't need. Then check your data to be sure you didn't lose any important ranges. If possible, use whole column references (e.g., $A:$A) to ensure that all data in the column is always included.
  • Try to keep lookups to the same worksheet, and pre-sort your data.
  • Array formulas should reference the least amount of cells and expressions as possible to perform the intended function. Be aware that any change to even one cell that an array formula references will trigger a full recalculation. If possible, use SUMPRODUCT, SUMIFS, COUNTIFS, or AVERAGEIFS instead of an array formula.

Read the full article for more details, examples, and tips.

Suzanne