There are a number of neat tips that'll help you out when you're managing your Excel spreadsheets. For example, if you have one worksheet with names and phone numbers and another sheet with names and email addresses, you can put the email addresses next to the names and phone numbers by using VLOOKUP. Using VLOOKUP, you can not only search for individual values, but also combine two worksheets into one. Enter the value whose data you're searching for. * Large data sets and the 64-bit version of Excel * New Functions (MaxIFs, MinIfs) “better” than older array functions * Adding and deleting worksheets: 10% slower 2010-2016 (oops) * Copying Conditional Formats: 10x’s 2010-2016 * Filtering, sorting, and copy/pasting: 9x’s 2010-2016 * Structured references: seconds down to milliseconds 2010-2016
* Full column references: 2x’s less RAM 2010-2016 * LAA memory improvement for 32-bit Excel: * VLOOKUP, HLOOKUP, MATCH improvements: 4x’s 2010-2016 * SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, MINIFS Improvements: 2.5x’s 2010-2020 Here is a relatively current MS doc about optimization improvements in Excel since 2010Ģ020 06 08 Performance and limit improvementsĮxcel M365 introduces new features that you can use to improve performance when you are working with large or complex Excel workbooks The test file contains three worksheets (in this order):.For each simulation, one variable was changed. The base case: In order to be able to compare the results, a base setting was defined. Of the remaining 3 runs, an average time is calculated.Of the remaining 5 runs the slowest and fastest were also eliminated.In order to eliminate outliers and getting more stable results, we eliminated all data with the following criteria: Simplified speaking, it writes down a start time stamp, initiates a full recalculation and also notes the time when done. The measuring is done with a simple VBA macro.
More information about the method, hardware and software The method and environment in detail For example closing all other programs, using the exact cell ranges, changing the worksheet order or optimizing the computer for best performance.
If you have the choice: Use Windows instead of macOS for your calculation.Avoiding the SUMIFS formula for lookup also cuts the calculation time by half.The biggest impact on the calculation time is easily achieved: Switch your computer to the region “English (USA)” (if not done yet).We measured which tips actually increase the performance of Excel. More surprisingly, the difference seems to come up only under Windows.Ī possible explanation could be that Excel first translates the formulas into “English” before really calculating them. We compared the region “German (Germany)” with “English (USA)”. Surprisingly, these settings seem to have a major impact on the calculation performance of Excel.Īs 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. A VLOOKUP in Excel with German regional settings looks like this: Therefore Excel formulas don’t use commas for separating arguments but rather semicolon. The German numeric system uses commas as decimal character and points as thousands separator. The regional settings seem to have a big impact on the performance of Excel. We actually didn’t plan this test but rather found out by coincidence. Under Windows, the regional settings have a major impact on the calculation time of Excel.