Excel: New Features in Office 365

Right now, both purchase options are available for Microsoft Office. The traditional way is to pay approximately $400 for a perpetual license to Office 2016. You’ll own this copy of Office forever, but the features won’t be updated until you pay another $400 for a new version. The new way is to subscribe to Office 365, paying $10, $12, or $15 a month for Office 365. The big difference is that you will receive new features as soon as they are available. Every Tuesday brings the chance for a new feature in Excel.

September 22, 2017, marks the two-year anniversary of the release date for the perpetual version of Office 2016. Customers who paid $400 for this version enjoyed a few new features: forecast sheets, new chart types (Sunburst, Treemap, Histogram, Pareto, Waterfall, and Box & Whisker), and automatic date-grouping in pivot tables.

Since the perpetual version of Office 2016 shipped, people with a subscription to Office 365 have been getting many awesome new features in Excel. Strictly looking at price, the perpetual customers have paid $400 and the Office 365 customers have paid $240 over two years, but the Office 365 customers have access to all of these new features:

Pivot Table Defaults. I’ve been lobbying Microsoft for this feature for seven years. If you want all future pivot tables to appear in Tabular Layout instead of Compact Layout, or if you want all future pivot tables in Classic Drag-and-Drop mode, your wish has been granted—but only if you have Office 365. Find the feature on the Data category of Excel Options.

Coauthoring. Your whole department can edit the same workbook at the same time, and no one is locked out with the read-only message. For coauthoring to work, you have to save your workbook to your OneDrive account or to SharePoint.

Funnel Charts. This new chart type is useful for things such as illustrating a sales funnel.

Map Charts. Create a chart where each country, state, county, or ZIP code is filled with a different color (see the chart at cell G5 in Figure 1).

3D Models with Full Rotation. There are many 3D models available for free on the internet, with extensions such as .fbx, .obj, .3mf, .ply, .stl, and .glb. You can insert these in Excel and rotate the objects to view them from all sides (see image at cell A7 in Figure 1).

Insert Icons. Add any of 297 icons to your worksheet. Resize, recolor, and rotate like any shape (cells A2:B5 in Figure 1).

Six New Calculation Functions. MAXIFS and MINIFS let you find the largest or smallest value that meets criteria. IFS and SWITCH make it easier to handle a long series of If, Else, Else, Else, Else, Else conditions. CONCAT and TEXTJOIN allow you to concatenate all of the values in a range. TEXTJOIN allows you to specify a delimiter between each value and choose if blanks are ignored. The third argument of TEXTJOIN accepts an array, allowing you to create a criteria-based TEXTJOIN, as shown in the formula bar of Figure 1.

Intelligent Formula AutoComplete. If you start to type =JOIN instead of =TEXTJOIN, the formula autocomplete will offer you TEXTJOIN.

CSV File Creation. Those of you who have had to create 100 CSV files a day to feed data into some other system know how annoying the constant nagging of “Some features in your workbook might be lost if you save it as CSV” is. With this update, Excel will no longer nag you when you try to save a CSV file.

Superscript and Subscript Icons. You can add these icons to the Quick Access Toolbar to apply superscript or subscript without having to open the Format Cells dialog.

Black Theme. For people who need to work in a dark environment, this new theme will be easier on your eyes (as shown in Figure 1).

Modern Sound Scheme. Found in a new Ease of Access category in Excel Options, Excel will now gently provide sound confirmations when you perform tasks in Excel.

Split Column to Rows. This is a new feature in Power Query where each delimiter generates a new row.

Persistent Copy. Copied cells stay on the clipboard even if you insert some rows or columns.

In the old days of the three-year cycle between versions of Office, you would be forced to live with any new feature the Excel team added for three years—even the ones that met with backlash. With Office 365, the Excel team is able to introduce a new Excel Option to allow people to turn a feature off, such as disabling the automatic date-grouping in pivot tables.

It used to be that if you had an idea for a feature you wanted to be included in Excel, you better have submitted that feature two years before the release date. Otherwise, you had no hope. Today, anyone who has an Office 365 subscription and can garner 20 votes for an idea on http://excel.uservoice.com has a chance to see the idea incorporated into Excel within months.