Excel 2020: Subscribe to Office 365 for Monthly Features
October 14, 2020 - by Bill Jelen
From 1997 to 2013, there was a predictable release cycle for Microsoft Office. A new version would come out every three years. Your IT department would wait one year for the Service Pack to come out and then would consider upgrading.
All of the Office apps had to ship on the same day. This created pressure on each individual team to have their features ready in time. No one wanted to be the person who caused the Office release date to slip. Some great Excel features got ripped out of the product because the team wasn‘t sure they could complete those features in time. If a feature did not make it into an Excel version, there would not be another opportunity for three years for that feature to make it into the product.
Today, the paradigm has changed. The Excel team has an opportunity to release a new feature to Office 365 on any given Tuesday. If a feature is not quite done this Tuesday, it only slips seven days, until the next Tuesday. There is no pressure to have 15 new features all ready on a certain day.
The net result is that Office 365 subscribers are getting access to many more features. While it can be annoying that my Excel book is obsolete the Tuesday after it prints, I appreciate getting great new features every month instead of every three years.
Your IT department still has a measure of control over when you get the features. There are several channels for distributing features:
- People who are not doing mission-critical work and want to test the latest bits can sign up for the Insider channel. On any given Tuesday, my Excel can experience bizarre bugs. One day, the Pivot Table Fields List stopped updating.
- People who like to see new features but who don‘t want to find themselves without Office can sign up for the Monthly Channel (Targeted) channel. Features appear in this channel about four weeks after they appear in the Insider channel. By that point, any bugs discovered by the Insider people have been resolved. You get new features quickly, but you won‘t see bugs that cause parts of Excel to stop working.
- Your IT department is likely going to have you on the semi-annual channel. These people get features every 26 weeks. The features have been tested by millions of other customers and are safe to use.
So a feature can be used by all Office 365 customers about 6 months after it is released. This beats the 3-year wait for a new version of Office and then the 12-month wait for your IT department to see the Service Pack and know it is safe to install.
Microsoft still sells a perpetual version of Office 2019. You pay once and never pay a monthly fee. But you will not get the new features. I feel bad for the people who paid $400 for Office 2019 but are not getting all of the new features such as Dynamic Arrays and Geography Data Types.
Office 365 does not mean you are running Office in the cloud. When you subscribe to Office 365, you agree to pay a monthly or annual fee, and you are using the real version of Office for Windows. As part of the bundle, you can also run Office on an iPad, or an Android, or in a web browser. For most people, those are not where most work gets done.
Here are some of the features that are only available to Office 365 subscribers:
- Pivot Table defaults: I want all future pivot tables to appear in Tabular layout instead of Compact.
- You now have access to Funnel chart, Map charts, the Insert Icons feature, and the ability to insert 3D models with full rotation ability.
- Co-authoring: Your whole team can now be editing the same Excel workbook at the same time.
- The black theme in Excel is available for people who like to work in the dark.
- The new functions MAXIFS, MINIFS, IFS, SWITCH, CONCAT, and TEXTJOIN: I hate to sound superlative, but the freaking awesomeness of
=TEXTJOIN(", ",True,A2:A99)is truly life-changing, particularly since the third argument can handle arrays to create a criteria-based TEXTJOIN.
- Excel will not nag you twice when you want to save as CSV. Trust me, for the people who had to create 100 CSV files a day to feed data into some other system, the constant nagging of “Are you sure? You will lose some features if you go with CSV” were annoying.
- A new Superscript and Subscript icon have been added.
- Improved function autocomplete: If you start to type
=LOOKUP, the tooltip will offer VLOOKUP, HLOOKUP, and LOOKUP.
- Power Query has new features, including the ability for Split Columns (think: Text to Columns) to split at a delimiter but put each item in a new row. It also adds Column by Example, similar to Flash FIll.
- The ability to turn off the newly introduced Pivot Table Date Grouping feature.
- You can now copy cells and they stay on the clipboard, even if you insert some rows or columns.
- Dynamic Arrays and new functions like SORT, SORTBY, UNIQUE, and FILTER.
- Picture transparency is now controlled with a slider on the Picture Tools tab.
- Artificial Intelligence with Ideas.
Title Photo: Renáta-Adrienn at Unsplash.com
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.