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.
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:
=VLOOKUP(A2;B:C;2;FALSE)
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.
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
- Spotify
- WhatsApp Desktop
- Microsoft OneNote
- Chrome
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:
- =IF(A1>0,A1,0)
- =MAX(A1,0)
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.
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.
Sheet order
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.
Summary
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.
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:
- ThinkPad.
- 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
Notebook specifications
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