The VBA for copy / pasting is just a loop with the below In my Excel settings I had all cores being used The number of addins installed is minimal
Uninstall old office software, reinstall new software and do a hard reboot to make it a fresh windows sessionĭone on the same hardware with no settings changed The above tests were done in identical conditions Į.g. Has anyone else seen this behaviour or know a reason why Excel 2010 would be faster than Office 365 at copy / pasting data as well as generally recalculating? So as can be seen, when there are lots of aggregation formulas Excel does speed up in the latest version and is fastest in Office 365 but in the last 2 examples Excel 2010 is generally faster at recalculating and doing a copy / paste / recalculate.
Office 365 before update = 0.194 seconds average recalc timeĮxcel 2010 = 0.137 seconds average recalc time Office 365 after update = 0.166 seconds average recalc time (sample same as above)
**Average recalculation time in the above model using Full Recalculation in FastExcelV3 addin** Office 365 before update = 8.052 seconds to solve Office 365 after update = 7.649 seconds to solve **Solve model which involved copy / pasting data 10 times using Application.calculate all in VBA** Taking just the 64bit versions (the result is similar in 32bit) i got the below times There are some aggregations but its a small % of the overall formulas. They are all compatible in 2010 as they don't use some of the newer features and most are quite optimised so don't use volotile formulas, no Vlookups / iferrors / sumproducts etc. Now here comes the issue i find, i decided to test some other models i have to see how quickly they recalculate and solve. So as we can see, Microsoft are correct there has been a big speed improvement with their aggregation formulas. Office 365 32bit = 0.730 seconds after update and 1.718 seconds after update Office 365 64bit = 0.625 seconds after update and 1.471 seconds before update Now if i create a file with 200k rows and 16 columns as well as 400 Sumifs formulas with 4 conditions and time the Full Recalculation speed using FastExcelV3 i get the below average times (sample of 10 and drop slowest and fastest time) Recent ClippyPoint Milestones !Ĭongratulations and thank you to these contributors DateĪ community since MaDownload the official /r/Excel Add-in to convert Excel cells into a table that can be posted using reddit's markdown.īase on a post on here a few days ago in which Microsoft claimed that aggregation formulas were now faster after a recent update in a recent update on Office 365 I decided to test some of my models in various versions to see if the update was faster.
Include a screenshot, use the tableit website, or use the ExcelToReddit converter (courtesy of u/tirlibibi17) to present your data.
You can select code in your VBA window, press Tab, then copy and paste into your post or comment. To apply code formatting Use 4 spaces to start each line This will award the user a ClippyPoint and change the post's flair to solved. OPs can (and should) reply to any solutions with: Solution Verified