Region Settings: Big Impact on Excel’s Calculation Speed
In our large analysis of how to speed up Excel, we’ve found an interesting fact: Changing the region settings of your computer has a big impact on the performance on Excel calculation. Unfortunately, finding this left us with more question than answers. That’s why we further investigated: Which region formats let Excel calculate faster? What is the reason? And what does it mean for you?
Contents
Method
How we measured
The test data.
The method for measuring the calculation time is almost identical to our previous test: We prepared a test file and wrote down the start and end time for one full calculation with a VBA macro. The only difference: Instead of 100,000 VLOOKUP formulas we just used half of it: 50,000 VLOOKUP formulas. The reason is simple: We wanted to save some time as for some region settings one full calculation would have taken up to 10 minutes.
Region settings in comparison
Overview of the tested region settings and their default formats.
For our test, we compared 33 different region settings. Important: We didn’t change the language of the computer or of Excel. Our Excel was always set to English and the computer in German. Only the region format changed (scroll down for learning how to change the region).
The format to choose has two parts: The language as well as the country. So for English, there are many different countries available. We chose “English (United States)” as the base case. In order to make sure that the performance of Excel doesn’t depend on the country, we also tested “English (UK)”, “English (World)” and “English (Germany)”.
Our goal is to cover all major languages with this selection. For some languages (as for English or Spanish) there are many more countries available. But our sample showed, that there are no differences of calculation speed depending on the exact country. Only the language seems to have an impact on the calculation time of Excel.
Results
The results: All other regions than “English” need at least x6 the calculation time.
The results from our test are shown on the right hand side. We got 4 main results when changing the region format.
Result 1: The calculation time varies significantly
As we already noticed in our first test, the calculation time depends highly on the region you’ve selected. Calculating our test file took between 3.1 seconds and 218.5 seconds. In order to put it into numbers: some regions took 70x the time for calculating our worksheets than “English”.
Result 2: Only English settings get fast calculations
It appears, that only the region “English” gets a good performance. All other region need at least 6x the time for calculating than English. Also “close” language region (as the European languages German, Italian, French or the Scandinavian languages) don’t have similar calculation speed.
Result 3: There are groups of calculation times
In our test we identified groups for different calculation times in Excel:
- The first group is our reference region “English”.
- The second group contains very heterogeneous regions with (as it seems) very different languages including Chinese, Greek, Korean, German, Japanese or Russian.
- Group 3 takes 12x to 23x the time for calculating our test file. Also this group is surprising, as it contains “Spanish” but “Portuguese” is in group 2.
- The fourth group needs more than 44x and up to 70x (!) the calculation time than English.
Hold on a second. Was this information helpful so far?
Connect with me on LinkedIn...
Boost your Excel skills: Learn the best Excel tricks and tutorials!
...or on other networks!
Twitter: Follow @professorexcel
Facebook:
Result 4: It’s just about the region format you select
The calculation speed only depends on the general format (1).
In our test, we only changed the “Region Format” (number 1 on the image on the right side). Nothing else. Windows was the whole time in German, Excel the whole time in English.
We did one more test: We selected the region “English (United States)” and changed the preferred formats manually to comply with the German number and date formats. Surprisingly again, there was no difference in calculation time. That means, that you can select the region format “English” and adjust it to your desired number and date format and still get good calculation speed.
Why? What is the reason?
Unfortunately, we don’t know for sure. We have some ideas but we aren’t sure if this is really the reason. That’s why we asked the Microsoft Excel team via reddit. Here is their answer:
The answer from the Microsoft Excel Team:
Other users also responded with some more explanation:
Or this one:
For anyone with not English regional settings: How to deal with this?
Many Excel user already use Excel (and their computers) with English region settings. But for those of us, who have set other regions (as we are simply located somewhere else), have to deal with very long calculation times. There are basically 2 different solutions:
Method 1: Don’t do anything.
Don’t do anything. Most Excel users won’t even feel the difference. Only if you work with large Excel files and complex calculations you will get the impression, that Excel is slow. So unless Excel doesn’t feel slow, don’t do anything.
Method 2: Switch your computer to another region.
The calculation speed only depends on the general format (1).
Switch to another region.
Actually it’s quite simple to switch to “English (United States)” (number 1 on the image on the right side). You can further adjust your desired format by clicking on “Additional settings” (number 2). Example: The standard German format has some differences towards the US format: Dates are shown like “26.11.2016” instead of “11/26/2016”. Also numbers use different decimal or thousands separators.
How do you get there? The fastest way to change the region: Type “Region” into the search field of Windows (under Windows 10 you can just click start and start typing). Windows then offers the system settings region. Click on it and set the Format to “English (United States)”. Sorry, the GIF on the right hand side is in German. It works the same way in English.
Alternatively (if the way by searching for “Region” doesn’t work) you can open the system settings. Next click on region as shown in the GIF on the right hand side.
Sorry again, the animation on the right hand side is in German. But the steps are similar in any other language.
Excel tips, tricks and tutorials.
1x per month.
No spam. Promised.
