2020 is a critical year for enterprises. Windows 7 went end-of-life and into paid extended support in January, and Office 2010 is going end-of-life in October, with no extended support, paid or otherwise, leaving organizations with their hands full.
With getting all your users migrated as soon as possible, you really do not need anything that can cause any lagging, incompatibility, display issues, and outright frustration for your users. To help with that, I want to make you aware of several Excel issues that you can encounter whether or not you are migrating away from Office 2010, or are already on a later on-premise version. So, let's get right to it.
1) Fewer Supported Themes Issue
The numerous amount of supported themes in Excel 2010 and 2013 were reduced to only a few dozen in Excel 2016. This reduction causes issues when you take a file created in an earlier version of Excel and open it in Excel 2016.
Because the theme is not supported, special fonts, international language packs, special characters, etc. will not be read properly, causing Excel 2016 to operate sluggishly inside the spreadsheet.
To fix this, Excel 2016 wants you to re-theme this file and then re-save it so it conforms to its supported themes, fonts, etc. Doing this for a few files is bearable, but doing this for a whole estate of files can be overwhelming.
Since each file and setup is different, there is no single concrete answer for how to best tackle this across an entire estate. However, it seems that having an Office 365 subscription in conjunction with Office 2016, or only using Office 365, opens up more themes to use, which mitigates some of these issues.
2) High-Resolution And Multiple Monitors Issues
Excel 2016 and other Office 2016 products can appear blurry or look like the wrong size when you have multiple monitors with different resolutions. This article from Microsoft documents and gives solutions to the issues (which require a later release of Office 365 and Windows 10) but not necessarily a fix for Excel 2016.
Another issue that can occur when using multiple high-resolution monitors and opening an Excel 2010 file in 2016 is the row height will change from 15 to 14.5. This is particularly problematic with files that have charts, graphs, or other visuals, as the file is expecting a row height of 15 and will have issues rendering, causing Excel to become sluggish. This isn't an issue when opening a file in Office 365.
One fix is to re-size and re-save the file, checking to make sure the formatting is where it should be. Another fix is to go into the display settings, turn off the option to extend your displays, and then turn the option back on again. However, as stated earlier, having this issue occur over multiple files across your IT environment can become a frustrating burden on your users.
3) Processing Speed Issues In Excel 2016
Microsoft published this article explaining several common issues that users were having when they upgraded from Office 2010/2013 to Office 2016. We'll discuss a couple of issues that cause end users to call IT for help with their slow files.
Flash Fill Issues
Top of the list in Microsoft's article is this solution for combating slowness: disable Flash Fill. First introduced in Office 2013, Flash Fill tries to use AI to predict and fill in the gap for equations, and this can cause a freezing effect. The article also suggests that opening the file the second time is faster than the first, as Excel does a full recalculation when opening a file saved in a previous version.
File Size & Complexity Issues
The article also points to issues with the size and complexity of the file itself. The more data that is entered into it, the slower it will process, especially if it is a VLOOKUP file with over 100,000 rows.
Having numerous Conditional Formats can also be to blame for a slow Excel experience, so Microsoft recommends eliminating any superfluous ones. You can also try cleaning up your Excel files as well.
Also mentioned in the Microsoft article, and in one of our earlier blog posts about Office 2010 going end-of-life, having macros that are dependent on VBA scripts can cause issues with later versions of Excel. Some of these issues will be unavoidable, as the files will be referencing VBA6 scripts from Office 2010 that don't exist anymore.
While fixing all the files that are using VBA is tedious, IT Automation, such as AppScan, can be used to search for all files that contain VBA code, allowing you to budget time and resources properly.
Some of the issues we delved into above can be fixed through Microsoft's Group Policy settings (GPO), so items like Flash Fill can be turned off for groups of users before it becomes an issue. However, for some of the issues we discussed above, it seems that, while Office 2016 will be supported by Microsoft through 2025 (monthly security updates), there are some inherent issues — mainly dealing with high-resolution monitors that won't be fixed.
Most of these issues don't exist or have been fixed in Office 365, which not only receives security and critical updates, but feature updates as well. This year, it will be important for organizations to upgrade away from Office 2010 and Windows 7, and as we blogged about recently, getting on Office 365 and Windows 10 as fast as possible is the best course of action.