There are various advice about how to speed up Excel. Also we’ve published an article about how to increase the performance of Excel (actually it’s our most read article). But we’ve asked ourselves: Which of these advice really help? Furthermore: How much time can you save with it? That’s why we measured how long Excel calculates under different conditions.
We found some surprising results.
The method for measuring the Excel performance
Our goal is not to get some laboratory results. We rather want to know, how Excel behaves under realistic work conditions.
Before we jump right in with the results we must take a quick look at the method. Please scroll down to the end of this article for the method and hardware in detail.
- Measuring is done with an Excel file with 100,000 VLOOKUP formulas.
- A VBA Macro determines the exact calculation time.
- Only one variable is changed at a time.
- The tests are done on two computers: A new Lenovo ThinkPad and a late 2013 (“medium-good”) MacBook Pro (the detailed specification are at the end).
- Each setting is calculated 6 times. The first run is removed as well as the slowest and fastest run.
- If the results are not as expected or the impact is especially large, the specific test was repeated under different settings.
Results of the Excel performance
64 bit Excel vs. 32 bit Excel
The first question: Is the 64bit version of Excel really faster than the 32bit version?
The results are quite clear: The 32bit version of Excel is app. 10% slower than the 64bit version of Excel. Or the other way around: The 64bit version is 9% faster than 32bit.
As of now, Microsoft still recommends going with the 32bit version. But the arguments supporting 32bit are mainly that some (older) add-ins and data connections might not support 64bit. So maybe you should just try it and see, if the 64bit version works for you.
Have you ever built an app based on Excel?
Optimized computer vs. not optimized computer
Windows provides the functionality to optimize the computer for best performance.
Therefore, go to settings, system and advanced system settings as shown on the image no. 1 to 3.
We tested one time with all tick marks set (“Optimized for best appearance”). Then we compared it to the option “Adjust for best performance” (no ticks set).
In our test we couldn’t verify that optimizing the computer for best performance really speeds up Excel. As there doesn’t seem to be a difference in calculation time we recommend you to stay with your preferred layout option.
Region Germany vs. Region USA
We actually didn’t plan this test but rather found out by coincidence. The regional settings seem to have a big impact on the performance of Excel.
The German numeric system uses commas as decimal character and points as thousands separator. Therefore Excel formulas don’t use commas for separating arguments but rather semicolon. A VLOOKUP in Excel with German regional settings looks like this:
We compared the region “German (Germany)” with “English (USA)”. Surprisingly, these settings seem to have a major impact on the calculation performance of Excel.
As the difference is so huge (+400 – +500% of calculation duration), we repeated this test under the three environments of the ThinkPad, Windows on a MacBook under Bootcamp as well as Excel for macOS.
More surprisingly, the difference seems to come up only under Windows.
A possible explanation could be that Excel first translates the formulas into “English” before really calculating them.
Such big difference of calculation times led us to investigate further: How are the calculation times with other region settings. We compared the top languages around the world and found even more interesting results.
The table on the right hand side shows the reduction of calculation time if you switch from a region to “English (USA)”.
Hindi, Punjabi and Arabic even reduce the time needed for calculating by even -97%. Choosing “English (USA)” instead of Spanish, French, German, Russian or Portuguese reduces the calculation time by around 80%. Only Japanese and China are almost as fast as English.
Number of processors
We assumed the following relation: The more processors you use, the faster Excel calculates.
Excel provides the option to choose the number of processors to calculate on. As our test computer has 4 processors, we could choose between 1 to 4 processors.
First of all, the relation of number of processors and performance turns out to be true. It seems like the difference in calculation time is between 63% and 94% higher with 4 processors than just one (highlighted in orange) .
But there is one surprise: Calculating on 3 processors (it says 3 threads on 4 processors) seems to be slightly faster than on 4 processors (highlighted in green). Unfortunately, we don’t have an explanation for this behavior. Therefore if you have an idea of why 3 threads are slightly faster, please let us know.
Have you ever built an app based on Excel?
Large file vs. small file
We always wondered, if the file size has an impact on the calcution time. That’s why we prepared a test file with many more worksheets, just containing hard values (no additional formulas). The “large” file had 25 more worksheets and had the file size 26,116 KB whereas the “small” test file was just 2,336 KB.
There seems to be a significant difference in performance: Calculating the same number of formulas in a large file seems to be significantly slower than in a small Excel file. So the conclusion: You can save up to 8% of time by removing old data from your file (the actual number of course depends on the amount of data you have in your file).
Close all other programs?
We often hear the suggestion to close all other programs in order to speed up Excel. But do other programs really have an impact?
We let our test file calculation once without any other (obvious) software in the background and tried to simulate some “normal” working environment with the following programs open:
- Microsoft Outlook
- Microsoft PowerPoint
- WhatsApp Desktop
- Microsoft OneNote
We didn’t use these programs but just let them stay opened in the background.
Our test revealed that closing all other programs in the background reduces the calculation time by app. 1.2%. Of course, this highly depends on your hardware and the CPU and RAM usage of the other programs.
2 steps in 2 formulas vs. 2 steps in 1 formula
Is it better to have an additional column or to put two formulas into one Excel column?
In order to answer this question we’ve set up a VLOOKUP within a VLOOKUP. So basically the result of the first VLOOUP is the search value for the second VLOOKUP. We compare two options:
- Each lookup formula in 1 column so that the first column with the first VLOOKUP is the input value for the second column. The formula is divided into two columns.
- Having a longer formula within 1 column.
The direct comparison shows: Having a longer formula in 1 column is slightly faster. You can save app. 0,5% of calculation time.
Do you want to boost your productivity in Excel?
Get the Professor Excel ribbon!
Add more than 120 great features to Excel!
Which lookup formula is the fastest?
There are basically 3 different formulas for conducting a simple lookup:
We’ve already evaluated in detail which formula to use in which case. The only aspect we haven’t really talked about is the performance.
The difference between VLOOKUP and SUMIFS as well as INDEX-MATCH and SUMIFS seems to be very clear: The calculation time of SUMIFS is app. 90% to 120% longer than either VLOOKUP or INDEX-MATCH (highlighted in orange color). If we put it the other way: VLOOKUP reduces the calculation time by 53% towards a SUMIFS.
The difference between VLOOKUP and INDEX-MATCH seems just minor and it seems under different condition, INDEX-MATCH is in some cases faster and in other cases VLOOKUP shows a better performance (highlighted in green color). That’s also the reason why we repeated this test 4 times under different conditions.
SUMIFS vs. SUMIF
Is there a difference between the “old” SUMIF and the “newer” SUMIFS?
In order to compare these two formulas, we only used SUMIFS with one criteria. So both formulas have 3 arguments each.
The results: SUMIF seem insignificantly faster. You could save around 0.1% of time by using SUMIF instead of SUMIFS. That’s why we recommend only using SUMIFS. It has more advantages towards SUMIF as the structure is clearer and – of course – you can use up to 127 criteria.
MAX() vs. IF
This is quite a specific case: The two following formulas get the same result:
But which one of them is faster in terms of calculation speed?
In our test, the second option (MAX) is app. 6.3% faster. So if you use this IF function a lot, you might want to replace it with the MAX formula. But please keep in mind that 100,000 rounds of calculation just take 0.05s. So unless you aren’t having a workbook full with this formula you won’t save much time in absolut numbers.
Have you ever built an app based on Excel?
Windows 10 vs. macOS
Honestly, we are not sure how much you can compare those two systems in terms of Excel calculation time. In order to at least get an indication of the performance, we used a MacBook having both macOS Sierra and Windows 10 (Anniversary edition). Windows runs under Bootcamp. Both systems have their latest versions of Excel.
Our test shows once again: the regional settings are more important than the operating system. With the region English (USA) macOS is much slower (needs almost 90% more time).
If you set the region to Germany, macOS is significantly faster.
In conclusion: If you choose the region USA you should use Windows under Bootcamp for your Excel calculation. If you choose Germany, stay under macOS.
Especially with old versions of Excel it was recommended to choose the sequence of worksheets carefully. Excel was supposed to calculate faster if the formula sequence was aligned with the worksheet order: The formulas on the second worksheet should depend on the first worksheet. Consequently the formulas on the third worksheet should depend on the second and so on.
So is this rule of thumb still up to date?
Our test shows that if the worksheet with the formulas (or results) is located on the right side of the input data, you can save 0.6% of time.
Faster computer: 2013 MacBook vs. 2016 ThinkPad
This test only has limited validity. But it’s still interesting to see how an older MacBook (late 2013) compares to a new ThinkPad (2016).
As assumed, the ThinkPad is faster. The MacBook with Windows under Bootcamp needs 36% more time for calculating. Or the other way around: The ThinkPad needs 26% less time than the MacBook.
One interesting side observation: As a result of the different cooling systems of the two computers, the fan of the ThinkPad was running the whole time on high power. Opposite on the MacBook: the fan didn’t turn on during the whole test.
We measured which tips actually increase the performance of Excel.
- The biggest impact on the calculation time is easily achieved: Switch your computer to the region “English (USA)” (if not done yet).
- Avoiding the SUMIFS formula for lookup also cuts the calculation time by half. Of course only if you use it a lot.
- If you have the choice: Use Windows instead of macOS for your calculation.
- This one is possible for most users: Make sure you calculate on all available processors (or in our case also 3 threads lead to a slightly faster performance).
- Surprisingly, a faster computer (in our case MacBook Pro late 2013 vs. 2016 ThinkPad) “only” decreases the calculation time by 26%.
- If you can, switch to 64bit Excel instead of the default 32bit version.
- Also, now it might be time to remove all old data from your Excel file.
- Just a minor effect, but still -6% of calculation time: Use =MAX(A1,0) instead of =IF(A1>0,A1,0.
- All other advice only have a minor impact. For example closing all other programs, using the exact cell ranges, changing the worksheet order or optimizing the computer for best performance.
Have you ever built an app based on Excel?
More information about the method, hardware and software
The method and environment in detail
The measuring is done with a simple VBA macro. Simplified speaking, it writes down a start time stamp, initiates a full recalculation and also notes the time when done. In order to eliminate outliers and getting more stable results, we eliminated all data with the following criteria:
- Each specification runs 6 times.
- The first result will be removed.
- Of the remaining 5 runs the slowest and fastest were also eliminated.
- Of the remaining 3 runs, an average time is calculated.
- All add-ins were disabled.
In order to be able to compare the results, a base setting was defined. For each simulation, one variable was changed. The base case:
- Excel, 64bit.
- German regional settings.
- Small file without additional data.
- Base formula: 100,000 x VLOOKUP.
- The test file contains three worksheets (in this order):
- The VLOOKUP formula on Sheet1.
- The data for the VLOOKUP on Sheet2.
- The results (time stamps) on Sheet3.
Download the test file
Please feel free to download the test file and do your own analysis. Just press start and Excel will do 6 full calculations. We already prepared some columns but you can of course modify them. Only column C and D must stay the same as the start and end time will be written here.
- File name: Test_File_Performance.xlsm
- File size: 2.4 MB
We used these versions of Excel: Excel 2016 64bit, Excel 2016 32bit and Excel Mac 2016.
Notebook 1: Lenovo ThinkPad
- Processor: Intel® Core™ i7-6500U CPU 2.50 GHz
- RAM: 8.00 GB
- Operating System: Windows 10 Pro, “Anniversary Edition”
Notebook 2: MacBook Pro, Late 2013
- Processor: Intel® Core™ i5, 2.4 GHz
- RAM: 8.00 GB
- Operating System: macOS Sierra (10.12.1) and Windows 10 Pro, “Anniversary Edition” with Bootcamp
Thank you for this – very useful article, particularly for somebody trying to figure out how to make using Excel on a Mac a better experience (looks like Bootcamp is the way to go)
Thanks for the feedback! For me, Bootcamp works quite well.
Please feel also free to use the test file for your own setting. You can download it above or (slightly improved) from this page: http://professor-excel.com/performance-book/
In response to this,
Therefore if you got an idea of why 3 threads are slightly faster, please let us know.
Well, my guess would be that having three threads, it takes three cores for them, the fourth core is use by the operating system.
Where as if we use 4 threads, the 4th core has a conflict with the system.
Does that make sense?
Thanks for the feedback. Unfortunately, there is not much information about this issue available. That leaves me with guessing. Therefore, your idea could be correct. I would just like to add, that it highly depends on your workbook. As far as I know, Excel tries to divide the calculation performance by the calculation chains. Because of that, the performance could differ for different workbooks.
Can I also encourage you to measure it for your own workbook and environment? The test workbook can be downloaded above (or slightly improved from this page: http://professor-excel.com/performance-book/) Please let me know what you find out 🙂
thumbs up for the article.
it would be interesting too, if by any chance you guys will test out the excel performance difference between laptop vs desktop running similar specs (quad-core processor, 4gb ram, ssd) 🙂
Great article someone who lives and breathes spreadsheets. Thank you for the depth of the research.
Thank you . This is very useful artical
Thanks for this. Very interesting and useful. There was one aspect I was interested in but that wasn’t included though. I don’t know if you’ve investigated this before or since. Do you know if, all else being equal, more RAM or a faster CPU make more difference?
For example, an Intel i5 with 4GB or 8GB RAM. vs an Intel i7 with 4GB or 8GB RAM?
I’d be interested to see which PC components give greater speed for Excel.
Thanks though. Good read!
I have one problem with excel on my brand new laptop. It is not working very smooth, when I scroll faster there are grey area showing up for a moment, it looks like the data on the screen is refreshing to slow. I have UHD display, maybe this is a problem. The laptop is Thinkpad T570 i7 7600, UHD display, 16GB RAM DDR4, Invidia GeForce 940mx, 512SSD.. So it is laptop with good specs but excel is working worst than my 8 years old HP. Do you have any suggestions? Thank you for your help, Jure
after several tests with different Excel versions this is my conclusions:
-Windows Server 2016
-Excel 2007 (32bits) Excel 2013 (32 bits) Excel 2016 (32bits) (64 bits discarded due to slow performance)
-Excel Workbook with 54 sheets and some vba macros.
Faster Excel with VBA code is Excel 2007.
But the fastest combination I found is Windows Server 2003 (32bits) with Excel 2007 (32 bits).
And … If you put the affinity to only one CPU, the time is 50% faster. INCREDIBLE…
All these test have occupied me two weeks.
Finally in my Company we are working with Excel 2007. Better versions, the older versions.
I was very surpised by the speedgain due to the language setting. Since I have many large sheets which run a considerable amount of time, I decided to take some tests too with my sheets, using your “VBA-Macro_Measuring_Calculation_Time” sheet. In all my cases I did not noticed any speedgain at all. Only your example with the 100K vlookups was way faster. In many of my sheets, calculations are performed by VBA code.
Thanks for your inspiring work!
Jan van Galen
asus N76VM i7 8Gbyte, Excel 2010/32bit
Very interesting results. Thank you for doing this work and publishing for us to learn.
Did you try English(USA) against say English (UK) or English (India)?
Basic numerical formats remain the same but dates differ, so I wondered if they still do some “translation” between them.
Thanks for testing!
My time was 1.5s! Yes 1,5 seconds.
Either 2016 office is so much faster or then i5-8400 cpu is on another level compared to older cpus.
Can you mention the processor difference between the Mac book pro vs the thinkpad. I’ve noticed almost a 1:1 relationship between the processor benchmark scores at cpubenchmark and the calculation performance in Excel. i.e. a I7 6700hq is 70% faster than a I5 6300u
Also can you compare Excel 2010 vs Excel 2016. My own test show a performance difference of 30% in favour of Excel 2010. Can you confirm?
It might be interesting to use similar techniques to compare excel and libre office calc.
Thanks, just managed to go from 10 minutes to 30 seconds calculation time. Especially SUMIFS to VLOOKUP made a huge difference. I was using a list of 500k lines to look stuf up 🙂
One test you forgot to do is
100000 lines of 100 columns with random data while say 40% of one column has same data, You filter the data where you have 40% of the data in random rows (non-sequential). Then try
– Filter the 40% data column then Delete Rows
– Sort the 40% data column then Filter and finally delete rows
This will be NIGHT and Day in performance… like HDD vs SSD in performance.
Thanks for your reply. I’ll keep that in mind and – if I’m eventually do a follow-up study – add this to the test. But please feel free to use the measuring workbook and share your results! 🙂
Comparison between 4 comps:
i3-6100u loaptop 7,8s and after 0365 1809 build time dropped to 0,16s! Now it is only 0,16s.
i7-4800mq laptop 40-44s
i7-8650u initially 45-50s (I dont know why it is slower than i7-4800mq). After couple of days use with new computer benchmark time dropped to 2,9s with same office build 1803
i5-8500 desktop 1,5s with office build 1803
–>office build 1809 is MUCH faster in this test. Stragely my slowest computer is fastest in this test!
I also made a few tests switching the Display language from French to English. Gain of roughly 10%.
desktop from 2015 – intel i5 4690k @ 4000 MHz 16 GB of RAM
@Peluchi Interesting results. However, I’m wondering why you chose to use the 1809 build just for the slowest i3 PC, instead of limiting the variation and using the same build across all PCs tested. I’m planning to test this myself when I get home as well.
Does anyone know if more RAM would affect the performance of these tests?
This is very interesting. I was wondering if formulas performed faster if for certain cases you had the first calculation return zero rather then calculate the whole long formula out? For example say you had four columns with really long formulas for the overall rating of a Pitcher, Outfielder, Infielder, or Catcher. Would it perform faster if you surrounded the “Calculate OVR Pitcher” column of formulas with an IF(Postion = “Pitcher”, Calculate OVR Pitcher, 0) instead of just having the Calculate OVR Pitcher formula?
This may be sacrilegious, but have you any thoughts on a similar test for G***le sheets?
I’ve but trying to determine how much the client machine’s HW matters vs how much is server side.
my searches led me here, however the macro does not import.
If nothing else, a comparison of the two could help.
Do you mean : “language English (USA)” ?
not “Region English (USA)” ?
In the Windows settings it’s called “Region”. It’s located under “Format” under “Region and Language”. Please refer to this article for more information: https://professor-excel.com/region-settings/
Here is a relatively current MS doc about optimization improvements in Excel since 2010
2020 06 08 Performance and limit improvements
Excel M365 introduces new features that you can use to improve performance when you are working with large or complex Excel workbooks
. * SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, MINIFS Improvements: 2.5x’s 2010-2020
. * RealTimeData Function (RTD): 8x’s 2010-2020
. * VLOOKUP, HLOOKUP, MATCH improvements: 4x’s 2010-2016
. * LAA memory improvement for 32-bit Excel:
. * Full column references: 2x’s less RAM 2010-2016
. * Structured references: seconds down to milliseconds 2010-2016
. * Filtering, sorting, and copy/pasting: 9x’s 2010-2016
. * Copying Conditional Formats: 10x’s 2010-2016
. * Adding and deleting worksheets: 10% slower 2010-2016 (oops)
. * New Functions (MaxIFs, MinIfs) “better” than older array functions
. * Large data sets and the 64-bit version of Excel
. * Shapes: 2010 significant improvements
. * Calculation improvements: 2007 added multithread, 2010 additional improvements,
. * Multi-core processing
. * PowerPivot
. * HPC Services for Excel 2010
Thanks for your study. However, where to find best practice on using Indirect. Indirect seems to be only way to implement generic formula; Tables are great but difficult to have named formulas. Microsoft encourage named formulas but I am not aware of better support for Indirect.
Thanks for your study. I am also looking for self-test tool for non-admin user to avoid erratic behavior. If there is a tool to alert me to reboot, maybe this will avoid the trouble e.g. Excel hangups.
There is Windows 10 Task Manager which can provide performance details need admin rights.
Thanks for your study. is there a way to suspend Excel function (not UDF nor VBA) calculation? This allows non-admin user to do other Apps when need arises. Windows 10 should multi-task with reliability but I experienced Excel frozen when I tried to open other Apps while Excel is calculating…