17 April 2017

Upcoming changes in Excel for Office 365

After moving to the subscription model, Microsoft has started to slowly increase the frequency of updates for its Office suite. A set of fairly important new features has been announced last month for the fast level of the Office Insider program: collaboration for editing spreadsheets and AutoSave for Excel. Both can make significant improvements to office work. Excel currently has ‘shared workbooks’ as a sort of collaboration feature, but it’s severely limited in some areas and can be confusing for the people updating them, as you will soon find out if you’re regularly working in Excel. Hopefully the new co-authoring will overcome these complications. The files also need to be saved to a SharePoint or OneDrive location, a good opportunity for Microsoft to promote its other products to companies. As for AutoSave – who hasn’t lost all their work because of a crash or computer freeze at some point? This will certainly minimize the loss of work from such problems.

Another feature I’ve been waiting for a long time was recently added to Insider’s Fast program: changing the default layout for PivotTables. I am using pivot tables relatively often at work and most of the times I change the layout to ‘tabular’ without subtotals – this makes it easier to use the resulting table as a source for other calculations, for example VLOOKUPs. But it’s tedious to repeat these formatting steps each time I create a new pivot table. From the screenshots shared by Microsoft, it looks like the update will solve this issue when it rolls out to the stable Office channel – and when companies finally decide to upgrade to Office 365.

Set a PivotTable Default: Office Insider interview

Update: as of May 2017, this feature should be available in Excel for all Office 365 subscribers, along with some PivotTable performance improvements.

The formulas available in Excel are evolving as well. It’s a sensitive area to make changes to, since you need to keep the maximum backwards compatibility with previous Excel versions, otherwise calculations will become unusable. One of the recent additions to Excel formulas is TEXTJOIN, an improved version of CONCATENATE that can cover more varied cases for joining together strings of texts from different cells. This should already be available in the standard Excel 365, as I have tested on my computer and the formula shows up in Excel. You can see below an example of how to use it.

Join All Dates Together in a Cell using TEXTJOIN & TEXT Array Formula

Post a Comment