Speed up Excel: Increase Calculation Performance in 17 Easy Steps

Excel is a great tool for performing complex calculations. Unfortunately, the larger an Excel spreadsheet gets, the slower the calculations will be. Depending on the formulas, size of the workbook and the computer, the calculations may take up to 30 minutes. In this article, we take a look at 17 methods to save time and speed up Excel.

Let’s categorize the following advices by the effort needed to apply them and the impact on the speed. That makes us come up with the matrix on the right hand side.

The numbers 1 to 6 should be your starting point as they are easy to apply and usually have a high impact. Then you should try the numbers 7 to 13. These steps are a little bit more complicated as most of them require changes to formulas or the workbook structure.

Numbers 14 to 15 (methods with an easy application and a low impact) are usually also worth a try but in most cases not too successful. Concerning the last two methods (16 and 17): I recommend only doing them when all other ways are already applied.

1. Speed up by using all processors for calculating

By default, Excel calculates using all available cores. But sometimes, it only uses one processor. How to check, how many cores are being used? When calculating a workbook, in the right bottom corner the number of processors used will be listed, “Calculating” (=only one processor)  or “Calculating (4 processors)” e.g., see number 1 on the picture on the right hand side.

You can switch on calculating with all available processors by going to “File” (no. 2), “Options”, “Advanced” (no. 3). Scroll down, tick “Enable multi-threaded calculations” and choose “Use all processors on this computer” (no. 4). This should speed up your calculation immediately.

This will reduce the calculation time by app. -42%.

2. Close other programs

The obvious step: close everything, which uses unnecessary system resources. This can be internet browsers, music programs, other Microsoft Office programs. You should also check, which programs are running in the background.

The effect strongly depends on the programs you are running. Some basic Microsoft Office programs will reduce the calculation time around -1%.

 

3. Switch computer’s region settings to “English (USA)”

Our study show, that choosing the region “English (USA)” has an insanely big impact on the calculation time of Excel: For example if you are using the German number system (use comma as decimal separator), you can reduce the calculation time up to 81%. 

Sorry to all readers who already are on the English numeric system: You can’t increase the performance here.

4. Use the 64bit version of Excel to speed up calculation

This one is quite simple: Install the 64bit version of Office. Our test reveals, that you can save app. 9% of calculation time with the 64bit version of Excel. The condition: Your computer must support 64bit software. Also, make sure that third-party add-ins support the 64bit version of Microsoft Office.

5. Switch Excel to manual calculation

Roughly speaking, after each change in your Excel workbook, Excel will recalculate all changed cell and the ones depending on your changes. This can make your workbook quite slow, especially large workbooks and sophisticated formulas.

To avoid recalculating every time you change anything, go to “Formulas”, “Calculation Options” and choose “Manual”. Now, your workbook will only be recalculated when you save it or you press F9. By pressing Shift + F9, only the current worksheet will be calculated.

For more information about the different calculation options in Excel, please refer to this article.

6. Disable all Excel add-ins

Add-ins are often useful, but often slow down your Excel or even make them crash. Disabling the add-ins is easy:

• Go to “File”, then “Options” and then “Add-Ins”.
• Take a look at the enabled add-ins and disable them by selecting for example “Com Add-Ins”.
• Then click on “Go”.
• Untick all add-ins you don’t necessarily need. You can still enable them later on if you need them again.

7. Use faster formulas

In case you are using SUMIFS as a lookup formula (which is actually quite handy) you might want to reconsider. We found out, that VLOOKUP and the INDEX-MATCH combination are much faster than SUMIFS. So replacing SUMIFS by VLOOKUP or INDEX-MATCH (wherever possible) might reduce the calculation time by  app. 53%. 

Another example: =MAX(A1,0) is app. 6% faster than =IF(A1>0,A1,0).

8. Same cell ranges in formulas

Let’s assume, you got a function, which you apply to an entire column. The cell range within the formula is different or changes with each row, for instance =SUM($A$1:A3) (one part is fixed with $-signs and one part is not). Such formula makes your workbook very slow. Instead, try to apply formulas with always the same range (=SUM($A$1:$A$3)). If you really can’t avoid it, you should look for a workaround (for example the following method number 6).

9. Replace formulas with values

If you got formulas in your Excel workbook, that won’t change any more, you can replace them with values for increasing the speed. For example, you got input data and you are doing some first modifications/ calculations with them. Based on these calculations you are conducting your further analysis. You can replace them with values (but should save the formula once somewhere).

Its usually a good recommendation to set up one or two blank rows on top of the data or calculations. You can save the formulas here and the data below doesn’t contain any formulas but just values. Therefore, copy the data (still with formulas) and paste special (Ctrl + Alt + v) them, selecting “Values” in the paste special window.

10. Avoid conditional formatting

Formatting is not necessary for calculations. It’s just formatting. Of course, your file should look good, but if you are suffering from long calculation times, you should try to avoid conditional formatting. Especially, if you are using formulas within your conditional formatting. If you really want to use conditional formats, you should consider dividing your file into several small files, for example one just for your final results (see number 10). In the one of the final results – obviously without large calculations – you of course can use conditional formatting.

11. Avoid volatile formulas

Volatile formulas are being calculated every time, you change something in your Excel workbook, no matter, if there has been changes to them or their predecessors. The following formulas are volatile:

• NOW
• TODAY
• RAND
• OFFSET
• INDIRECT
• INFO (depending on its arguments)
• CELL (depending on its arguments)

As those formulas, especially INDIRECT and OFFSET are very powerful, you should consider well, if you can replace or omit them.

12. Avoid data tables

You haven’t heard of data tables? Don’t worry, in that case you can proceed to step no. 10. If you are a big fan of data tables, here is the bad news: They need a lot of performance. Try to avoid them, or at least don’t use too many of them. Of course, they are a great method for simulating different input values, but each one of them multiplies the calculation time. If you really want to have data tables, you could switch the calculation mode to ‘Automatic Except for Data Tables‘.

13. Divide the workbook into several files

Effective, but sometimes complicated: the separation of the workbook into several files. For example, you use one file for processing the raw input data. Within the second file, all major calculations are done and the third file would just display the results in a nice way.

14. Use several ‘small’ formulas

A little bit easier to use and maybe even simplifying your model could be using several “small” formulas in different cells or rows instead of one large formula within one cell.

Advantages:

• It’s much easier to understand the calculation steps, especially if you open your file after some time or somebody else tries to work with your workbook.
• Also, the calculation is slightly faster.
• You could replace the formulas of some of the “small” steps with values as described in number 6.

15. Delete worksheets and (formats) of unused cells

Often during the process of setting up a workbook, you’ll gather worksheets and cells which you don’t need any more. Walk through the workbook and check, which content you don’t need any longer. Delete it if possible.

Furthermore – as we are already talking about unused cells: The workbook is a supposedly small file, but it uses several MB on your hard drive? That might be a hint, that there are cells formatted, which aren’t used. Just delete all rows and columns after your last used cells and see, if the file size decreases after saving. At the same time, this reduces the calculation time.

You can speed up Excel up to 8%.

16. Optimize computer

Similar to number 2: Make your whole computer run faster. There are different methods, the easiest one is to set your (Windows-) computer to “best performance” instead of “best appearance”. Admittedly, especially on Windows 7, the desktop, menu bars and so on will look 15 years older. Follow the steps as shown on the screenshot on the right hand side:

1. Open the control panel and navigate to “System”. Once there, click on “Advanced system settings”.
2. On the “Advanced” tab, click on “Settings” within the “Performance” group.
3. Select “Adjust for best performance”.

Performance and speed should increase instantly.

Update: In our study we couldn’t measure any increase of performance. So maybe you skip this advice.

16. Avoid large ranges in formulas

Try to set the ranges in formulas as small as possible and only let Excel use ranges, which really contain necessary information. For example, if you are having lookups (like “=VLOOKUP”), minimize the range of cells Excel searches in. If you can, sort your data. 

This trick can reduce the calculation time by only -1%.

17. Move data into one sheet

Using several worksheets provides a clear structure of the whole workbook. But if the calculations are spread over many worksheets, Excel will also suffer performance. Especially for VLOOKUP, having the data within one sheet should speed up calculations.

Also changing the sheet order has a small impact on the performance: Excel calculates from front to back of your workbook. “Jumping” across worksheets and workbooks might decrease the calculation time. Our study shows, that you could decrease the calculation time by -1%.

Conclusion

Excel often takes a long time calculating, especially in large Excel models. The reasons are different for each workbook. Because of that, some of the steps described might work well and some won’t.

These 15 methods should provide a first reference. We suggest you to go through this list and decide, which advice is suitable for you. 

To make it easier to use these methods, we’ve prepared a handy printout (direct download).

Further reading:

Microsoft provides some in-depth articles about how Excel calculations work:

• https://msdn.microsoft.com/en-us/library/office/ff700515(v=office.14).aspx
• https://msdn.microsoft.com/de-de/library/office/bb687891.aspx