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.
Have you ever built an app based on Excel?
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.
Have you ever built an app based on Excel?
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:
- 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‘.
Have you ever built an app based on Excel?
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.
- 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:
- Open the control panel and navigate to “System”. Once there, click on “Advanced system settings”.
- On the “Advanced” tab, click on “Settings” within the “Performance” group.
- 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.
Have you ever built an app based on Excel?
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%.
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).
Microsoft provides some in-depth articles about how Excel calculations work:
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.
Thanks a lot, I’ve corrected it.
Can you please explain more about the section 13. Please send some example file for better understanding to split the one file to several.
Thank you very much and its beautiful page,we liked it.
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.
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.
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
Thank you for your help. Please add vlookup to the list in #11.
Thanks for the comment. VLOOKUP is not volatile, though, so that it shouldn’t be added to number 11.
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
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:
K2: = K1+J2
L2: = L1+K2
which does just 59 additions, with zero function calls, to generate the same running sum.
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)
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.
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!
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?
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.
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.
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