Speed Up Excel By Turning Off Calculations (And Other Tricks)

One of the most common problems with some Excel workbooks (and other types of spreadsheets) is painfully slow load times and updates. There are three typical cuprits: too many calculations, too much formatting, and unnecessary file overhead. Here's how to fix them.

Photo remixed from an original by r h.

If an Excel file is taking too long to load or update, there are a couple of things you can try:

  • Turn off automatic calculations. This can help in a case where your workbook is very complex and every change requires you to wait for Excel to perform all the calculations. Change the setting to manual workbook calculations in the Options dialog, and then you can hit F9 to update the workbook as needed.
  • Save the workbook as a new file and clear the formats. If the new file is much faster, too much formatting was the culprit.
  • Copy only the used cells into a new worksheet. Some Excel files may be bigger than necessary due to extra cells without any content being included in the worksheet.

For more tips on solving other Excel nightmares like difficulty working with multiple files or confusion when more than one user works on the file, see the Computerworld article below. Also, if you have any tips or tricks for other Excel users, share them with us in the comments.

Five Excel Nightmares and How to Fix Them [Computerworld]


Comments

    I use excel daily as a Data Analyst, and all are good tips.

    Both vlookup and countif are big offenders for bogging down Excel, so where possible flatten formula into hard data (Highlight and copy the cells/range, then with the cells still highlighted, select Paste Special, then values).

    By flattening formula once they've done their job, it will help Excel speed along as best it can. Only downside, is that it can be very hard to track what changes have been made to the data, and also makes it much harder to go back and fix your mistakes.

    Also, remember to save your spreadsheets regularly, using "Save As..." and duplicate your working documents. Undo and autosave are life savers, but they lack the flexibility of having multiple documents saved at various stages of your data processinging.

      +1 for copy and paste values trick.

      A 50mb excel file with a few pivot tables can mean some pretty long wait times for each and every change on a machine with 512mb ram.

      Also limiting the number of calculations using results of (previous) calculations saves lots of issues.

Join the discussion!

Trending Stories Right Now