Speed up Excel: Increase Calculation Performance in 17 Easy Steps

speed, up, excel, performance, calculation, speeding

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.

overview, speed, up, excel, ways, methods, impact
17 Ways to Speed up Excel: Overview

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

excel, calculate, all, processors, cores, speed up excel
Calculate with all available cores

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

manual, calculation, excel, settings
Use the manual calculation of Excel

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.


Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Add more than 120 great features to Excel!


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

excel, formula
Divide a long formula into several short 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

best performance, windows, settings
Optimize your Windows Computer for best performance instead of best appearance

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

speed up, speed, performance, excel, preview, pdf, 15 steps
Printout: Speed up Excel in 15 simple steps

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:

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

20 comments

  1. Hi, could you fix up the wording of the fifth section so it makes sense please? (5. Same cell ranges in formulas)
    It looks like some words have accidentally been deleted from the paragraph.
    Cheers
    John

  2. Hi

    Can you please explain more about the section 13. Please send some example file for better understanding to split the one file to several.

  3. Hi

    More often than not volatile formulas, conditional formats and other useful excel features are needed to analyse large data sets. If you stop using the features of excel that make it useful there is no point in using excel. Hence the solution cannot be to stop use the features of excel that makes it useful.

    1. Hi Mayhem,
      you are right, you should consider well if you want to remove useful and essential features from your Excel workbook. But to my mind, the speeding-up process has several steps: First you need to find out what feature/worksheet/table slows down the calculation. Especially formulas (e.g. lookups) within conditional formatting rules should be evaluated. As the next step you have to decide, if you want to keep that feature and try to optimize less important parts of your workbook. Also you could just temporarily remove or also optimize them. For most features there are alternatives (maybe less beautiful).
      In order to cut a long story short: I still believe you should at least investigate those features you mentioned. Only that way you can make a well considered decision of how to speed up your workbook.
      Best regards,
      Henrik

  4. Hello Henrik,

    I have problem also with very slow excel workbook. Can you please be so kind and check what is wrong with this file if I send it to you? I have try almost all your methods. The file is my own product and nobody can explane why is hapening or processing everything so long. Copying of one cell can take to 5 minutes.

    Thank you in advance

  5. Thanks for this. After upgrading to Windows 10, I was experiencing a issue with a very large spreadsheet, that was spending minutes recalculating every time I filtered a column. Changing my calculation options to manual has resolved the issue. Cheers CRK

  6. within item 7 also point out: “that VLOOKUP and the INDEX-MATCH combination are much faster than SUMIFS.”
    You might also note that INDEX(,MATCH()) tends to be slower than HLOOKUP or VLOOKUP,
    while HLOOKUP and VLOOKUP tend to be slower then helper cell=MATCH() followed by =INDEX(,helper cell).

    within item 7 also you point out: “Another example: =MAX(A1,0) is app. 6% faster than =IF(A1>0,A1,0).”
    A further speedy example would be =(A1)*(A1>0) which drops the function all together.

    within item 8 it is worthwhile to remind users that a row of running sums across 60 columns does
    60 function calls with
    1770 [=+(60)*(60-1)/2] additions across the row.
    This is much slower than, say:
    J2: =J1
    K2: = K1+J2
    L2: = L1+K2
    M2: =M1+L2
    which does just 59 additions, with zero function calls, to generate the same running sum.

  7. how to calculate internet download speed in Excel sheet total time of downloading of some file of movies if we enter the exact speed (ex : 170 kb per sec)

  8. Thank you so much for this. #8 was incredibly helpful. I was struggling with unmerging a worksheet with 53k rows and about 30 columns so I could sort as needed. When selecting the entire worksheet to unmerge this took forever. However, #8 gave me the idea to select each column individually to unmerge. This cut the time down to about 5 seconds.

  9. Hello.

    you should change “17. Move data into one sheet”

    I personally have used this technique and found it reduced my programs runtime from an hour and a half to 2minutes 45 seconds. All by merging data from one worksheet to another, and then running my macro.

    I can personally vouch for its effectiveness. And would implore all users who have macros that process a bit of data on one sheet, automatically paste the evaluated data onto another worksheet, then return to the original worksheet for more data processing etc etc to merge all the data. It works really well!

    Rowan.

  10. re Tip #8 – Does this apply across a sheet as well as a column? For example, if using a vlookup in two columns is there a benefit to using the same range and changing just the reference column or does this make little difference? eg vlookup(A2,’sheet2’$A$1:$F$200,3,false) and vlookup(A2,’sheet2’$A$1:$F$200,6,false) vs vlookup(A2,’sheet2’$A$1:$C$200,3,false) and vlookup(A2,’sheet2’$A$1:$F$200,6,false)

    re Tip #5 – Once manual calculation is on, is there a benefit in calculating prior to saving?

  11. Interesting article. Would be interesting if you could retest to see how changes both in hardware and software (MS is constantly “improving performance”) to see how relevant the suggestion are.
    Note: original article obviously had 15 points. The text of your intro only mentions 15 points, so the categories in the text no longer completely match the the grid chart.

    It would be nice if you included the specs of the computers you used for testing. Would the be the same ones you used in the followup article?

    PS: personally I strongly prefer articles that have the date at the top. That way it is easier to estimate the relevance of the article to the present. For example, your followup article a year later “Performance of Excel: Study Shows How to Speed up Excel by 81%” used an i7-6500U CPU for testing. It’s now 5 years later and leading edge Intel is iX-11###, “11th Generation”. And Excel has 1 (2019) almost 2 (2021 “soon”) and 365 generations of “improvements”.
    .
    Keep up the good work.

    1. Hi Ron,

      Thanks for your feedback! I have updated the intro accordingly (15 vs. 17 points on the list). And yes, I fully agree that it would be very interesting to update the article (and the “study” I link to) with current specs.

      Best regards,
      Henrik

  12. PS: one issue your raised is regions. MS haven’t commented on that, yet.

    I’ve submitted a “feedback” to MS about this question. You may want to add your own comments to it: https://aka.ms/AAd9cfx

Leave a comment

Your email address will not be published. Required fields are marked *