Slipstreamed Excel 2016 Features Pose Compatibility Challengesby
Historically all Excel users operated on the same playing field, and if you’re an Office 365 subscriber, your version of Excel is gaining new features on a sometimes-monthly basis that aren’t available to users who have perpetual licenses for Microsoft Excel.
For decades, software companies would cajole users into buying periodic upgrades. Many users would oblige, but others would take the “it’s not broken, so I’m not going to fix it” approach. As time goes by, the number of software choices we have is both getting smaller and larger: smaller on the desktop front, larger on the “there’s an app for that” front.
Consolidation on the accounting software front has left us with only a handful of choices. Google Sheets is trying to give Excel a run for its money, but many of us can’t carry out our day-to-day work without using Excel. Renting software through subscriptions, instead of one-time purchases that allowed us to hold onto a given version with a death grip, is the new normal.
If you’re using Excel 2013 or earlier then you have parity with everyone else using the same version of Excel. This means Excel 2013 users in particular can use Slicers with both pivot tables and tables, filter pivot tables with the Timeline feature, and avoid repetitive data entry with Excel’s Flash Fill.
Excel 2016 has all this and more, such as the Tell Me feature that eliminates the Easter egg hunt approach of scouring Excel’s menus to find that feature or function that was so helpful a few months ago but has since slipped your mind. When Excel 2016 was first released, a subsequent software update added six new chart types: Treemap, Waterfall, Pareto, Histogram, Box and Whisker, and Sunburst. These charts work in any version of Excel 2016, but are not viewable in Excel 2013 or earlier. We’re accustomed to such new features not being backwards compatible.
What’s new is since then, Microsoft began slipstreaming other new features that are available based on how one chooses to pay for Excel. The term “slipstream” means adding new features or introducing bug fixes to a software program subsequent to its initial release.
Slipstreaming is widely used among apps for mobile devices and cloud-based packages. Log into QuickBooks Online, for instance, and you may encounter a whole new user interface one fine day. Microsoft’s euphemism for bug fixes or feature improvements to its software were known as service packs. The number of service packs for Microsoft Office has trailed down from three for Office 2007, two in Office 2010, one in Office 2013, and thus far none for Office 2016.
The service pack model seems to have been replaced by software updates that occur as often as monthly that add new features to Excel and the other Office 2016 applications. These can be a boon for those of us who thrive on living on the cutting edge of Excel.
No longer do we have to wait three years between major overhauls of our favorite business tool. However, there’s a cost involved here, in that it’s becoming easier to use features in your version of Excel 2016 that will result in ignominious “your Excel version doesn’t support this” type prompts as shown in Figure 1. If you try to share a Funnel chart with someone who isn’t using a subscription-based version of Excel 2016, they’ll receive this prompt:
Figure 1: A Funnel chart in a subscription-based version of Excel 2016, and what will appear in all other versions of Excel.
Many of the slipstreamed improvements in Excel 2016 are focused on Microsoft’s burgeoning suite of business intelligence features, namely Get and Transform (nÃ©e PowerQuery). However, mainstream features are being affected as well. For instance, Office 365 subscribers can take advantage of these new worksheet functions: TEXTJOIN, CONCAT, IFS, SWITCH, MAXIFS, and MINIFS.
If you open a spreadsheet that utilizes these functions in either a perpetually licensed version of Microsoft Excel 2016 or an earlier version of Excel, #NAME? will appear in the affected cells instead of that clever new worksheet function you chose. Fortunately there are a couple of easy ways to identify compatibility issues before you share a spreadsheet with others.
First is to determine how your version of Excel is licensed. If you’re using Excel 2010 or earlier, you have a perpetual license. In Excel 2013 and later:
1. Choose File.
2. Choose Account.
3. If as shown in Figure 2 the words Subscription Product appear along with Microsoft Office 365, then you very likely have new features and capabilities that many other Excel users don’t.
Figure 2: The Account screen in Excel 2013 and later identifies subscription-based versions of Excel.
To confirm categorically if you’ve used any potentially incompatible features:
- Choose File.
- Choose Info if necessary. This section of the menu appears automatically if you’ve saved a document at least once.
- Click Check for Issues.
- Choose Check Compatibility.
- Click Find if an issue is noted.
- Review the cell or object that is selected to identify the incompatibility. In the case of the IFS function shown in Figure 3 alternatives include using the CHOOSE function or rewriting the formula as a series of nested IF functions.
Ideally you’ll encounter a prompt that says, “No compatibility issues were found,” but instead you may see something that looks like Figure 3. If you look closely at Figure 3 you’ll notice the irony of Excel 2016 reporting that a worksheet function in the spreadsheet is indeed incompatible with Excel 2016.
Figure 3: The Compatibility Checker identifies features you may have used in one version of Excel that are incompatible with other versions of Excel.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.